Purpose : Find total sales made by each employee for each year

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'

Purpose : Find total sales made by each company for each product

Many users complain that some new features are not working in SQL Server 2005 when the server is migrated from SQL Server 2000

These are the common tasks that should be done to avoid some migration issues

1 Change the compatibility level to 90
 
  EXEC sp_dbcmptlevel 'your_db_name',90

  This will solve the errors that occur during the execution of the code that contains newly introduced functions 
  like row_number(), rank(),etc

  EXEC SP_CONFIGURE 'show advanced options', 1
  RECONFIGURE
  go
  EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
  RECONFIGURE
  go
  EXEC SP_CONFIGURE 'show advanced options', 0
  RECONFIGURE

  This will solve the errors that occur when you use OPENROWSET and OPENDATASOURCE commands

3 Install Service Pack1 for SQL Server 2005
 
  This will solve the errors that occur when you use Import/Export wizard

Writing a better TSQL query will get you better performance, in terms of system & life-style too.

Here is the email I received from my BIL, too get a     lighter side of blending a TSQL query as Stored Procedure (from а BrideGroom) .... guess what would be end result of that Stored Procedure!!  

----------------

Connect to MarriageLookings SQL instance and write the following Stored Procedure to execute:

CREATE PROCEDURE BeforeMarriage (BrideGroom Male (25) ,Bride Female(20) )

AS
BEGIN

    SELECT Bride FROM  [india_Brides] WHERE FatherInLaw = 'Millionaire' 
    AND  Count(Car) > 20  

    AND  HouseStatus ='ThreeStoreyed'

    AND BrideEduStatus IN ('Masters Degree','B.TECH' ,'B.E' ,'Degree' ,'MCA' ,'MiBA')

    AND Having  Brothers= Null AND Sisters =Null
 
END

GO

CREATE PROCEDURE AfterMarriage (FatherInLawBankAccount(25) , MyBankAccount(20) )

AS
BEGIN
 

    SELECT Gold ,Cash,Car,BankBalance FROM FatherInLaw   

    UPDATE MyBankAccout SETMyBal = MyBal + FatherInLawBal

    UPDATE MyLocker SET MyLockerContents = MyLockerContents + FatherInLawGold


    INSERT INTO MyCarShed VALUES('BMW')
END
GO 

----------------------------------------------------------------------

Compiled both the stored procedures and executed, awaiting results!!!

!

!

!

!

!

!

!

!

!

!

!

!

Then the BrideGroom writes the below query as a reply:

DROP BrideGroom;
Commit; 

Hope you get the lighter side of the above TSQL query. 

As you all know, GO command signals the end of the batch of T-SQL statements

However in SQL Server 2005, it is also used to execute set of commands for a specified number of times

Consider that you want to create a table that should have hundred random integer values. You can the methods like the ones specified in http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspx or a while loop. You can also use GO command by specifying how many times it should run

create table #numbers(random_value int)
GO -- Signals the end of the batch

insert into #numbers(random_value) Select cast(100000*rand() as int)
GO 100 -- Signals that the above statement should run for 100 times

select random_value from #numbers

Now the table would have 100 random interger values

As a SQL Server user or DBAs using SP_WHO or undocumented stored procedure SP_WHO2 is a common occurrence.

For this there are times when you will need to run sp_who on your SQL Server to figure out who is on and what are they doing. The output is quite clear to get a list of processes with SPID, but how about more detailed information on these processes. Until SQL Server 2005 it is bit tricky to get such detailed information, so what 2005 version offers you with system catalogs such as sys.dm_exec_requests. Here is the script you can obtain information that is freely available on Technet site too:

select r.session_id
,status
,substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
as query_text   --- this is the statement executing right now
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

Until SQL Server 2005 SP_WHO2 was my favourite and using system catalogs & DMVs is much flexible to find out the culprit.

Sometimes it may be useful to reuse the result of the DBCC commands. If the DBCC command resturns a resultset, it can be outputted to a table.

Consider the following command

DBCC useroptions

It returns a single resultset. To copy the resultset to a table, you can use the following

Create table #dbcc_useroptions ([set option] varchar(100), [value] varchar(100))

insert into #dbcc_useroptions
exec('DBCC useroptions')

select * from #dbcc_useroptions

drop table #dbcc_useroptions

Note that it is possible with dynamically executing DBCC commands and only the DBCC commands that returns a resultset can be used

I have seen many newbies asking "How do I sort the numbers stored in varchar columns?"

Here are some methods

declare @t table(data varchar(15))

insert into @t
select '6134' union all
select '144' union all
select '7345' union all
select '109812' union all
select '100074'union all
select '1290' union all
select '45764'

select data from @t
order by cast(data as int)

select data from @t
order by data+0

select data from @t
order by len(data),data

select data from @t
order by replace(str(data),' ','0')

select data from @t
group by data
order by right(replicate('0',len(max(data))),len(data))

As you know, Replicate function is used to repeat a character expression for a specified number of times. But by default the result is converted to varchar of maximum size 8000 when you dont convert the expression to specific type

Consider the following example

declare @v varchar(max)
set @v=replicate('a',50000)
select len(@v),datalength(@v)

Note that the result is not 50000 but 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype

To get a correct result, you need to convert the expression to the type of varchar(max)

declare @v varchar(max)
set @v=replicate(convert(varchar(max),'a'),50000)
select len(@v),datalength(@v)

Now the result is 50000 as expected.

So you need to be aware of this implicit convertion when using Replicate function to assingn value to the column of varchar(max) datatype

DMVs are most helpful to find instant information on SQL instance without causing further delays, in any performance degradation time you can execute following TSQL to get top 10 queries that are generating lots of I/O on the server:

SELECT TOP 10
       total_logical_reads,
       total_logical_writes,
       execution_count,
       total_logical_reads+total_logical_writes AS [IO_total],
        st.text AS query_text,
       db_name(st.dbid) AS database_name,
       st.objectid AS object_id
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes > 0
ORDER BY [IO_total] DESC

As you may be aware that you can pass parameters to the stored procedure when you have the selection of data requirement such as stored procedure with data type,length, parameter position and also the mode of parameter (Input or Output).

Using INFORMATION_SCHEMA views you can get further information on what kind of parameters are used within a stored procedure without viewing the stored procedure or calling it to execute.

select Specific_Name as 'Procedure_Name', Parameter_Name, Ordinal_Position as 'Param_Position',  case Parameter_Mode when 'IN' then 'Input' else 'Output' end as 'Mode', Data_Type, Character_Maximum_Length 'Data_Length'
from information_schema.Parameters where specific_name = @Proc_Name
go

This is another best example to state how flexible it is to use INFORMATION_SCHEMA views that are one of the hidden gems in SQL Server.

Temporary database tempdb is created whenever the Server is restarted. So you can know when Server was restarted by knowing the creation date of the tempdb

EXEC sp_helpdb tempdb

select crdate  from master..sysdatabases
where name='tempdb'

Initially I preferred to put this blog post within Performance tuning blog section here, but as it relates to the TSQL script thought this is best place to go.

Anyways, if you have a performance problem the foremost option is to find whether the server resources are utilized properly or not, this is where SQL Server migth encounter the allocation bottleneck in terms of memory & query exeuction.

The following DMV will get the information about about the waits encountered by threads that are in execution. You can use this view to diagnose performance issues with SQL Server and also with specific queries and batches, by using the TSQL below you can identify the allocation bottleneck:

SELECT session_id, wait_duration_ms, resource_description

FROM sys.dm_os_waiting_tasks

WHERE wait_type LIKE 'PAGE%LATCH_%' AND resource_description like '2:%'

As you can see the above query will get information for TEMPDB that represents any page in TEMPDB acorss any file. Refer to Kalen Delaney's Inside SQL Server 2005: Query Tuning and Optimization book for  more information.

Second part of the subject refers about identifying the DDL bottlenecks, which is similar to finding the allocation contention by getting the current worker threads that are waiting by using the above TSQL.

Here is the kind of scripts defined within TEchnet articles about running black-box kind of traces against your SQL Server instance, then also you can  take advantage of the blackbox trace if you are facing intermittent problems, you want to make sure that it is always running when your server is running—including after either a planned or unplanned restart. To accomplish this, you can set the blackbox trace to start automatically when SQL Server starts. First, wrap the trace definition in a stored procedure in the master database:

 --Transact-SQL can be used to start a blackbox trace

USE master
GO
CREATE PROCEDURE StartBlackBoxTrace
AS
BEGIN
DECLARE @TraceId int
DECLARE @maxfilesize bigint
SET @maxfilesize = 25
EXEC sp_trace_create
@TraceId OUTPUT,
@options = 8,
@tracefile = NULL,
@maxfilesize = @maxfilesize
EXEC sp_trace_setstatus @TraceId, 1
END
GO

 Next, set the procedure to start automatically when the SQL Server service is started:

EXEC sp_procoption 'StartBlackBoxTrace', 'STARTUP', 'ON'

It may not be a hardcore requirement on day-to-day basis to drop all tables, views and stored procedures from a SQL Server database within your environment, but it will be handy to have such a code at your end when such task is required.

There are 2 ways to accomplish this, first using undocumented stored procedure such as 'sp_MSforeachtable' as follows:

exec sp_MSforeachtable "DROP TABLE ? PRINT '? to be dropped' "

Where the results will have all of the tables to be dropped, ok how about for views & stored procedure then. Here it goes:

create procedure Usp_DropAllSPViews
as

declare @name  varchar(100)
declare @xtype char(1)
declare @sqlstring nvarchar(1000)

declare AllSPViews_cursor cursor for
SELECT sysobjects.name, sysobjects.xtype
FROM sysobjects
  join sysusers on sysobjects.uid = sysusers.uid
where OBJECTPROPERTY(sysobjects.id, N'IsProcedure') = 1
  or OBJECTPROPERTY(sysobjects.id, N'IsView') = 1 and sysusers.name =
'USERNAME'

open AllSPViews_cursor

fetch next from SPViews_cursor into @name, @xtype

while @@fetch_status = 0
  begin
-- obtain object type if it is a stored procedure or view
   if @xtype = 'P'
      begin
        set @sqlstring = 'drop procedure ' + @name
        exec sp_executesql @sqlstring
        set @sqlstring = ' '
      end
-- obtain object type if it is a view or stored procedure
   if @xtype = 'V'
      begin
         set @sqlstring = 'drop view ' + @name
         exec sp_executesql @sqlstring
         set @sqlstring = ' '
      end

    fetch next from AllSPViews_cursor into @name, @xtype
  end

close AllSPViews_cursor
deallocate AllSPViews_cursor

 

Always test above script within your test or sample database and be satisfied with results to check, do not directly attempt on a live database that I will not give you any warranty or guarantee on above task. Do not forget to have a complete database backup that has been tested further with a restore on to another server, taking or keeping backup is not sufficient enough!!!

Sometimes you may want to run a query against each database

Suppose you want to find all dabase names where particular table exists. You can use undocumented procedure

EXEC sp_msforeachdb 'SELECT table_catalog FROM ?.INFORMATION_SCHEMA.TABLES where table_name=''your_table'''

while exists(Select * from sys.databases where database_id>@database_id)
Begin
        select @dbname=name,@database_id=database_id from sys.databases where database_id=@database_id
        EXEC('SELECT * FROM '+@dbname+'.INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''') 
        select @database_id=min(database_id) from sys.databases where database_id>@database_id
End

declare @sql varchar(max), @table_name varchar(100)
select @sql='', @table_name='your_table'
select  @sql=@sql+ 'SELECT table_catalog FROM '+name+'.INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''' from sys.databases
exec(@sql)