SQL Server Transact-SQL (SSQA.net)

Outputting DBCC results

2008-07-26T11:29:00Z

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...(read more)

Ordering Interger values stored in Varchar column

2008-07-21T14:21:00Z

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' --Method 1 select data from @t order by cast ( data as int ) --Method 2 select data from @t order by data + 0 --Method 3 select data from @t order by len ( data ), data --Method...(read more)

Varchar(max) datatype and Replicate function

2008-07-15T13:54:00Z

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...(read more)

TSQL to get TOP 10 queries that are using I/O generation, SQL Server 2005

2008-07-14T10:26:00Z

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)...(read more)

Quick and simple way to know the stored procedures parameters using TSQL

2008-07-08T11:13:00Z

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...(read more)

When was my Server restarted?

2008-06-09T15:35:00Z

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'...(read more)

SQL Server 2005 DMV - quick information to find resource allocation & DDL bottleneck

2008-06-09T08:29:00Z

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...(read more)

SQL Server: Creating BlackBox kind of trace with TSQL

2008-05-28T11:59:00Z

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...(read more)

How to drop all tables, all views, and all stored procedures from a SQL Server 2005 Database?

2008-05-20T08:12:00Z

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...(read more)

Simulating undocumented Procedures

2008-05-19T14:00:00Z

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''' Because they are undocmented, you cant always rely on them. Alternatively you can use the following methods 1 WHILE LOOP declare @dbname varchar ( 100 ), @database_id int , @table_name varchar ( 100 ) select @dbname = '' , @database_id...(read more)

SQL Server 2005 detect DAC session with TSQL

2008-05-09T18:11:00Z

SQL Server 2005 has introduce a secret-door for Admins to identify and resolve any connect lockout issues within your database instance, as on http://sqlserver-qa.net/blogs/tools/archive/2007/08/04/dedicated-administrator-console-dac-saved-an-important-day-for-a-dba.aspx how. Use the following query to detect it Select t2.session_id, t2.program_name, t2.host_name, t1.name FROM sys.tcp_endpoints as t1 JOIN sys.dm_exec_sessions as t2 ON t1.endpoint_id=t2.endpoint_id WHERE t1.name=‘Dedicated Admin Connection...(read more)

Object Catalog Views in SQL Server 2005

2008-05-03T13:19:00Z

In addition to INFORMATION_SCHEMA VIEWS, in SQL Server 2005, we can use Object Catalog Views to know more informations about the objects Refer this http://msdn.microsoft.com/en-us/library/ms189783.aspx...(read more)

SQL Server: Quick way to find Active & idle connections on a SQL instance

2008-05-02T09:32:00Z

Using SQL Server 2005 you can take help of DMV - sys.dm_exec_connections that will give server-level information about the connections. In the olden days usage of sysprocesses system table used to be heavy and same approach can be achieved by using system DMVs within 2005 version onwards. All these DMVs - sys.dm_exec_connections , sys.dm_exec_sessions , and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses system table. So if you have any adhoc code that used to get connections...(read more)

Populating sample data

2008-04-04T14:15:00Z

Sometimes you may need some sample data for testing purpose The following may help you in generating some sample data of different datatypes select abs ( checksum ( newid ()))% 10000 as intcol , abs ( checksum ( newid ()))* rand ()/ 100 as float_col , dateadd ( day , 0 , abs ( checksum ( newid ()))% 100000 ) as date_col , substring ( replace ( cast ( newid () as varchar ( 36 )), '-' , '' ), 1 , abs ( checksum ( newid ()))% 15 ) as varchar_col , abs ( checksum ( newid ()))% 2 as bit_col from master...(read more)

Union Vs Union All

2008-04-03T14:32:00Z

Well. Most of you know the diffrence between the two. http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/ http://www.codethinked.com/post/2007/11/UNION-versus-UNION-ALL2c-lessons-in-minutiae.aspx Here is one of the differences that is not known by newbies When you use UNION, you cant include any column of type TEXT and NTEXT. But it is possible if you use UNION ALL Declare @test table ( i int , text_col text ) insert into @test select 1 , 'some test...(read more)