Browse by Tags

Simulating undocumented Procedures

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...

Posted to Madhivanan (Weblog) by Madhivanan on 05-13-2008
Object Catalog Views in SQL Server 2005

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

Posted to Madhivanan (Weblog) by Madhivanan on 05-03-2008
Populating sample data

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 ()))%...

Posted to Madhivanan (Weblog) by Madhivanan on 04-04-2008
Union Vs Union All

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...

Posted to Madhivanan (Weblog) by Madhivanan on 04-03-2008
Splitting csv to columns

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 ) select @s = 'this,is,test' select @data = '''' ...

Posted to Madhivanan (Weblog) by Madhivanan on 03-15-2008
Understanding Single quotes

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 to double it SELECT columns from mytable where...

Posted to Madhivanan (Weblog) by Madhivanan on 02-19-2008
Forcing integrity between tables and procedures

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. Tables can be dropped though there are procedures...

Posted to Madhivanan (Weblog) by Madhivanan on 01-17-2008
Beware of Implicit conversions

Sometimes you may wonder why integer divisions are not giving exact result Consider this example Select 3/4 Select 4/3 Because both numerator and denominators are INTegers, results 0.75, 1.3333 are truncated to integers resulting 0 and 1 respectively Suppose you want to find out percentage number of...

Posted to Madhivanan (Weblog) by Madhivanan on 01-16-2008
Using Recursive CTEs to compute darts combinations

In my first blog post I will explain Common Table Expressions (CTEs) in T-SQL queries and the powerful feature of recursive CTEs, i.e., CTEs that reference themselves. The game of darts provides a great example to illustrate these concepts. When two players play a game (leg) of darts, the purpose is...

Posted to Jorginho on T-SQL & Business Intelligence (Weblog) by Jorginho on 01-08-2008
Remove duplicate characters from a string

Pinal Dave in his weblog posted about Remove Duplicate Chars From String Here is its alternate method with Number table approach create procedure remove_duplicate_characters ( @string varchar ( 100 )) as Declare @result varchar ( 100 ) set @result = '' select @result = @result + min ( substring ( @string...

Posted to Madhivanan (Weblog) by Madhivanan on 12-29-2007

Page 1 of 2 (12 items) 1 2 Next >