Browse by Tags

All Tags » T-SQL (RSS)
Time Calculation on Numbers
Published 15 November 8 3:18 AM | SqlServer-QA.net
In Forums sometimes I read questions on how to find out the difference between two times which are stored as float Here are some possible answers declare @t1 char ( 5 ), @t2 char ( 5 ), @sum float select @t1 = '12.56' , @t2 = '7.58' set...
Filed under: , ,
SQL_VARIANT_PROPERTY function
Published 1 November 8 4:47 AM | SqlServer-QA.net
If you want to know the datatype or length of a column , you can query on the system table syscolumns or system view INFORMATION_SCHEMA.COLUMNS But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function Example SELECT TOP 1...
Filed under: , ,
Export to EXCEL with column names
Published 14 October 8 5:3 AM | SqlServer-QA.net
In the post Import/Export to Excel , I showed how to export data to EXCEL The problem that most users find it is it wont include column names in the file and file should exists already with headings This procedure would solve that problem create procedure...
Filed under: , , ,
Return TOP N rows
Published 15 September 8 9:22 AM | SqlServer-QA.net
The TOP Clause returns top rows from the table based on the number or percentage value What if you want to have TOP N rows for each group? The following explains it ( The purpose is to return top 3 orders for each customer based on the most recent orderdate...
Filed under: , ,
Splitting delimited data to columns - Set based approach
Published 11 September 8 9:58 AM | SSQA.net
In this post splitting-csv-to-columns I showed how to split delimited data into multiple columns Simon in his post Set-based-splitting-of-delimited-strings-to-columns showed how to use that in a set based approach which would work on a set of data.In...
Filed under: , , , ,
Should alias names be preceded by AS? - Part 2
Published 9 September 8 9:10 AM | SSQA.net
In the Part 1 , I expressed my opinion on having the alias names preceded by the keyword AS Adding to the examples given at the link, consider this example too SELECT 10number , 10.number , 10 number , 10 [number] , 10[number] , 10 as number It gives...
Filed under: , ,
Fun with GO
Published 5 September 8 5:58 AM | SSQA.net
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 )...
Filed under: , ,
Empty string and Default values
Published 2 September 8 5:56 AM | SSQA.net
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...
Filed under: , ,
Dynamic PIVOT in SQL Server 2005
Published 27 August 8 9:58 AM | SSQA.net
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...
Filed under: , , , ,
Common migration issues from version 2000 to 2005
Published 12 August 8 4:12 AM | SSQA.net
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...
Filed under: , ,
Another use of GO command in SQL Server 2005
Published 6 August 8 4:7 AM | SSQA.net
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...
Filed under: , ,
Outputting DBCC results
Published 26 July 8 6:29 AM | SSQA.net
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...
Filed under: , ,
Ordering Interger values stored in Varchar column
Published 21 July 8 9:21 AM | SSQA.net
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'...
Filed under: , ,
Varchar(max) datatype and Replicate function
Published 15 July 8 8:54 AM | SSQA.net
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...
Filed under: , ,
Simulating undocumented Procedures
Published 19 May 8 9:0 AM | SSQA.net
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...
Filed under: ,
Object Catalog Views in SQL Server 2005
Published 3 May 8 8:19 AM | SSQA.net
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 ) Read More...
Filed under: ,
Populating sample data
Published 4 April 8 9:15 AM | SSQA.net
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...
Filed under: ,
Union Vs Union All
Published 3 April 8 9:32 AM | SSQA.net
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...
Filed under: , ,
Splitting csv to columns
Published 15 March 8 6:10 AM | SSQA.net
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...
Filed under: , , ,
Understanding Single quotes within a SELECT statement
Published 19 February 8 1:19 PM | SSQA.net
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...
Filed under: , , , , ,
More Posts Next page »