Very common short bits of code
I type these over and over and yet sometimes I have to look it up!
Qualify objects as needed.
Explicitly convert data types. Usually char(n), varchar(n), nvarchar(n), int, float, datetime, and money. CAST is standard, but CONVERT has extra features like styling. See CAST and CONVERT (Transact-SQL) [http://msdn.microsoft.com/en-us/library/ms187928.aspx].
SELECT CAST(12.35 AS int), GETDATE(), CONVERT(datetime, '2011-08-02T10:17:03.123', 126), -- ISO8601 CONVERT(varchar(30), GETDATE(), 126) -- The reverse of the previous line ;
Local variables are handy especially in stored procedures. SET is standard, but you can set multiple values with SELECT.
DECLARE @myVariable varchar(10), @x int, @y int; SET @myVariable = 'hi'; SELECT @x = 7, @y = 8;
Use this to temporarily disable the server from returning the number of rows affected by the SQL statement (i.e.
SET NOCOUNT ON
Use CASE like an inline lookup.
SELECT Name, Party = CASE Name WHEN 'R' THEN 'Republican' WHEN 'D' THEN 'Democrat' ELSE 'Other', END AgeGroup = CASE WHEN Age < 12 THEN 'Child' WHEN Age >= 12 AND < 20 THEN 'Teen' WHEN Age >=20 AND < 65 THEN 'Adult' WHEN Age >=65 THEN 'Senior' END FROM Voter
There is also the situation where MySQL will put in a "friendly" or "soft" line break within a field when you have stuff like a
\r\n in your code that is supposed to represent a Windows end of line (EOL) as opposed to be an actual EOL. This will occur in very particular circumstances. For HTML code in, say, a <pre>, try using stuff like Numeric Character References. EG:
\r\n. One cheapo mnemonic for this is SQL 92.
NULL and empty string
NULL is not the same as empty string (''). Situations with NULL and empty string are so prevalent that it's best to master it.
For an expression, return a default value if it's NULL.
select ISNULL(Size, 'Med') from x;
For an expression, return NULL if it's a particular value.
select NULLIF(Answer, '') from x;
If something may be NULL or empty string, then you can have it return a particular value.
To get the first non-NULL value from a list:
COALESCE(Ans1, Ans2, Ans3)
This next bit is used in apps before putting stuff into SQL. Often times you get data from a form control and pass it to SQL. Two common problems are empty strings and apostrophes. Alternatively, you may want to avoid NULLS and enter empty strings (and then decide on the SQL end), or you may want to detect the empty string and not enter anything at all (especially if the column in SQL has a default value).
The absence or presence of a row is roughly related:
declare @IsAtFake int; select @IsAtFake = 0; if exists( select id from User where id = @UserID and login like '%@fake.com' ) begin select @IsAtFake = 1; end
Given Date of birth, get age
c1 is a column/expression that holds the date of birth, then the following statement will select his/her age.
SELECT DATEDIFF(yy, c1, current_timestamp ) - CASE WHEN DATEADD(yy,datediff(yy,c1,current_timestamp),c1) > current_timestamp THEN 1 ELSE 0 END
Get info about databases on a server
The system stored procedure of
sp_databases returns name, size, and remarks about the database in an instance of SQL Server.
This SQL code provides additional info about the databases on a server:
use master select name, crdate, filename from sysdatabases order by name
Here are some system views that provide info about the databases on a SQL 2005 server:
select * from sys.databases select * from sys.database_files select * from sys.master_files
Get info about tables in a database
The system stored procedure of
sp_tables returns info about the tables in an instance of SQL Server.
Make the following stored procedure in your database and run it from there to get info about each table in that database.
CREATE procedure spTableStats as /* -- This code, for the current database, lists info about each of its tables: name, row count, column count, reserved KB, used KB, used percent, category, and description. Note that the latter 2 are extended properties of each table. Created by: George Hernandez Modified: 2001 September 21 */ -- -- Prepare variables and temp table set nocount on -- Don't say how many rows declare @SQL nvarchar(2000), @TableName sysname, @Columns int, @Rows int declare @ReservedKB int, @UsedKB int, @UsedPercent real declare @Category nvarchar(300), @Description nvarchar(300) if exists(select name from tempdb.dbo.sysobjects where name like '#tbl1%') drop table #tbl1 create table #tbl1(TableName sysname, Columns int, Rows int, ReservedKB int, UsedKB int, UsedPercent real, Category nvarchar(300), Description nvarchar(300)) -- Fill cursor then populate temp table with columns from extended properties. declare crsr1 cursor for select t1.TableName,t1.Columns,t1.Rows,t2.ReservedKB, t2.UsedKB, t2.UsedPercent from (select so.name as TableName, count(sc.name) as Columns, si.rowcnt as Rows from sysobjects as so join syscolumns as sc on (so.id=sc.id) join sysindexes as si on (so.id=si.id) where so.type='U' and so.name<>'dtproperties' and si.indid in(0,1) group by so.name,si.rowcnt) as t1 join (select so.name as TableName, sum(si.reserved*8) as ReservedKB, sum(si.used*8) as UsedKB , case sum(si.reserved) when 0 then 0 else cast(sum(si.used) as real)/cast(sum(si.reserved) as real)*100 end as UsedPercent from sysobjects as so join sysindexes as si on (so.id=si.id) where so.type='U' and so.name<>'dtproperties' group by so.name) as t2 on (t1.TableName=t2.TableName) -- /* open crsr1 fetch next from crsr1 into @TableName, @Columns, @Rows, @ReservedKB, @UsedKB, @UsedPercent while @@fetch_status>=0 begin select @Description=cast(value as nvarchar(300)) from ::fn_listextendedproperty('MS_Description','user','dbo','table',@TableName,null,null) if @Description is null or @Description='' or @@rowcount=0 select @Description='null' else select @Description=quotename(@Description, '''') select @Category=cast(value as nvarchar(300)) from ::fn_listextendedproperty('Category','user','dbo','table',@TableName,null,null) if @Category is null or @Category='' or @@rowcount=0 select @Category='null' else select @Category=quotename(@Category, '''') select @SQL=N'insert into #tbl1 ' +'select ' +quotename(@TableName,'''') +', '+cast(@Columns as nvarchar(50)) +', '+cast(@Rows as nvarchar(50)) +', '+cast(@ReservedKB as nvarchar(50)) +', '+cast(@UsedKB as nvarchar(50)) +', '+cast(@UsedPercent as nvarchar(50)) +', '+@Category +', '+@Description -- print @SQL execute(@SQL) fetch next from crsr1 into @TableName, @Columns, @Rows, @ReservedKB, @UsedKB, @UsedPercent end close crsr1 deallocate crsr1 select * from #tbl1 order by TableName drop table #tbl1 --*/ GO
This SQL code provides additional info about the tables on a SQL 2005 server:
select * from sys.tables
Get info about columns on a server
The system stored procedure of
sp_columns table returns info about the columns in a table of SQL Server.
-- Next: Columns in all the tables sp_columns @table_name=N'%' -- Next: Columns in specified table sp_columns tblX
EG: To return all info about all columns that have "ID" as part of its name, try this:
sp_columns @table_name=N'%', @column_name=N'%ID%' .
This SQL code gets info about each column in each table in that database.
select t.name as TableName, c.name as ColumnName, dt.name as DataType, c.length, c.prec as "precision", c.scale, c.isnullable from sysobjects as t join syscolumns as c on (t.id = c.id) join systypes as dt on (c.xtype = dt.xtype) where t.type ='U' and t.name not like 'dtproperties' and t.name not like '%tmp%' and dt.name not like '%sys%' order by t.name, c.name
However if your database happens to have user defined datatypes, then try this code suggested by Vadivel Mohanakrishnan of Vadivel.blogspot.com.
select so.name "Table Name", sc.name "Field Name", type_name (sc.xusertype) "Data Type", sc.length "Size", "Is Nullable" = case when sc.isnullable = 1 then 'Null' else 'Not Null' end from syscolumns sc, sysobjects so where so.type = 'U' and sc.id = so.id and so.status > 0
This SQL code provides additional info about the columns in a SQL 2005 server:
select * from sys.columns
Get info about indexes in a database
This SQL code gets info about all the indexes in a database.
select t.name as TableName, i.name as IndexName, case i.indid when 1 then 1 else 0 end as "IndexClustered", i.keycnt, i.rows from sysindexes as i join sysobjects as t on i.id = t.id where t.type ='U' and t.name not in('dtproperties', 'SomeTableYouDontWant', 'test') and i.rows > 0 and i.keycnt > 0 order by t.name, i.name
This SQL code gets the name of the columns used by all the indexes in a database.
select distinct t.name as TableName, i.name as IndexName , i.keycnt, ik.keyno, c.name from sysobjects as t join sysindexes as i on t.id = i.id join sysindexkeys as ik on i.indid = ik.indid join syscolumns as c on ik.colid = c.colid where t.type ='U' and t.name not in('dtproperties', 'SomeTableYouDontWant', 'test') and i.rows > 0 and i.keycnt > 0 and t.id = ik.id and t.id = c.id order by t.name, i.name, ik.keyno
Using @@Identity to get ID just made
This SQL code inserts a row into a table and returns the ID that was automatically generated.
CREATE PROCEDURE insAppointment @Category varchar(50) = NULL, @ScheduledDate datetime = NULL, @ScheduledTime datetime = NULL, @DrID varchar(9), @Note varchar(500) = NULL AS SET NOCOUNT ON INSERT INTO Appointment(Category, ScheduledFor, DrID_FK, Note) VALUES(@Category, @ScheduledDate+@ScheduledTime, @DrID_FK, @Note) SELECT @@IDENTITY AS 'Identity' SET NOCOUNT OFF GO
Here is some VBScript code that puts the ID generated into a variable.
sql= _ "spCCSI_insAppointment " & _ "@Category='" & strAppointmentCategory & "', " & _ "@ScheduledDate='" & strScheduledDate & "', " & _ "@ScheduledTime='" & strScheduledTime & "', " & _ "@Physician='" & strPhysician & "', " & _ "@Note='" & strNote & "'" Set rst = Server.CreateObject("ADODB.Recordset") rst.Open sql, cnn strAppointmentID = strPrep(rst(0))
In MySQL, it's much simpler.
insert into t2 values(null,79); -- insert null for auto_increment columns select last_insert_id(); -- to get latest ID select @@identity; -- to get latest ID as of MySQL 3.23.25 -- For multiple row inserts, last_insert_id() returns the 1st generated ID
Semi-related to this topic is inserting non-auto generated values into the ID column.
In SQL Server, you have to set
identity_insert first. Use this to enable you to insert values of your choice into an identity column (a field that automatically generates the ID). Only one table per session can have this on at a time so make sure you turn it off as soon as possible.
SET IDENTITY_INSERT t1 ON; insert into t1 (99, 'test'); SET IDENTITY_INSERT t1 OFF;
In MySQL, you just insert your value into the ID column as you would normally.
insert into t1 (99, 'test');
Select top and sum the rest
This SQL code gets the top twelve and sums and counts the rest. Fancy code is needed because UNION does not work well with multiple instances of ORDER BY.
This way uses a temporary table:
if object_id('tempdb..#temp') is not null drop table #temp create table #temp (ProcedureID int, CPT nvarchar(308), Charges money) --This is the top 12 insert into #temp (ProcedureID, CPT, Charges) select top 12 Fsvc.ProcedureID as ProcedureID, [Procedure] as CPT, sum(ChargeAmount) as Charges from fact_Service as Fsvc join dimension_Procedure as Dpro on (Fsvc.ProcedureID = Dpro.ProcedureID) group by [Procedure], Fsvc.ProcedureID order by sum(ChargeAmount) desc --This is the rest insert into #temp (ProcedureID,CPT,Charges) select cast(0 as int) as ProcedureID, cast( cast( (select (count(distinct fact_Service.ProcedureID)-12) from fact_Service) as nvarchar(308) )+ ' Other CPTs' as nvarchar(308)) as CPT, sum(Fsvc1.ChargeAmount) as Charges from fact_Service as Fsvc1 where Fsvc1.ProcedureID not in ( select top 12 Fsvc2.ProcedureID from fact_Service as Fsvc2 join dimension_Procedure as Dpro on (Fsvc2.ProcedureID = Dpro.ProcedureID) group by Fsvc2.ProcedureID order by sum(ChargeAmount) desc ) select * from #temp
This way uses a variable of type TABLE:
declare @myTable table(ProcedureID int, CPT nvarchar(308), Charges money) insert into @myTable (ProcedureID, CPT, Charges) select top 12 f.ProcedureID as ProcedureID, [Procedure] as CPT, sum(ChargeAmount) as Charges from fact_Service as f join dimension_Procedure as d on (f.ProcedureID = d.ProcedureID) group by [Procedure], f.ProcedureID order by sum(ChargeAmount) desc --select * from @myTable insert into @myTable (ProcedureID, CPT, Charges) select cast(0 as int) as ProcedureID, cast( cast( (select (count(distinct fact_Service.ProcedureID)-12) from fact_Service) as nvarchar(308) )+ ' Other CPTs' as nvarchar(308)) as CPT, sum(f1.ChargeAmount) as Charges from fact_Service as f1 where f1.ProcedureID not in ( select top 12 f2.ProcedureID from fact_Service as f2 join dimension_Procedure as d1 on (f2.ProcedureID = d1.ProcedureID) group by f2.ProcedureID order by sum(ChargeAmount) desc ) select * from @myTable
Using remote servers
In Microsoft SQL Server, to use a remote server you have to take two preliminary steps.
First, add a "Linked Server" to the master database on the local server. This is done using a specific system stored procedure. EG:
--This is for a server on the network exec master.dbo.sp_addlinkedserver @server=N'RemoteServerName', @srvproduct=N'SQL Server'
--This is for a server on the Internet exec master.dbo.sp_addlinkedserver @server=N'AliasForRemoteServer', @srvproduct=N'', @datasrc=N'SQLServerName.SiteName.com', @provider='SQLNCLI'
Second, provide a method for authenticating. EGs:
--This uses the user's credentials exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RemoteServerName' --This is useful if you are on server A and using linked server B.
--This sets up credentialing ahead of time exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RemoteServerName', @locallogin=NULL , @useself=N'False', @rmtuser=N'SQLLogin', @rmtpassword=N'SQLPassword' --This is useful if you are on server C, connecting to server A, --which in turn is using linked server B. [support.microsoft.com/...]
After that, it's simply a matter of fully qualifying objects on the linked server. EG:
select * from RemoteServerName.remoteDatabase.dbo.remoteTable
You can remove access to a remote server too. EGs:
--This removes default logins exec sp_droplinkedsrvlogin @rmtsrvname=N'RemoteServerName'
--This removes specific logins exec sp_droplinkedsrvlogin @rmtsrvname=N'RemoteServerName', @locallogin=NULL
--This removes the linked server and login exec sp_dropserver @server='RemoteServerName', @droplogins='droplogins'
These few lines help in getting your orientation when dealing with multiple servers:
select @@servername, @@version; select * from master.dbo.sysservers; -- MSSS 2000/v8+ select * from master.sys.servers; -- MSSS 2005/v9+
Search and replace within fields
This beautiful bit of SQL can save a lot of time. It's from Search & Replace in MySQL tables!! [blog.igeek.info/still-fresh/2005/08/31/search-replace-in-mysql-tables/]
update table1 set field1 = replace(field1, "old string", "new String")
Note that the above searches and replaces within each field. In contrast the following searches and replaces entire fields.
update table1 set field1 = "new String" where field1 = "old string"
Find a column or table in the database
Given a column name or part of a column name, this returns a list of table and column names where the column name matches the given column name.
select TableName=t.name, ColumnName=c.name from sysobjects t join syscolumns c on (t.id=c.id) where c.id in (select id from syscolumns where name like '%org%') and c.name like '%org%' and t.type='U'
Given a table name or part of a table name, this returns a list of matching table names.
select name from sysobjects where name like '%dress%' and type='U'
Find duplicates in tables
Sometimes duplicates are OK (EG: The User_FirstName column may have many duplicates), but sometimes duplicates are not OK (EG: The User_Login column should have no duplicates). It is best to programmatically prevent unwanted duplicates from entering the system in the first place but sometimes you have to deal with what you're given (EG: You might be have to merge a list of doctors from two meetings).
Here is some simple SQL to check for duplicates:
SELECT User_Login, COUNT(User_Login) AS Occurrences FROM User GROUP BY User_Login HAVING ( COUNT(User_Login) > 1 )
If the table has a multi-column identifier, use code like this:
SELECT FirstName, LastName, COUNT(*) AS Occurrences FROM Doctor GROUP BY FirstName, LastName HAVING ( COUNT(*) > 1 )
One simple way to get rid of true duplicates:
select distinct * into #holding from patient; truncate table patient; insert patient select * from #holding; drop table #holding;
Split column into first and last names
This assumes that the column to be split has data in this format: "Last Name, First Name" and that there is only one comma.
select LastName = left(LastFirstName,charindex(', ',LastFirstName)-1), FirstName = substring(LastFirstName,charindex(', ',LastFirstName)+2,len(LastFirstName))
It is much easier to do this sort of thing in Excel with Data > Text to Columns.
Get all values for a column as a list
Concatenate the values of a column as a delimited string. The limitation of this is the max size of varchar.
declare @array varchar(8000); set @array = ''; select @array = @array + SomeColumn from SomeTable; select @array;
SELECT T.ID TicketID , stuff( ( SELECT ',' + cast(ApplicationID as varchar(9)) FROM com.Ticket_Application A WHERE A.TicketID = T.ID ORDER BY A.ApplicationID FOR XML PATH('') ) , 1, 1, '' ) ApplicationIDs FROM com.Ticket T;
- "Generating Comma Separated list in TSQL by using XML Path" [http://blog.namwarrizvi.com/?p=140].
Select random row from a table
This assumes that the table has primary key ID integer column.
DECLARE @max int, @min int SELECT @max = MAX(MyTableID) FROM MyTable SELECT @min = MIN(MyTableID) FROM MyTable SELECT MyTableID FROM MyTable WHERE MyTableID = ROUND(@min + (RAND() * (@max-@min)),0)
This select several fairly random rows:
SELECT TOP 10 * FROM YourTable ORDER By NEWID()
Pad a value
It is common to pad an integer with zeroes or to pad a string with spaces on the left or right side. The latter is frequently used on older column counted data.
--Pad an integer with zeroes. In this case: Make a 4 digit PIN. select right(replicate('0',4) + cast(78 as varchar(4)), 4) select right('0000' + convert(varchar(4), 78), 4) --same thing
--Pad a string with spaces. In this case: Make a 16 character string. select right(space(16) + 'foo bar', 16)
Backup and restore
Backup frequently. Restore to recover a database or to copy a database. This sort of backup saves all the data, functions, stored procedures, views, etc.
backup database dbX to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\dbX_200610251613.bak'
restore database dbX from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\dbX_200610251613.bak' with move 'dbX_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\dbX.mdf', move 'dbX_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\dbX.ldf'
Swap the value of 2 columns
If you've put the values of one column in the other and vice versa, this bit of T-SQL can undo it.
declare @myTemp varchar(63); update employee set @myTemp=FirstName, FirstName=LastName, LastName=@myTemp;
Insert into an Identity column
Identity columns autmoatically generate values when you do an insert but sometimes you want to insert a specific value. The trick is to turn
IDENTITY_INSERT on for the table and then do the insert.
SET IDENTITY_INSERT IdentityTable ON INSERT IdentityTable (ID, SomeColumn) VALUES (99, 'Row with Identity column specified') SET IDENTITY_INSERT IdentityTable OFF
A few pointers:
- Don't forget to turn it off since it can on for only one table at a time.
- If it's on then you must provide a value for the Identity column.
- If the Identity value you insert is greater than the current seed, then it becomes the new seed.
Find rows in one table but not in another
For a single-column ID:
select * from a where not exists (select * from b where b.ID=a.ID)
For a multi-column ID:
select * from a where not exists (select * from b where b.ID=a.ID and b.foo=a.foo and b.bar=a.bar)
Generate random numbers
You can visit site like random.org for more about more sophisticated random number generation, but this bit of SQL that I got from "Random problems" By Chris Nowicki, 2009/06/09 [http://www.sqlservercentral.com/articles/Random+Numbers/66718/] meets my simple needs. The issue with rand() is that it defaults to seeding with the timestamp but SQL code runs so fast that it may use the same timestamp many times.
-- Generate a uniqueidentifier select newid(); -- For a random integer between 1 and 10 select Cast(10 * RAND(Checksum(Newid())) + 1 AS INT) AS Rnd;
Transform List into a Table
Take a list (array, CSV, etc) and return it as a table. There are many ways to do this, some more efficient than others.
CREATE FUNCTION [MySchema].[Split] ( @Array VARCHAR(MAX) , @Delimiter CHAR(1) = ',' ) RETURNS TABLE AS RETURN ( SELECT SeqNo - LEN(REPLACE(LEFT(@Array, SeqNo), @Delimiter, '')) + 1 AS Position , REPLACE(LTRIM(RTRIM(SUBSTRING(@Array, SeqNo, CHARINDEX(@Delimiter, @Array + @Delimiter, SeqNo) - SeqNo))), char(10), '') AS Element FROM MySchema.SequenceNumbers -- A table with 1 col, SeqNo, with values 1, 2, 3, ..., n (as high as you want) WHERE SeqNo <= LEN(@Array) AND SUBSTRING(@Delimiter + @Array, SeqNo, 1) = @Delimiter )