Search This Blog & Web

Wednesday, August 19, 2009

Create and Use Templates -- SSMS Enhancements 2008

We can save our custome code and execute it several time for testing like if you want to "returning all column information" and you have no time to search for query every time you need then their is an other way to remember and use that.Another example is if you want to test you procedure to execute several time with different parameters then you can use SSMS template for that.

To get that we have to follow these steps.

1. In the Object Explorer go to view -- > Template Explorer
2. There are several templates to use in.
3. Right click on Sql Server Templates and Add New Folder
4. Right clieck on Folder and Add new Template
5. Set the Name of the Template and then click edit for write you sql
6. Write your query there like
        Select * from information_schema.tables where table_name = 'abc'
7.  Run this query and save the template.
8. Open it again in edit mode and this time apply following changes.
         Select * from information_schema.tables where table_name = ''
9.  Save and close the Template window
10. Now open the Template agin by duble click on the file.
11. go to Query -- > Specify Values for Template Parameter
12. as parameter displays their
13.  Enter value of parameter and see how Template work for several conditions.

Here are some Sample Pictures.
Picture1 : Template explorer with New Template file and folder
 
Picture 2 : Passing parameter values

 
Picture 3: After providing values query looks like
 

Wednesday, August 12, 2009

Replacing characters from a String -- Stuff

We can concat and replace characters from a string in different ways. To understand lets have an example

Lets suppose I have a value

declare @str varchar(20)
select @str = '44556663333666251324'


I want this output 00006663333666251324 this means i want to replace first 4 characters.

Solution One:

Use substring to return all required characters and remove undesired ones.

set @str = substring(@str, 5, len(@str))

This is the output of above code 6663333666251324.
Now use + concate operator to add four 0000 in start

select '0000' + @str

and we have desired result 00006663333666251324

Second Solution:

but their is another efficent way to do this.

declare @str varchar(20)
select @str = '44556663333666251324'

select stuff(@str,1,4,'0000')

and we have same output 00006663333666251324


Stuff
This SQL Server function used to replace string characters with any other characters. It has four parameters
1. String name
2. Starting replacement index
3. Ending replacement index
4. What are the new characters (replaced characters)

We can use this in find and replace requirements.

Tuesday, August 11, 2009

Common Table Expression

When to Use
Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. Even when a CTE is not necessarily needed (as when listing just the employees and their subordinate count in the example above), it can improve readability. In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs:

• Create a recursive query.
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
Using scalar subqueries (such as the (SELECT COUNT(1) FROM ...) examples we've looked at in this article) cannot be grouped or filtered directly in the containing query. Similarly, when using SQL Server 2005's ranking functions - ROW_NUMBER(), RANK(), DENSE_RANK(), and so on - the containing query cannot include a filter or grouping expression to return only a subset of the ranked results. For both of these instances, CTEs are quite handy. (For more on SQL Server 2005's ranking capabilities, be sure to read: Returning Ranked Results with Microsoft SQL Server 2005.)
CTEs can also be used to recursively enumerate hierarchical data. We'll examine this next!

Common Table Expression Syntax
A Common Table Expression contains three core parts:


• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

A Simple Common Table Expression Example
Before we CTEs in detail, let's start by looking at a simple example.

WITH ProductCategoryNamesOverTenDollar
(ProductName, CategoryName, UnitPrice) AS
(
SELECT
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

Once the CTE has been defined, it must then immediately be used in a query.
In short, a Common Table Expression allows us to define a temporary, view-like construct. We start by (optionally) specifying the columns it returns, then define the query. Following that, the CTE can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Using Multiple CTE’s
You can, however, define multiple CTEs after the WITH keyword by separating each CTE with a comma. For example, the following query uses two CTEs. The subsequent SELECT query then uses an INNER JOIN to match together the records from the two CTEs:

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
SELECT
CategoryID,
CategoryName,
(SELECT COUNT(1) FROM Products p
WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
FROM Categories c
),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
SELECT
ProductID,
CategoryID,
ProductName,
UnitPrice
FROM Products p
WHERE UnitPrice > 10.0
)

SELECT c.CategoryName, c.NumberOfProducts,
p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
INNER JOIN CategoryAndNumberOfProducts c ON
p.CategoryID = c.CategoryID
ORDER BY ProductName

Recursive Common Table Expressions
Recursion is the process of defining a solution to a problem in terms of itself. For example parent child relationship in employee table.

SELECT query that returns the rows from the CTE:
WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS
(
-- Base step
SELECT
EmployeeID,
LastName,
FirstName,
ReportsTo,
1 as HierarchyLevel
FROM Employees
WHERE ReportsTo IS NULL

UNION ALL

-- Recursive step
SELECT
e.EmployeeID,
e.LastName,
e.FirstName,
e.ReportsTo,
eh.HierarchyLevel + 1 AS HierarchyLevel
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON
e.ReportsTo = eh.EmployeeID
)

SELECT *
FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName

This will return all the employee and their hierarchy level:

Monday, August 10, 2009

Best practices for Developers

These are the common best practices that I follow durning development.

1.Database design
1.Take time and try to best understand and design database.
2.Take narrow column, this would be better for memory paging and I/O cost.
3.Try to use database built-in referential integrity.
2.Indexes
1.Using Index tuning Wizard to remove and add necessary indexes.
2.If regular DML performed on Clustered Index column then use defragmentation of indexes on regular interval.
3.Consider indexes on all columns that are frequently used in where clause, group by and top.
3.Store Procedures
1.Keep transaction as short as possible this reduce locking.
2.Always use Begin Trans, Commit and Rollback to get best result of DMLs.
3.Use least restrictive transaction Isolation level like Read Committed.
4.Always use SET NO COUNT ON in begging of your store procedure.
5.Always remove unwanted variables and comments from procedures.
6.Use same SQL connection as much you want to re-use same execution plan for a procedure.
4.SQL and T-SQL
1.Use block and Inline comments in your SQL this will improve understanding of the SQL complexity and reusability.
2.Keep in mind while using Union it will by default use select distinct clause if you want all records you must use Union all.
3.Don’t return column data that you do not need. Try to avoid select * in query when you have covering indexes.
4.Try to use where clause in your query to narrow the result and result only those columns that you need. If you have no where clause then from doing the same if all joins are inner joins but return all columns in memory.
5.If you have a choice between IN and Between, try to Use between.
6.If you want to return data from single table do not use view because that might have many table joins and cause the performance.
7.Try to avoid where clause that is non-sargable while using index columns. This cannot take advantage of indexes and seek operation. Such as “Is NULL, like, between, not , not exists, <>, !< etc.”
8.Try to Use CLR functions only for logic not for DML operations.
9.Try to avoid cursors as much as possible.
10.Better to use set based operations.
11.Try to minimize joins in query while using T-SQL.
12.To avoid deadlock use NOLOCK with table name.

SQL Server Locking

Locking
Shared (S) Used for read operations that do not change or update data, such as a SELECT statement.
Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X) Used for data-modification operations. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent Used to establish a lock hierarchy. The types of intent locks are:
intent shared (IS),
intent exclusive (IX),
shared with intent exclusive (SIX).

Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

We can see Dead locks using query
(Select * from master.dbo.sysprocesses where Blocked = 1)

You can create Deadlock example as

1. Create a table and insert 2 records in it on 1st query window
Create Table ##Table (a int,b varchar(10))

begin transaction
insert into ##Table values (1,'aslam')
insert into ##Table values (2,'akram')
commit transaction

2. Query in a seperate window to get result
select * from ##Table

this will give you 2 rows
3. Now Insert another record from 1st window like this.
begin transaction
insert into ##Table values (3,'aslam')

4. Again run query from step 2
select * from ##Table

this will not give you any row and query continues execution -- deadlock situation
5. Now run this command
rollback transaction
6. Go to query window and see you got 2 records.


There is a way to get result by avoiding deadlock situation even transaction is open
1. Execute first 3 steps from above
2. Now run this query
select * from ##Table (NOLCOK)

this will show you all 3 records.
3. now execute commamd# 5
and agains select all records you will get 2 records.

It means you can get all records with NOLOCK.

Database Version and Service Pack

To check which version of database installed on your pc. Write this command

Select @@VERSION

If you want to check what is the latest Service pack on your system.Write this query.

select serverproperty('productversion'),serverproperty('productlevel'),serverproperty('edition')

Wednesday, August 5, 2009

Optimize a query using Indexes (Index Options) Part-2

To get this query i find help from msdn and got following result.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
  ON (column [ ASC | DESC ] ) 
   INCLUDE ( column_name )  
   WITH ( [ Columnlist] )  
   ON  partition_scheme_name ( column_name ) 
   filegroup_name 
   default 
 
  PAD_INDEX = [ ON | OFF ] 
   SORT_IN_TEMPDB = [ ON | OFF ] 
   IGNORE_DUP_KEY = [ ON | OFF ] 
   STATISTICS_NORECOMPUTE = [ ON | OFF ] 
   DROP_EXISTING = [ ON | OFF ] 
   ONLINE = [ ON | OFF ] 
   ALLOW_ROW_LOCKS = [ ON | OFF ] 
   ALLOW_PAGE_LOCKS = [ ON | OFF ] 
   MAXDOP = max_degree_of_parallelism


Step 6 : After building index following query used to find the index on my table.

select * from sys.indexes where name like '_dta_index_users_bitActive_%'

Step 7 : After a little search i find a rule to handle index after creation

         1. Reorganize index when its defregmentation is less then 40%  

         2. Rebuild index when its defregmentation is greater 40%

and this query returns the fregmention percent

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() and ps.index_id = 248440009
ORDER BY ps.OBJECT_ID
GO

Step 8: Now i need a query that update my new created index and i use

ALTER INDEX Index Name 
ON dbo.TABLE REBUILD REORGANIZE
WITH ( PAD_INDEX = OFF, 
  STATISTICS_NORECOMPUTE = OFF, 
  ALLOW_ROW_LOCKS = OFF, 
  ALLOW_PAGE_LOCKS = ON, 
  SORT_IN_TEMPDB = OFF, 
  ONLINE = ON )
GO

Step9: At last i need some job who check fregmentation and reorganize or rebuild that index.

Step10 : After all query result is dramatically fast but job might not be the best idea every time.

Step11: I am searching more and will share when got result........ 

Optimize a query using Indexes (Index Options)

Recently i got a query that is running on more then 10 million records and join 2 tables. Simple query returns result in more than 1.30 minutes. For much search i find solution to recduce its time to 0 seconds and i do following to do that.

Step1: Change the query paging logic from 2 quries to one query using WITH clause.

Step2: Execute the query and save profiler of that query. Analyze it in Database Tunning Advisor.

Step3: It offers me more than 5 solution by creating 2 indexes on 2 tables and different columns combinations and also creating staistics for all selecting columns.

Step4: I might not do this because if i create staistics then their must be some job who will regularly update that satistics so i decided to create 1 index to improve performence.

Step5: I got 2 types of Indexes:

              ONLINE: Which slows down result during rebuilding.

              OFFLINE: Which locks all table during rebuilding.

I use following query to create Index

CREATE NONCLUSTERED INDEX [_dta_index_IndexName] ON Table
(
[Column list] [order] ,
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

more detail is in next post......

Monday, August 3, 2009

Returning table from ; seperated values

create function [dbo].[fn_split](
@str varchar(8000),
@delimiter char(1)
)
returns @returnTable table (idx int primary key identity, item varchar(8000))
as
begin
declare @pos int
select @str = @str + @delimiter
While len(@str) > 0
begin
select @pos = charindex(@delimiter,@str)
if @pos = 1
insert @returnTable (item)
values (null)
else
insert @returnTable (item)
values (substring(@str, 1, @pos-1))

select @str = substring(@str, @pos+1, len(@str)-@pos)
end
return
end