These scripts demonstrate some of the new features in SQL70, tricks to solve some common problems etc. These scripts were accumulated from my various postings on the MS SQL newsgroups & the Windows NT Magazine newsgroups. For more details, you can search these resources. If you have any questions or comments, send an email.
Thanks for checking these out!
Click here to download the SQL scripts.
1) How to use fully scrollable cursors?
2) How to determine the number of triggers & their firing order?
3) How to solve some ranking problem? This question was posed in the Winntmag forum. See the thread for more details.
4) How to get output value from a dynamic SQL statement?
5) How to generate a header record for the data in a table that can be BCPed out?
6) How to order values in a sequence using an indexed update?
7) How to get tokens from a delimited string? This also shows how to generate some simple statements using T-SQL rather than coding them manually.
8) How to convert varbinary values into their hexadecimal string representation?
9) How to generate SELECT statements for tables?
10) How to pipe the RESTORE statement output to a temporary table?
11) How to determine all the defined identity columns in a database?
12) Information on undocumented DBCC commands. Some of these commands will work in SQL60/65 also. These commands are intended for understanding the internal working of the database system while some are purely academic in nature.
13) How to use ODBC style canonical functions in T-SQL? The OLE-DB provider for SQL Server fully supports the ODBC canonical functions. This allows you to for instance encrypt data using the ODBC ENCRYPT function or do other operations. This can be used in distributed queries also.
14) How to join results of a SP with another table?
15) How to get the data for all the saved diagrams in a database? I have a sample script that uses linked server to import diagrams from a different server. This can be moved to a different server using DTS or BCP also.
16) How to get records with the TOP 2 dates in a set of values?
17) How to generate random values using RAND & NEWID function?
18) How to determine the size taken by the auto-statistics in a database?
19) How to set user-defined counters?
20) How to pad decimal values with zeroes & to a fixed length for say exporting the data?
21) This script demonstrates how to use the getchecksum function. This is undocumented & i do not recommed using it. This is provided for the curious folks who wants to know how this function works. This function is also used in the replication procedures.
22) How to evalulate an expression in a string & get the value?
23) How to perform SELECT..INTO type of queries for results from a SP? This is useful in cases where you do not know the metadata of the result set & do not want to write the CREATE TABLE statement before doing the INSERT..EXEC. This can be used in a easy way to say save the data from a SP in a table.
24) This script shows that a primary data file is required for a active log backup. Keep this in mind when recovering suspect databases, hardware failures etc. For more details, see a discussion on the same in the Windows 2000 Magazine SQL Server forum.
25) This sample script shows how to validate the a GUID value in a string. This can be used to ensure that the value is in the correct format & with only hex digits.
26) Code to convert a hexadecimal value stored in a string to integer.
27) Script to simplify creation of backup files with timestamp just like the SQLMAINT utility. This script also does differential backups during the week & full backup on sundays. You can use this in a job to automate the backup process quite easily & add your own administrative routines.
28) This example shows how to read, write to the NT registry from T-SQL. Please note that these SPs are undocumented & hence should not be used in any production code.
29) Statements to list the filegroup on which a table resides in a database. This can be used in administrative routines.
30) How can you check if a row(s) is locked or not & take action appropriately? The examples in the script use the new optimizer hint READPAST to achieve the result. A method using the lock timeout mechanism is also shown for completeness. These methods will allow the required SPs to check for locked rows & then take action rather than waiting for the lock to get released.
31) How to determine if a system table is materialized internally by SQL Server or if it is stored in the database?
32) This script shows several solutions to solve a problem relating to counting. Analysis of the solutions is left to the reader but sample output from several runs are shown & the solutions are analyzed in the script.
33) This sample script shows how to use the COLUMNS_UPDATED() function to check for update of specific column(s). This generic approach can be used on tables with more than 8 columns. The required formula for calculating the mask & getting the corresponding byte from the function is also explained in the script.
34) How to generate a simple ALTER TABLE script to disable all FK constraints in a database?
35) How to get a random row from duplicates?
36) This script shows how to use a cursor to store the @@IDENTITY values inside trigger & use them later in the calling SPs.
37) How to use the INFORMATION_SCHEMA views & the meta-data functions to determine all the primary keys in a database?
38) This script can be used to delete orphaned users in a restored database on a different Server. This can happen due to missing logins in the new server for instance.
39) T-SQL string manipulation functions are limited yet powerful if used properly. This script shows how to count occurrences of a string / character in a string, find the number of values in a delimited string & so on.
40) A sample script that shows the new INDEXPROPERTY metadata function usage. This script produces several interesting reports!
|
