Search this site!

Here are my T-SQL scripts that demonstrate various techniques & some tricks. These scripts will run on all versions of SQL Server from 6.x to 7.0. Please note that these scripts do require some basic understanding of SQL. Hope you find these tidbits useful! Most of these scripts were posted as answers to questions on the MS SQL Server newsgroups and the Windows NT Magazine forums. I owe it to all those who posed these interesting questions. Hope these help you in some way!

 

Click here to download the SQL scripts.

1) How to count the number of lines in an external OS file? Will work for ASCII files only.

Counting lines in an external text file from T-SQL.sql

2) How to find the number of days in a month?

number of days in month.sql

3) This is the solution to the February Reader's Challenge in the SQL Server Magazine.

Solution to February Reader Challenge of SQLMAG.sql

4) How to generate a virtual table from 5 integer values?

SELECTING 5 integer variables as an INT column.sql

5) How to store different datatypes in a column & extract them?

Variant data type storage.sql

6) How to concatenate values from a column using a SELECT & UPDATE statement?

Pivot Table Example.sql

7) How to get the maximum date value in a group or NULL if present?

For getting max date or record with NULL values in a group.sql

8) How to get monday date given any date? This will work with the default SQL Server DATEFIRST setting & similar logic can be used to determine SUNDAY date etc.

Get MondayDate given a date - assuming sunday is the first day of the week.sql

9) How to copy text values from one table to another? This one demonstrates for a text value in a single row.

Copy Text Data from one column to another.sql

10) How to calculate AGE in years? The DATEDIFF function in MS SQL Server gives the distance between year boundaries & as such cannot be used to calculate the age.

Age Calculation in years.sql

11) How to get identity kind of values for each row in a query? This technique demonstrates how to use CROSS JOIN, sub-query for counting & does not use temporary tables.

For getting identity column using CROSS JOIN & sub-query technique.sql

12) How to determine the type of DML operation in a trigger that is coded for INSERT, UPDATE or DELETE?

How to determine the type of DML operation in a multi-statement trigger.sql

13) How to format int or decimal values with commas?

Formatting int & decimal values with commas.sql

14) How to print only the first value in a group of duplicates?

Getting only a header value for a column (with duplicates) kinda like a header.sql

15) How to retrieve individual octets from an IP address?

Retrieve Individual Octets from an IP address.sql

16) How to verify the validity of an IP address presented as a string? The IF part of the check can be used as part of a table's CHECK constraint also.

CHECK CONSTRAINT for verifying IP address values in a field.sql

17) How does decimal datatypes differ in their precision & scale?

Numeric precision example that shows -.0000 value.sql

18) How to determine the product of values in a column?

Product of values in a column.sql

19) How to find the matching set of recipes given the ingredients? This question was posed in the Winntmag SQL Server forum. See the corresponding thread for more details.

Recipe Query Example - Get Recipes for given ingredients.sql

20) How to retrieve rows based on a range? This solution doesn't use temporary tables or cursors.

Retrieving records based on a range or number.sql

21) How to search for strings within XML tags?

Searching for strings withing TAGS.sql

22) How to transpose columns as rows? This script also demonstrates how to use the CUBE operator.

Transposing columns as fields.sql

23) How to decode powers of 2 as columnar values or rows?

Decode powers of 2.sql

24) How to generate ids for each row - same as the identity column without using temporary tables?

Generating unique values or ids for rows in SELECT statement.sql

25) How to find the minimum value in 3 columns?

Min of columns.sql

26) How to find modulus of floating point numbers? This one doesn't calculate the real modulus but this script just demonstrates few techniques. Also contains a sample to find the values before & after the decimal point in a decimal value.

Modulus of Floating Point Numbers.sql

27) Yet another pivot table example.

Pivot table example - solution 1.sql

28) How not to handle NOT IN queries when there are NULL values?

Query to show the NOT IN problem when there are NULL rows in the IN clause & MAIN table.sql

29) How to rollup data in a table? There are several ways to do this, here is one way. I think there might be a more efficient way but I haven't worked on it yet.

Rolling up trick#1.sql

30) How to strip a text field into several varchar fields?

Text fields stripping into varchar variables.sql

31) How to define a column with a custom time format & verify validity of data using CHECK constraint?

Table that uses a custom time field (varchar) & checks validity of the datetime value.sql

32) How to transpose data in a table? This problem was posed in the MS SQL Server newsgroup.

Transistor data transposing problem.sql

33) How to compare rows in tables without writing a SELECT with all the conditions? Can be used for simple dirty work & of course if you are lazy to type.

Compare Rows in tables using ISQL & temp tables only.sql

34) How to simplify CHECK constraints? This is an example using some datetime based checks.

Some CHECK constraint example using datetime fields.sql

35) How to convert TIMESTAMP values to numeric?

Converting TIMESTAMP values to numeric.sql

36) How to re-sequence the values in an indexed field?

Resequencing values in an indexed field.sql

37) How to validate an IP address specified as a character string?

One single big IF check to make sure the IP address specified in a char field is OK.sql

38) How to rank items starting with highest value within each group?

Ranking from highest within each group.sql

39) How to forcefully disconnect users from a DB? This script also contains some timing features so that it doesn't run forever.

KILL all for DB.sql

40) How to find the greatest circle distance? Also shows how to do a simple UPDATE loop. Note that the greatest circle formula is an approximation only.

Greatest Circle Distance Calculation with a simple UPDATE loop.sql

41) How to concatenate values that are grouped by columns using SELECT statement alone? This method has several restrictions but serves to demonstrate some techniques.

Concatenating values grouped by columns.sql

42) How to pass a list of IDs & use it like an IN clause?

How to pass a list of IDs & simulate an IN clause.sql

43) How to perform DISTINCT queries on tables that contain BLOB fields like text/image.

Perform a DISTINCT query of tables with BLOB fields.sql

44) How to determine the table from which a trigger was fired in the trigger itself? This technique can be used to write a common SP that can use the table name identified thus.

Programmatically determine which table a trigger was called from.sql

45) How to transfer BLOB fields like text/image from one server to another in SQL60/65? The same can be done in SQL70 using distributed queries.

Transfer BLOB fields from one server to another.sql

46) How to perform case-sensitive search on strings in a case-insensitive server?

Case-Sensitive search on case-insensitive SQL installation.sql

47) How to strip money values from a specifically formatted string: "n...-X..."? n... indicates the money value with varying lengths & decimal points also. X... indicates the alpha-numeric string after the money value

Stripping money value from a string with non-numeric data.sql

48) How to add time values formatted as hh:mm & display as hh:mm?

How to add hh,mm formatted time values and get output as hh,mm.sql

49) How to perform array-like manipulations using the set-oriented SQL features.

How to simulate arrays in T-SQL using a temporary table.sql

50) Here is a sample SP that shows how to manipulate text / image data. For simple insert / update of BLOB data, the INSERT / UPDATE statement will suffice. UPDATETEXT or WRITETEXT statements are required when modification of existing data needs to be done.

Example that demonstrates how to manipulate BLOB data using T-SQL.sql

51) This is an example that shows how to do the opposite of pivot.

Create rows from columns - opposite of pivoting.sql

52) This is a simple routine that shows how to combine several binary values into a single image value. This is similar to string concatenation & this example just adds the bytes together as a stream.

Merge several varbinary data into a single image field like doing a string concatenation.sql

53) This T-SQL script shows several techniques to form complex dynamic SQL strings. These eliminate to some extent the coding involved in concatenating string values and making sure to add single quotes appropriately between values. The methods discussed can make the code simpler to read & is less error-prone. The solutions discussed here use similar logic i.e., using parameter markers in the dynamic SQL string and replacing them with the values using functions / SPs.

Methods to form complex strings for dynamic SQL execution.sql

54) Solving most problems using a relational technique is more elegant and better in terms of performance. This script shows few examples that uses a Numbers table to solve some common problems. Keep a table with Numbers & it will prove very handy.

Tricks using a numbers table.sql

55) Concatenating several column values as a comma-separated string is a often encountered scenario. This method shows one way to do this using COALESCE function.

Concat several address fields together (example).sql

56) Functions to encrypt data in columns are not available in SQL Server (except undocumented ones that are risky to use & not supported). Here is one that shows how to do this using XOR.

Example for encrypting data using XOR.sql

57) The ISNUMERIC function in SQL60/65/70 checks for decimal & integer values. Hence characters like D, E are valid float representations & similarly ','. This is a simple logic that can check only for numeric digits without using a loop of any kind.

Check a string for numeric digits only.sql

58) Here is one example of a correlated query. This example uses a table that contains messages for each user. This can be used to delete all but the 2 latest messages.

Delete all but the two latest messages for each user.sql

59) A relational technique to strip the HTML tags out of a string. This solution demonstrates how to use simple tables & search functions effectively in SQL Server to solve procedural / iterative problems.

Strip the tags out of a HTML string.sql

60) A sample script to count the number of occurrences of a particular string pattern in a text column. This demonstrates how to use PATINDEX with simple SELECT statements.

Counting ocurrences of a pattern in text data.sql

61) Ever wondered how SQL Server stores the datetime value in 8 bytes. Well, this code shows how to decode the values using simple mathematical operations.

Dissecting datetime value storage to individual dateparts.sql

62) Getting file details is easy using the undocumented extended SP 'xp_getfiledetails'. But you can't rely on this to be the same between versions of SQL Server or even service packs. So here is an alternative method using the standard NT commands. The undocumented SP is also shown for completeness.

File details using NT commands & undocumented SP.sql

63) An example for using a CASE expression in an ORDER BY clause. This solution solves the problem of sorting based on the numeric values in a column. The column contains strings of the format 'nnnXXXX' where 'n' represents a numeric digit & 'X' represents any non-numeric character.

Sorting only based on the numeric digits in a column containing alpha-numeric values of the form nnnXXX.sql

64) A simple JOIN that will determine gaps in a set of sequential values. This query will basically give the sequence number after which a gap is present.

Finding gaps in sequential numbers.sql

65) This method of dynamic SQL execution is not well-known. This will work from SQL60 & upwards. This is very useful when calling SPs dynamically with parameters of different datatypes, output parameters & return value.

Dynamic execution of SP ( This method is not well-known ).sql

66) This is the fastest way to get say only one matching address row for each person out of a set of duplicates. This logic assumes that you do not care which address row you want to display. It can be easily extended to include other checks say based on the last added address row etc.

Get only one matching row for each name from several address rows.sql

67) Some sample code that shows how to return progress info from long running SPs. This is useful for reporting purposes or to include other mechanisms to control the SP execution.

Sample for how to send progress info from long running sps.sql

68) This script shows how to obtain the maximum value of 5 columns. It is possible to do this using CASE statement but the expression gets quite lengthy. This shows an easy way to use the MAX function itself & this approach can be extended to as many columns as you want.

Maximum Value from 5 columns.sql

69) This T-SQL script shows how to calculate the number of days between a given date and the 3rd sunday of the current month. This can be easily modified to accomodate count till 3rd saturday or 3rd monday of current month & so on.

Number of days between a given date & 3rd sunday of current month.sql

70) This is 2 of the solutions to an ORDER BY problem posed in the microsoft.sqlserver.programming newsgroups. This demonstrates several powerful derived table techniques.

A tricky ordering problem.sql

71) SELECT statement that demonstrate how to strip individual values from a comma-separated string. This uses a table of Numbers to solve the procedural problem.

Stripping individual values from a comma-separated string using SELECT statement only.sql

72) A generic logic to search & replace certain characters in a string. This approach is flexible in the sense that new characters can be added for searching without modifying the logic. This will make the code more maintainable too.

Generic logic to search & replace characters in a string.sql

73) Another example that shows how to convert columnar values into individual rows.

Changing columns to rows - Example #2.sql

74) How to calculate the statistical mode of a set of values using a SELECT statement?

Statistical Mode Calculation.sql

75) This example shows how to generate sequence numbers within a group of values in a SELECT statement. With proper indexes & search conditions, the example using the SELECT statement with GROUP BY will be very efficient.

Generate sequence numbers within a group of values.sql

76) This trick shows how you can use a table of numbers to do some procedural logic in a SELECT statement. The example shows how a comma-separated string can be split into it's individual values. This logic can be used for any string of delimited values.

Split a character value in a column into several fixed length values.sql

77) This script demonstrates the wrong usage of UPDATE statement with a FROM clause esp. in case of a table with 1-to-many relationship with another tables(s).

Wrong usage of UPDATE FROM clause.sql

78) How to order rows from a query in random order? This method should be used with caution & only after testing.

Ordering rows randomly based on the identity column value.sql

79) A flight schedule ordering problem. This demonstrates the powerful derived table feature. More details regarding the problem is in the link.

Flight Schedule Ordering problem.sql

80) How can you generate various combinations of data from existing columns? This method shows one approach using the CUBE operator with the SELECT statement.

Generate combinations of data from columns.sql

81) How to generate duplicates of existing rows based on the count value in a column?

Generating duplicates of existing rows.sql

82) Yet another pivot table example #2.

Pivot Table Example #2.sql

83) Another pivot table example #3.

Pivot Table Example #3.sql

84) How to calculate the previous & current salaries for each employees from rows that contain the start & end date for each salary?

Previous & Current Salary calculation for Employees.sql

85) How to generate summary rows that contain say overal total of a particular column value? This shows one approach using COMPUTE clause & another using ROLLUP.

Summary Total Generation using ROLLUP.sql

86) How to convert integer values to hexadecimal strings in SELECT statement or computations or expressions in WHERE clause etc?

Int to hexadecimal string conversion using SELECT statement.sql

87) This scripts shows how to obtain minimum value of four 4 datatime values contained in local variables. This method can be adapted to any datatye supported by the the SQL Server MIN function. This technique basically eliminates the writing of series of IF..ELSE or CASE statements.

Minimum value from 4 variables - datetime example.sql

88) How to check for the existence of a file from SQL Server? This example uses the standard DIR command & the documented xp_cmdshell SP. The undocumented sp 'xp_fileexist' is also shown for completeness.

Check for existence of an OS file.sql

89) This sample shows how you can conditionally fire triggers. This example shows how to suppress the trigger logic while inserting data from a SP & allowing other inserts to go through fine. A SQL6x/70/2000 version of the example is shown first & another one using the SQL70/2000 cursor function is shown next.

Conditional Firing of Triggers.sql

90) Several solutions for a general itinerary problem. Please see the script for further comments & description of the tables etc.

Flight Itinerary Problem.sql

91) This example demonstrates how to generate some ranking column for string searches. This column can be something like the RANK column generated by the SQL70 Full-Text feature.

Custom Ranking for Search conditions ( Similar to Full-Text Rank column).sql

92) How to generate sequence numbers based on values in multiple columns? This is similar to doing an ORDER BY on the required columns & counting the rows from the top.

Generate sequence numbers based on a particular order of rows.sql

93) ANSI & T-SQL specific solutions for getting the 2nd recent date from a set of values. This can be extended to answer nth date from a set of values but the ANSI version gets unwieldy & has to be modified for bigger values.

Getting the second recent date from a set of values.sql

94) One solution for searching for combination of column values in different rows. Please see the script for more details & example.

Search for combination of column values in different rows.sql

95) How to generate combinations of values in SQL Server? Demonstrates the application of the CUBE operator for SELECT statement.

Generate combinations of data from columns #2.sql

96) A technique to search & remove dirty characters from a numeric string. This problem shows how you can simplify the solution by looking at only what is needed.

Strip dirty characters from a numeric string.sql

97) An aggregation problem in a topic, seminars & registration scenario.

Topic , Seminars & Registrants aggregation problem.sql

98) Solution for determining the top 3 scores for each player in a game. This script demonstrates several SQL techniques both ANSI & T-SQL specific.

Finding average of top 3 scores for each player.sql

99) A sample script that demonstrates how to perform case-sensitive search & replace on a case-sensitive server. This script demonstrates some of the least known features of the T-SQL string functions.

Case-sensitive replace for strings on case-insensitive server.sql

100) This example shows how date & time values can be stored separately using the SQL data types.

Storing date & time values separately.sql

101) One solution for getting rows based on the Anniversary date of a person. This is similar to calculating the birth date.

Get data for a given Anniversary date & Year.sql

102) This script shows a technique to perform effective date searches especially when searching on an indexed column.

How to form effective date only search condition.sql

103) Get NT account names of the server or domain using NT commands from T-SQL.

How to get NT Accounts from DOMAIN.sql

104) Demonstrates some powerful derived table & ANSI join features. The script shows how to generate combinations of values from 2 tables & match them against a third table.

Generate combinations of data from 2 tables & match against a combinations table.sql

105) This example solves the elements listing problem. More description to be provided soon.

Elements List Problem.sql

106) This example shows how to solve the Moving Average problem using a correlated query.

Moving Average Example #1.sql

107) This simple example shows a powerful way to manipulate values in text columns. This one can be used to determine the email address from a suitably formatted text value.

Stripping Email Address from a text or character value.sql

108) Solution to update the team placement in a league based on their points, budget & registration date.

Team Placement in a League.sql

109) How to check for the existence of a database on a server?

Check for existence of DB.sql
This page was last updated on April 28, 2006 05:15 PM.