Browse by Tags

All Tags » sql server   (RSS)
Outputting DBCC results
26 July 08 09:29 AM | Madhivanan | 2 Comments   
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, Read More...
Ordering Interger values stored in Varchar column
21 July 08 12:21 PM | Madhivanan | 2 Comments   
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' Read More...
Filed under: , ,
Varchar(max) datatype and Replicate function
15 July 08 11:54 AM | Madhivanan | 2 Comments   
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 Read More...
Filed under: , ,
TSQL to get TOP 10 queries that are using I/O generation, SQL Server 2005
14 July 08 03:26 AM | SQL Master | 2 Comments   
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, Read More...
Quick and simple way to know the stored procedures parameters using TSQL
08 July 08 04:13 AM | SQL Master | 2 Comments   
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 Read More...
When was my Server restarted?
09 June 08 01:35 PM | Madhivanan | 2 Comments   
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...
Filed under: ,
SQL Server 2005 DMV - quick information to find resource allocation & DDL bottleneck
09 June 08 01:29 AM | SQL Master | 2 Comments   
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 Read More...
SQL Server: Creating BlackBox kind of trace with TSQL
28 May 08 04:59 AM | SQL Master | 2 Comments   
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 Read More...
How to drop all tables, all views, and all stored procedures from a SQL Server 2005 Database?
20 May 08 01:12 AM | SQL Master | 2 Comments   
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 Read More...
Simulating undocumented Procedures
19 May 08 12:00 PM | Madhivanan | 2 Comments   
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''' Read More...
Filed under: ,
SQL Server 2005 detect DAC session with TSQL
09 May 08 11:11 AM | SQL Master | 3 Comments   
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 Read More...
Object Catalog Views in SQL Server 2005
03 May 08 11:19 AM | Madhivanan | 2 Comments   
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...
Filed under: ,
SQL Server: Quick way to find Active & idle connections on a SQL instance
02 May 08 02:32 AM | SQL Master | 2 Comments   
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 Read More...
Populating sample data
04 April 08 12:15 PM | Madhivanan | 2 Comments   
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 , Read More...
Filed under: ,
Union Vs Union All
03 April 08 12:32 PM | Madhivanan | 2 Comments   
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 Read More...
More Posts Next page »