Copy query result into excel sheet.

Copy query result from SQL Server Managment studio.
http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!2792.entry

Using multiple CTE 's in single SQL Statement

Declare @table table (id int,name varchar(50),parentid int) Insert into @table values (1,'Director',NULL)
Insert into @table values (4,'City Manager Fsd',3)
Insert into @table values (5,'Area Manager Fsd',4)
Insert into @table values (2,'County Manager',1)
Insert into @table values (3,'Country Manager 2',1)
Insert into @table values (6,'City Manager ISB',2)
Insert into @table values (7,'Co-director',NULL)
Insert into @table values (8,'Country Head',7)
Insert into @table values (9,'Sales Manager',7)
Insert into @table values (9,'Sales Manager',4);

with CTE(id ,name,parentid,levels) as
(Select id,name,parentid ,1 as levels from @table t where parentid =1
union all
Select td.id,td.name,td.parentid,levels+ 1 as levels from @table td inner join cte on td.parentid = cte.id
),

CTE1(id ,name,parentid,levels) as
(
Select id,name,parentid ,1 as levels from @table t where parentid =7
union all
Select td.id,td.name,td.parentid,levels+ 1 as levels from @table td inner join cte on td.parentid = cte.id
)

select CTE.* from CTE inner join CTE1 on CTE.id = CTE1.id
 
cheers:)

Using CTE in Sql Server 2005 / 2008

Declare @table table (id int,name varchar(50),parentid int)
Insert into @table values (1,'Director',NULL)
Insert into @table values (4,'City Manager Fsd',3)
Insert into @table values (5,'Area Manager Fsd',4)
Insert into @table values (2,'County Manager',1)
Insert into @table values (3,'Country Manager 2',1)
Insert into @table values (6,'City Manager ISB',2)
Insert into @table values (7,'Co-director',NULL)
Insert into @table values (8,'Country Head',7)
;

with CTE(id ,name,parentid,level) as

(



Select id,name,parentid ,1 as level from @table t where parentid is null

union all

Select td.id,td.name,td.parentid,level+ 1 as level from @table td inner join cte on td.parentid = cte.id

)


select * from CTE


Find answers of your questions in this advance technique of SQL Server

SQL Server ETL tool proves to be best in market.

Insertion of 1TB of data with UniSys in less than 30 minutes beating informatica which made this in 45minutes.

Here is more detail.
http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx

Update Statement Using Join in from clause

Some time we need to update our table from another table join that cannot possible with simple select table. In sql server 2005 we can do this using update using join as I show this in my example.
Declare @vA table (id int,val varchar(50))
Declare @vb table (id int,val varchar(50))

Insert into @vA(id,val) values (1,'1111')
Insert into @vA(id,val) values(2,'1111')
Insert into @vA(id,val) values(3,'1111')
Insert into @vA(id,val) values(4,'1111')
Insert into @vA(id,val) values(5,'1111')
Insert into @vA(id,val) values(6,'1111')
Insert into @vB(id,val) values(1,'2222')
Insert into @vB(id,val) values(2,'4444')
Insert into @vB(id,val) values(3,'5555')
Insert into @vB(id,val) values(4,'3333')
Insert into @vB(id,val)values (5,'6666')
Insert into @vB(id,val) values(6,'7777')

Update @vA set val=d.val
from @vA a inner join @vB d on a.id = d.id

Select * from @vA


enjoy the output.

Store procedure optimization -- best practices

1. Use stored procedures instead of heavy-duty queries.

2. Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
3. Call stored procedure using its fully qualified name.
4. Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
5. Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
6. Use the sp_executesql stored procedure instead of the EXECUTE statement.
7. Use sp_executesql stored procedure instead of temporary stored procedures.
8. If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
9. Try to avoid using temporary tables inside your stored procedure.
10. Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
11. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
12. Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
 
 
For more detail please visit.http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm

Finding used table space with data

Following script used to find each table space used in database along with data. This script is using a master procedure to find this.

Very helpfull and picked from a fourm post.

Create Table #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50))

SET NOCOUNT ON
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''

select a.table_name,
a.row_count,
count(*) as col_count,
a.data_size
from #temp a
inner join information_schema.columns b on a.table_name = b.table_name
group by
a.table_name, a.row_count, a.data_size
Order by CAST(Replace(a.data_size, ' KB', '') as integer) desc
drop table #temp

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

 

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.

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:

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')

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

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

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......

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

SQL server CPU utilization history

I find this from a blog and save for my memory

-- Get CPU Utilization History (SQL 2005 Only)
DECLARE @ts_now bigint;
SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
SELECT TOP(10) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%') AS x
) AS y
ORDER BY record_id DESC;

first and last date of Month,Quarter,Week and year.

you can get a lot more by changing parameters a little

--- first & last day of month
select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()),0)
select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1 ,-1)

-- first & last day of week
select DATEADD(wk, DATEDIFF(wk, 0, GETDATE()),0)
select DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1 ,-1)

-- first & last day of year
select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()),0)
select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1 ,-1)

-- first & last day of Querter
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()),0)
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1 ,-1)

Returning month name from Date

One of my colleage asked about getting month name and month value for date time value.

Here is the example to do that.

For Month,Year and Day value use following datetime functions.
Select DAY(GETDAET()); -- returns day part of date
Select MONTH(GETDAET()); -- returns year part of date
Select YAR(GETDAET()); -- returns year part of date

there is another function to do this
Select DATEPART(mm,GETDAET());

use
'mm', 'm' for month
'dd' for day
'y','yyyy' for year


For Month name value use following datetime functions.
Select DATENAME(mm,GETDAET()); -- returns month part of date like July

using custome code we can achive this using case statement

Select
case month(getdate())
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
End

How to remove time postion from date in SQL server 2005

we can remove time portion in different ways.


1. select DATEADD(DD, DATEDIFF(DD, 0, GETDATE()),0)
2. select convert(datetime,cast(GETDATE() as varchar(12)) , 103)
3. select convert(datetime,cast(getdate() as int) , 103) - 1

if you know any other please mention.

SQL Join Tactics

Join conditions can be specified in either the FROM or WHERE clauses. Specifying them in the FROM clause is recommended. From Microsoft MSDN. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.

Joins can be categorized as:

1. Inner joins: (the typical join operation, which uses some comparison operator like =). Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. This includes equi-join or natural join. For example, retrieving all rows where the employee number is the same in both the employee and working-hour tables.

2. Outer joins: Outer joins can be a left, a right, or full outer join.
LEFT JOIN or LEFT OUTER JOIN The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN a right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

3. FULL JOIN or FULL OUTER JOIN A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

4. CROSS JOINS Cross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.

For more detail please read. http://msdn.microsoft.com/en-us/library/ms191472.aspx

How to shrink database log file size

There are 2 ways to shrink db file size using SQL Server

Way one: Using SQL Server job or Maintainess plan and by setting log shrink to 10%.

but some time we face problem that our log file do not shrink even if job runs successfully. This is because this shrink operation shinks free and unused log file update 10%. if we want to shrink db file at our desired percentage then we have to use DBCC command and manually enter remaing size of log file. like follows

Second Way:
DBCC SHRINKDATABASE ('MYDATABASENAME',10);

This command shrinks database log file size to 10Mb.

Caustion: before using this please take a backup first.

you can take backup using this command

backup log 'dbname' with truncate_only

Returning required rows when there is no primary key.

One of my friend ask solution for this problem

Problem : If a table has 11 records, how can we get records from record number 5 to record number 10 and there is no primary key in the table.

Solution: see the example in SQL Server in following example

Declare @Table Table(name varchar(50),DateCreated datetime)

Insert into @Table values ('Shamas',getdate())
Insert into @Table values ('Qamar',getdate())
Insert into @Table values ('Atif',getdate())
Insert into @Table values ('Kashif',getdate())
Insert into @Table values ('Maria',getdate())
Insert into @Table values ('Usman',getdate())
Insert into @Table values ('Wasif',getdate())
Insert into @Table values ('Narimaan',getdate())
Insert into @Table values ('Hina',getdate())
Insert into @Table values ('Saqib',getdate())
Insert into @Table values ('Farhan',getdate())

Select * from
(
Select row_number() over (order by name ) as rowid,
name,DateCreated
from @Table
) t
where rowid >= 5 and rowid < = 10

Summary: This query will assing row number to each record with name as order column. Then outer query will return record number as what you want.

We can do this in many other ways in SQL Server 2000 and 2008

SQL Server 2008 Katmail new features (Declaring and initializing variables)

This is my first post for SQL Server 2008 update features. We are going to start complete list of new features according to my following link

We declare and assign value to a variable in 2 different statements before SQL Server 2008 update. Lets see example

-- declare variables
DECLARE @date DATETIME
DECLARE @i INT

-- initialize
SELECT @date = GETDATE(), @i = 10

Now with SQL server 2008 we can declare an initialize variables in a single TSQL statement. see this example:

-- sql server 2008 - Declare and initialize in a single statement
DECLARE @date DATETIME = GETDATE(), @i INT = 5;

This is a great feature in a way to easy the code and assigning it value. Another interesting feature is the support for Compound Assignment as we read in C++ and VB 6. If we have a little idea then it is a appreciable change. See following example

DECLARE @i INT
SELECT @i = 10

SELECT @i = @i + 1 -- i = 11 now
SELECT @i = @i - 2 -- i = 9 now
SELECT @i = @i * 3 -- i = 27 now

SELECT @i AS newValue

----- Here is output-----
----------
newValue
-----------
27

There is another very good enhancement in Katmai. We can insert multiple records in a single statement that we do in different insert statements in sql server 2005. See below example of sql server 2005

Declare @table Table(a int, b int)
Insert into @table values (1,2)
Insert into @table values (2,2)
Insert into @table values (3,3)
Insert into @table values (3,4)

Now how can we do that in sql server 2008 is defined in following example

DECLARE @table TABLE ( a INT, b INT )
INSERT INTO @table VALUES (1,1), (2,2), (2,3),(3,3),(3,4)

I will discuss more updates in more posts

To see complete list of update for related posts visit
http://sqlservercoollinks.blogspot.com/2009/07/new-features-in-sql-server-2008-katmai.html

Returning random result from a dataset

Problem: Many of the developers write complex and huge code to return random images or news on every page refresh on its website or project.

Solution: Sql server provides easy way to return random result on each call.

Query:

Select * from [table1] order by newid()

Result:
if you have 2 records in your table you will get different result each time.

How to avoid dynamic query using procedures

We all know string query or dynamic query utalize so much resources while execution. Major flow back of dynamic query is string conversion of all types of parameters and writing bad code through SQL injection.

Here is a solution to avoid that type of query.

Case:
There is a column "Param" and it is not required every time while query execution.
the table joins with master table as left outer join.

Problem:
When we provide parameter we can use case to handle it without writing dynamic query. But when we do not provide parameter value then only child table records return what can i do to return whole dataset.

Solution:
here is the solution

DECLARE @PARAM INT
SET @PARAM = 0

SELECT * FROM Master LEFT OUTER JOIN Child ON Master.id = Child.id
WHERE (CASE WHEN @PARAM = 0 THEN master.id ELSE child.id END )
IN (CASE WHEN @PARAM = 0 THEN master.id ELSE
CASE WHEN @PARAM > 0 THEN @PARAM ELSE child.id END
END)

when we provide paramter then only required records returns else all records returns from master table and we do not need any dynamic query.

New Features in SQL Server 2008 Katmai

Microsoft SQL Server 2008 introduces several important new Transact‑SQL programmability features and enhances some existing ones. You can find details in SQL Server Books Online.

Following are highlighted features

1. Declaring and initializing variables : http://sqlservercoollinks.blogspot.com/2009/07/1-declaring-and-initializing-variables.html
2. Compound assignment operators : http://sqlservercoollinks.blogspot.com/2009/07/1-declaring-and-initializing-variables.html
3. Table value constructor support through the VALUES clause
4. Enhancements to the CONVERT function
5. New date and time data types and functions
6. Large UDTs
7. The HIERARCHYID data type
8. Table types and table-valued parameters
9. The MERGE statement, grouping sets enhancements
10.DDL trigger enhancements
11.Sparse columns
12.Filtered indexes
13.Large CLR user-defined aggregates
14.Multi-input CLR user-defined aggregates
15.The ORDER option for CLR table-valued functions
16.Object dependencies
17.Change data capture
18.Collation alignment with Microsoft® Windows®
19.Deprecation

I have picked this list from official website of Microsoft TECHNET.I will define it in detail one by one.You can also get detail from this attached link.

Returning first row of each repeating group values

create table repeatCheck
(id int identity(1,1),
email varchar(50),
[name] varchar(50) );

insert into repeatCheck values ('shamas@isl.com','Shamas');
insert into repeatCheck values ('shamas@hotmail.com','Shamas');
insert into repeatCheck values ('maria@isl.com','Maria');
insert into repeatCheck values ('azher@isl.com','Azher');
insert into repeatCheck values ('azher@hotmail.com.com','Azher');
insert into repeatCheck values ('khawar@isl.com','Khawar');
insert into repeatCheck values ('khalil@isl.com','Khalil');
insert into repeatCheck values ('khalil@hotmail.com.com','Khalil');
insert into repeatCheck values ('maria@hotmail.com.com','Maria');

Problem: Many of my friends ask me how can i get first or second row from table on some critaria which generates group like Category in Questions table.

Solution: In SQL Server 2005 we have many advance options which can do this. I am going to give you an example for that.


select * from repeatCheck;

--- Now return first row of repeating names

select *
from
(
select rank() over(partition by [name] order by email) as rownumber,email
from repeatCheck
) v
where v.rownumber = 1


delete from repeatCheck;
drop table repeatCheck;


You can see Rank function assign rank with the changing group like in the above example row number 1 and then 2 of rank value assigns form "Maria@isl.com" and "Maria@hotmail.com" but due to same name we have two id's of rank value. For next name we have rank value again starts from 1. At the last we can easily return top 1 of each group or any top number.

We can also do this in SQL Server 2000 or older but not Using Rank..

Find repeating values from table

Today i am going to discuss a very common problem that takes too much time of developers to write code and find soution

Problem: How can i find duplicate and return values from table data
Solution: Using SQL Server following is the example to find duplicating values.


create table repeatCheck
(id int identity(1,1),
email varchar(50));

insert into repeatCheck values ('shamas@isl.com');
insert into repeatCheck values ('shamas@isl.com');
insert into repeatCheck values ('maria@isl.com');
insert into repeatCheck values ('azher@isl.com');
insert into repeatCheck values ('khawar@isl.com');
insert into repeatCheck values ('khalil@isl.com');
insert into repeatCheck values ('maria@isl.com');


select * from repeatCheck

--- Now return all repeating emails

select email,count(email) as emailcount
from repeatCheck
group by email
having count(email) > 1


As you can see how simple is to return duplicate values for different email addresses

Difference between Editions

1. SQL Server 2008 Features Comparison
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
http://msdn.microsoft.com/en-us/library/cc645993.aspx

2. SQL Server 2005 Features Comparison
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

3. SQL Server 2000 Features Supported
http://msdn.microsoft.com/en-us/library/aa175266(SQL.80).aspx

SQL Server 2008 auto audit facility

Sql Server 2008 introduce automatic auditing facility. You can read detail from following link or follow these steps
1. In Security tab go to --> Audit --> New Audit.
2. In Audit Database go to -- > Audit Specificatoin.

and enjoy

for more detail please read
http://msdn.microsoft.com/en-us/library/dd392015.aspx

Finding Slow Runnig Quries

SELECT top 10
ProcedureName = o.name,
[Procedure] = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
LastLogicalReads = s.last_logical_reads,
MaxLogicalWrites = s.max_logical_writes,
LastLogicalWrites = s.last_logical_writes,
CreationDateTime = s.creation_time,
MaxPhysicalReads = s.max_Physical_reads,
LastPhysicalReads = s.last_Physical_reads,
CallsPerSecond = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
INNER JOIN SYS.objects o on t.objectid = o.object_id
ORDER BY
AvgExecutionTime DESC



-- select * from sys.databases

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,
st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Returning Comma seperated Values in SQL Server 2005

Declare @vComma Varchar(max);

Select @vComma = COALESCE(@vComma+';','') + column1 from table1

select @vComma

I have 5 values in Unique table against Val column and this command will return all values as ; seperated

Finding missing relationship

-- Possible bad Indexes (writes > reads)

DECLARE @dbid int
SELECT @dbid = db_id()
SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id, 'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups, 'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1 AND s.database_id = @dbid AND user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;

--- Index Read/Write stats for a single table
DECLARE @dbid int
SELECT @dbid = db_id()
SELECT objectname = object_name(s.object_id), indexname = i.name, i.index_id, reads = user_seeks + user_scans + user_lookups, writes = user_updates
FROM sys.dm_db_index_usage_stats AS s, sys.indexes AS i
WHERE objectproperty(s.object_id,'IsUserTable') = 1 AND s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id = @dbid AND object_name(s.object_id) IN( 'tablename')
ORDER BY object_name(s.object_id), writes DESC, reads DESC;

-- Show existing indexes for this table EXEC sp_HelpIndex 'tablename'

-- Missing Indexes SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',mid.equality_columns, mid.inequality_columns, mid.included_columns,migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)ON mig.index_handle = mid.index_handleORDER BY index_advantage DESC;

-- Missing indexes for a single tableSELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',mid.equality_columns, mid.inequality_columns, mid.included_columns,migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)ON mig.index_handle = mid.index_handleWHERE statement = '[databasename].[dbo].[tablename]' -- Specify one tableORDER BY index_advantage DESC;

-- Examine current indexesEXEC sp_HelpIndex 'dbo.tablename'

Convert Date time function in SQL Server 2005

I find on many websites and do many exercieses following are the output summary for those.

-- return datetime portion without time (best is from top to bottom)
1. SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
2. SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
3. SELECT CONVERT(DATETIME, CONVERT(INT, GETDATE()))
4. CAST(CONVERT(varchar, GetDate(), 101) 

Query execution order in SQL Server database engine

Reading Output statistics for SQL SERVER

I have lot of questions in my mind while start optimization on procedures. On of the question is how can we see what happens with SQL SERVER when a procedure executes? How system calculates overall procedure execution time? and many others.

I have found many published material on these topics and most suitable command to read Statistics for procedure is as below

This command will show you detail about

• Execution time
• Logical reads
• Physical reads
• read-ahead reads
• Scan Count
• CPU time

and other options you need to note for excessive query optimization.

SET Statistics IO ON
SET Statistics TIME ON

exec procedurename "parameters...."

SET Statistics IO OFF
SET Statistics TIME OFF

Using Case in where clause utilizing parameter

Recently i have faced a problem about parameters usage in where clase. Problem statement is as?
"I have 2 parameters and all alow null values. How can i impliement this logic."

I got one solution that is as under:

For 2 parameters

@vparam1
@vparam2

if @vparam1 is not null and @vparam2 is not null
Select "column list..."
From "table..."
Where "columname" = @vparam1 and "columnname" = @vparam2
elseif @vparam1 is not null and @vparam2 is null
Select "column list..."
From "table..."
Where "columname" = @vparam1
elseif @vparam2 is not null and @vparam1 is null
Select "column list..."
From "table..."
Where "columname" = @vparam2
else
Select "column list..."
From "table..."

If any one have another solution please update me ...... in any version of SQL Server

Transaction Handling for SQL Server Procedures

--------------- SQL Server 2000

Begin Transaction
------- Paste your code here
------- On every Insert or Update write
if @@Error > 0
GoTo ErrorPoint

Commit Transaction

ErrorPoint: --- this is go to defination
Rollback Transaction

--------------- SQL Server 2005
Begin Try
Begin Transaction
----------- Paste your code here
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch

Database restore using T-SQL

If you find difficulty with SSMS to restore your database you can restore it with T-SQL as follows.

RESTORE DATABASE CC_DEV FROM DISK = 'C:\CC_DEV.bak' WITH MOVE CC_Data' TO 'c:\CC_Dev\CC_Data.mdf',MOVE 'CC_log' TO 'c:\CC_Dev\CC_Log.mdf'

This post will use for restore in all version of SQL SERVER