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.
2) How to find the number of days in a month?
3) This is the solution to the February Reader's Challenge in the SQL Server Magazine.
4) How to generate a virtual table from 5 integer values?
5) How to store different datatypes in a column & extract them?
6) How to concatenate values from a column using a SELECT & UPDATE statement?
7) How to get the maximum date value in a group or NULL if present?
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.
9) How to copy text values from one table to another? This one demonstrates for a text value in a single row.
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.
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.
12) How to determine the type of DML operation in a trigger that is coded for INSERT, UPDATE or DELETE?
13) How to format int or decimal values with commas?
14) How to print only the first value in a group of duplicates?
15) How to retrieve individual octets from an IP address?
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.
17) How does decimal datatypes differ in their precision & scale?
18) How to determine the product of values in a column?
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.
20) How to retrieve rows based on a range? This solution doesn't use temporary tables or cursors.
21) How to search for strings within XML tags?
22) How to transpose columns as rows? This script also demonstrates how to use the CUBE operator.
23) How to decode powers of 2 as columnar values or rows?
24) How to generate ids for each row - same as the identity column without using temporary tables?
25) How to find the minimum value in 3 columns?
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.
27) Yet another pivot table example.
28) How not to handle NOT IN queries when there are NULL values?
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.
30) How to strip a text field into several varchar fields?
31) How to define a column with a custom time format & verify validity of data using CHECK constraint?
32) How to transpose data in a table? This problem was posed in the MS SQL Server newsgroup.
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.
34) How to simplify CHECK constraints? This is an example using some datetime based checks.
35) How to convert TIMESTAMP values to numeric?
36) How to re-sequence the values in an indexed field?
37) How to validate an IP address specified as a character string?
38) How to rank items starting with highest value within each group?
39) How to forcefully disconnect users from a DB? This script also contains some timing features so that it doesn't run forever.
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.
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.
42) How to pass a list of IDs & use it like an IN clause?
43) How to perform DISTINCT queries on tables that contain BLOB fields like text/image.
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.
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.
46) How to perform case-sensitive search on strings in a case-insensitive server?
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
48) How to add time values formatted as hh:mm & display as hh:mm?
49) How to perform array-like manipulations using the set-oriented SQL features.
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.
51) This is an example that shows how to do the opposite of pivot.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
73) Another example that shows how to convert columnar values into individual rows.
74) How to calculate the statistical mode of a set of values using a SELECT statement?
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.
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.
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).
78) How to order rows from a query in random order? This method should be used with caution & only after testing.
79) A flight schedule ordering problem. This demonstrates the powerful derived table feature. More details regarding the problem is in the link.
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.
81) How to generate duplicates of existing rows based on the count value in a column?
82) Yet another pivot table example #2.
83) Another pivot table example #3.
84) How to calculate the previous & current salaries for each employees from rows that contain the start & end date for each salary?
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.
86) How to convert integer values to hexadecimal strings in SELECT statement or computations or expressions in WHERE clause etc?
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.
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.
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.
90) Several solutions for a general itinerary problem. Please see the script for further comments & description of the tables etc.
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.
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.
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.
94) One solution for searching for combination of column values in different rows. Please see the script for more details & example.
95) How to generate combinations of values in SQL Server? Demonstrates the application of the CUBE operator for SELECT statement.
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.
97) An aggregation problem in a topic, seminars & registration scenario.
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.
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.
100) This example shows how date & time values can be stored separately using the SQL data types.
101) One solution for getting rows based on the Anniversary date of a person. This is similar to calculating the birth date.
102) This script shows a technique to perform effective date searches especially when searching on an indexed column.
103) Get NT account names of the server or domain using NT commands from T-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.
105) This example solves the elements listing problem. More description to be provided soon.
106) This example shows how to solve the Moving Average problem using a correlated query.
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.
108) Solution to update the team placement in a league based on their points, budget & registration date.
109) How to check for the existence of a database on a server?
|
