 |
Changing columns to rows.
|
| Total Hit (1467) |
Another example that shows how to convert columnar values into individual rows.
|
Rating
 |
|
|
|
|
|
Generate sequence numbers within a group of values.
|
| Total Hit (1465) |
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.
|
| Rating
|
|
|
|
Wrong usage of UPDATE FROM clause.
|
| Total Hit (1521) |
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).
|
| Rating
|
|
|
|
|
|
Flight Schedule Ordering problem.
|
| Total Hit (1696) |
A flight schedule ordering problem. This demonstrates the powerful derived table feature. More details regarding the problem is in the link.
|
| Rating
|
|
|
|
Generate combinations of data from columns.
|
| Total Hit (2188) |
How can you generate various combinations of data from existing columns? This method shows one approach using the CUBE operator with the SELECT statement.
|
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Minimum value from 4 variables - datetime example.
|
| Total Hit (2070) |
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.
....Read More |
| Rating
|
|
|
|
|
|
Conditional Firing of Triggers.
|
| Total Hit (2082) |
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 show
....Read More |
| Rating
|
|
|
|
|
|
|
|
Counting ocurrences of a pattern in text data
|
| Total Hit (1436) |
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.
|
| Rating
|
|
|
|
|
|
File details using NT commands & undocumented SP.
|
| Total Hit (2291) |
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.
....Read More |
| Rating
|
|
|
|
|
|
Finding gaps in sequential numbers.
|
| Total Hit (2825) |
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.
|
| Rating
|
|
|
|
Dynamic execution of SP ( This method is not well-known ).
|
| Total Hit (2463) |
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.
|
| Rating
|
|
|
|
|
|
|
|
Maximum Value from 5 columns.
|
| Total Hit (1414) |
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.
....Read More |
| Rating
|
|
|
|
|
|
A tricky ordering problem.
|
| Total Hit (1749) |
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.
|
| Rating
|
|
|
|
|
|
Generic logic to search & replace characters in a string.
|
| Total Hit (1517) |
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.
|
| Rating
|
|