Browse by Tags

All Tags » T-SQL   (RSS)
Fun with GO
05 September 08 08:58 AM | Madhivanan | 2 Comments   
In this blog post , I explained different uses of GO command Here is a Fun when you use GO as object name CREATE PROCEDURE GO ( @I INT ) AS SELECT @I AS number Well. The procedure is created and let us try to execute GO 2 What we see is Command ( s ) Read More...
Filed under: , ,
Empty string and Default values
02 September 08 08:56 AM | Madhivanan | 2 Comments   
Often newbies use empty string and consider it as NULL. But when an empty string is passed, it may not be stored as empty string or NULL. It actually depends on the datatype of the column Consider this example DECLARE @t TINYINT , @s SMALLINT , @i INT Read More...
Dynamic PIVOT in SQL Server 2005
27 August 08 12:58 PM | Madhivanan | 2 Comments   
The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results Consider this example select * from ( select Year ( OrderDate ) as pivot_col , e . lastname , o . OrderDate FROM northwind .. Employees as e INNER JOIN northwind Read More...
Common migration issues (version 2000 to 2005)
12 August 08 07:12 AM | Madhivanan | 2 Comments   
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 Read More...
Filed under: , ,
Another use of GO command in SQL Server 2005
06 August 08 07:07 AM | Madhivanan | 2 Comments   
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 Read More...
Filed under: , ,
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: , ,
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: ,
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: ,
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...
Splitting csv to columns
15 March 08 09:10 AM | Madhivanan | 2 Comments   
Suppose you have string in csv format and want to split into seperate columns. You can use parsename function as long as you have maximum four values. The following would work for any number of values declare @s varchar ( 2000 ), @data varchar ( 2000 Read More...
Understanding Single quotes within a SELECT statement
19 February 08 12:19 PM | Madhivanan | 3 Comments   
This is for newbies who struggle to understand how single quotes work in SQL Server I have seen newbies worrying why the following doesnt work SELECT columns from mytable where col = 'Lifco' s ' When you specify a value which has single quote , you need Read More...
Forcing integrity between tables and procedures
17 January 08 01:09 PM | Madhivanan | 2 Comments   
You can force integerity between tables and views with schemabinding when you create views so that tables cant be dropped until views that bind on tables are dropped. Unfortunately it is not possible to force this integrity between tables and procedures. Read More...
Filed under: , ,
More Posts Next page »