Structured Query Language

Interface Language for retrieving data from, er, databases.

 

MSN Books Online

 

A Visual Explanation of SQL Joins

 

Joe Celko’s SimpleTalk columns

 

 

It should possibly be noted that just about all of these notes and links come from my experience with MS-SQL server, a glancing familiarity with MySQL and a complete unfamiliarity with Oracle. And NoSQL? uh, whut?

 

 

Books

Joe Celko’s “SQL Programming Style” - have not read
Joe Celko’s “SQL for Smarties: Advanced SQL Programming” - I’m reading the 4th edition

 

SQL Weekend Crash Course - Years ago, I got it at a discount store for $5. Well worth the money. Never cracked-open the CD.

 

 

SQL Server versions

Sql Server Versions

 

What version do I have?

 

Get the version via checking the following registry key:

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion

 

 

Style

Wiki:SqlCodingStyle
Stack Overflow: various SQL coding style guidelines

 

 

JOINs

Wikpedia: Join (SQL)
SQL-tutorial.net: SQL Join
SO: SQL JOIN ON vs WHERE
Coding Horror: visual explanation of SQL JOINs

 

 

general db notes

 

If a field is set as small date-time, you cannot enter an invalid, blank or NULL value.

 

As of IA 5.3, setting a default of NULL is now an option in IAXODBC2

 

all sp's begin with up_
table names have Underscore between words
fieldnames are camel case
sp's follow same rules as tables with naming convention
keep columns consistent across tables

 

 

Installing SQL Server database from a .bak file

Installing A SQL Server Database From A BAK File

 

-- step 1
USE master
GO

CREATE DATABASE MYDB
ON (
        NAME = MYDB,
        FILENAME = '\path\to\hf_new\MYDB.mdf',
                SIZE = 300MB
        )
LOG ON (
        NAME = MYDB_log,
        FILENAME = '\path\to\hf_new\MYDB.ldf',
        SIZE = 100MB
        )
GO

-- step 2
-- only the two "LogicalNames" are important
RESTORE FILELISTONLY
        FROM DISK = '\path\to\hf_new\MYDB.bak'
GO

--step 3
RESTORE DATABASE MYDB
        FROM DISK = '\path\to\hf_new\MYDB.bak'
        WITH MOVE 'MYDB' TO '\path\to\hf_new\MYDB.mdf',
                 MOVE 'MYDB_log' TO '\path\to\hf_new\MYDB.ldf',
                 REPLACE
GO

 

The steps worked great. However, the code uses C:\temp by default, and I followed it to the letter the first time I ran it, not knowing if SQL-Server would move the files to another location or not. Turned out to be not.

 

So, I had to move the files

 

Here is a semi-automated script to generate the above:

 

 

-- Michael Paulukonis 2012
-- notes @ http://www.xradiograph.com/Programming/SQL
-- adapted from http://sqlanywhere.blogspot.com/2011/06/installing-sql-server-database-from-bak.html

-- ASSUMES the BAK file and dest db have the same names

-- step2 is not grabbing the information we need --
-- it's a purely visual remnant of the manual process
-- TODO: fix this

-- original version scripted a DROP of existing table
-- I've removed it from here, in order to force a manual backup-vs-delete decision


-- work in master
USE master
GO


DECLARE @dbname AS VARCHAR(25)
SET @dbname = 'mydb'

DECLARE @bakpath AS VARCHAR(100) -- end path with slash
SET @bakpath = '\path\to\bak\'

DECLARE @dbpath as VARCHAR(100) -- end path with slash
SET @dbpath = '
\path\TO\destination\'

-- EXEC when you are confident
-- PRINT when you want to see the output (and maybe manually tweak/test)

EXEC (
'

-- step 1
CREATE DATABASE ' + @dbname + '
ON (
        NAME = ' + @dbname + ',
        FILENAME = ''' + @dbpath + @dbname + '.mdf'',
                SIZE = 300MB
        )
LOG ON (
        NAME = ' + @dbname + '_log,
        FILENAME = ''' + @dbpath + @dbname + '.ldf'',
        SIZE = 100MB
        )
--GO

-- step 2
-- only the two "LogicalNames" are important
-- TODO: uh, we are not actual grabbing these with this method....
-- ASSUMPTION: names are [dbname] and [dbname]_log
RESTORE FILELISTONLY
        FROM DISK = ''' + @bakpath + @dbname + '.bak''
--GO

--step 3
RESTORE DATABASE ' + @dbname + '
        FROM DISK = ''' + @bakpath + @dbname + '.bak''
        WITH MOVE ''' + @dbname + ''' TO ''' + @dbpath + @dbname + '.mdf'',
                 MOVE ''' + @dbname + '_log'' TO ''' + @dbpath + @dbname + '.ldf'',
                 REPLACE
--GO
'

)

 

 

moving database files to another location

And possibly renaming them (step 2)

 

-- Move database files
-- http://support.microsoft.com/kb/224071

-- Step 1: detach the database
USE master
go
sp_detach_db 'dbname'
go


-- Step 2: copy the .mdf and .ldf files from original location to new location
--         you can rename them at this point


-- Step 3: attach the newly copied files to the database
USE master
go
sp_attach_db 'dbname', 'c:\path\to\filename.mdf', 'c:\path\to\filename.ldf'
go

-- NOTE: .mdf and .ldf files do NOT have to match the DB-name
 

 

 

Stored Procedures

aka “sproc”
Always use ‘em, if possible

 

 

Wildcards

% = match any characters, multiple times
_ = match any single character, once

 

To match actual instances of “%” or “_” in a query, surround them in brackets, as follows:

 

SELECT BatchName, DCN, FieldName
FROM Audit_2
WHERE AuditOperator = '<name>'
        AND (FieldName LIKE 'F1[_]%' OR FieldName LIKE 'F2[_]%')
ORDER BY ID DESC;

 

 

Save not permitted

MSDN: Save (Not Permitted) Dialog Box

 

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

 

 

Sample queries

These are not presented as beginners queries (though they may be) to teach basic SQL.
Rather, these are snippets I found useful at various times, and kept forgetting.
Maybe I remember them, maybe I don’t.
Here they are.

 

ALTER TABLE (Transact-SQL) possibilities

 

Retrieve duplicate entries:

 

SELECT COUNT(*) AS Expr2, DCN FROM Document_Export GROUP BY DCN HAVING COUNT(DCN) > 1;

 

SELECT * FROM document WHERE barcode IN
(SELECT barcode FROM document
GROUP BY barcode
HAVING count(barcode) > 1)
ORDER BY barcode

 

Delete duplicate entries

SQL SERVER – Delete Duplicate Records – Rows


--DELETE
--FROM MyTable
--WHERE ID NOT IN
--(
--SELECT MAX(ID)
--FROM MyTable
--GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

DELETE
FROM Exported_Data
WHERE ID NOT IN (
        SELECT MAX(ID)
        FROM Exported_Data
        GROUP BY DCN
        );

 

If you follow the logic, it deletes all records that appear more than once, that are not the MAXimum value; CreateDate could also be used, if ID is not present.

 

An alternate method uses DELETE TOP(COUNT(*)-1)

 

Returning the autoincrementing column of a newly added record.

The following Query will add a new record and then return (as per a select statement) the value assigned to the ID column (that is, the column that automatically provides a unique ID for each new record).

 

INSERT INTO TestTable (Test, Test2) VALUES ('Foo','Foo2') SELECT @@IDENTITY AS returnId,
Test FROM TestTable WHERE ID = @@Identity;

 

Replace portions of fields

UPDATE tblLinks SET title = REPLACE(title, 'Welcome to', '<replace with this string>')

 

Get all info on tables

sp_help table_name

 

see also: http://msdn.microsoft.com/en-us/library/ms187335.aspx

 

Enter a NULL value (in EnterpriseManager)

  • When you manually update table data in SQL Server Enterprise Manager press Ctrl+0 [zero] to insert a NULL value.
  • This works with SQL 2005 Management Studio as well.
  • source

 

use variables

DECLARE @LName varchar(20)
SET @LName = 'Florange'
DECLARE @FName varchar(15)
SET @FName = 'Bruce'

 

SqlStringManipulation (longer article)

 

get date (with no time) from DateTime

SELECT (CONVERT(DATETIME,DATEDIFF(DAY,0,datetimefield)))

 

Count by field

In this example, by parsing a DateTime field

SELECT LEFT(CreateDate,11) AS CreateDate, COUNT(*) AS Total
FROM Document
GROUP BY LEFT(CreateDate,11)
ORDER BY CreateDate DESC

 

convert from varchar to int

CAST(CdNbr AS int)

 

Do I need to point out the utter inanity of naming a varchar field with “Nbr”, or data-typing a field named “Nbr” as varchar?
Because it is utterly inane.

 

 

swap two fields, in WHILE loop

-- swaps two fields
-- by copying each into local vars, and updating
DECLARE @DESC varchar(20)
DECLARE @SOURCE varchar(20)
DECLARE @ID int
SET @ID = 0
DECLARE @LIMIT int
SET @LIMIT = (SELECT MAX(DocTypeID) FROM CICA_EDOC_Type)
WHILE (@ID <= @LIMIT)
        BEGIN
                SET @ID = @ID+1
                SET @DESC = (SELECT EDocSource FROM EDOC_Type WHERE DocTypeID = @ID)
                SET @SOURCE = (SELECT EDocDescription FROM EDOC_Type WHERE DocTypeID = @ID)
                UPDATE EDOC_Type SET EDocDescription = @DESC, EDocSource = @SOURCE WHERE DocTypeID = @ID
        END

 

Not guaranteed to be the best way to do this, but it works...

 

 

Set proper execute permissions on stored procedures

 

When creating the sproc, make sure the schema is [dbo].[<sproc-name>] - otherwise it will be created with the schema DOMAIN/username.<sproc> and only be accessible to yourself.

 

GRANT exec ON [sp name] TO FormWare_Role
GO

GRANT VIEW definition ON [sp name] TO [<domain\group>]
GO

 

creating indexes

View an existing index via sp_helpindex <tablename>

 

 

 

Notes for Not Null

 

You cannot add a Not Null constraint to an existing table unless you first add the column as nullable, then backfill the values.  Then, once that is complete, can do an alter table/alter column in T-SQL.  This is the preferred method over using SQL Mgmt Studio since it will try to recreate the entire table if you later add a not null constraint.

 

 

auto-create insert statements for copying data

(there’s probably a better way to do this)
http://www.sqlservercentral.com/scripts/Miscellaneous/31447/

 

/*
Use this script to create insert statements for each row in the specified table.

Instructions:
1. Set the database you want to script from as normal.

2. change the set @TableName = '<YourTableName>' line to be the
table you want to script out.

3. Run the script and copy all the text from the results below
the line with all the dashes (----).

Notes:
   If you get the error message "Invalid object name '<YourTableName>'."
   then you either forgot to set the correct database or you spelled
   your table name wrong

Credits:
  Bob Wiechman - Fix for smalldatetime support
  Richard Lesh - correct support of uniqueidentifiers, automatic
          setting of Identity off/on, add Where clause support, more detail in
        debug mode.
*/


declare @TableName sysname
declare @WhereClause  varchar(1024)
declare @IdentityInsert int
declare @ColName sysname
declare @ColType tinyint
declare @ColStatus tinyint
declare @DebugMode bit
declare @ColList nvarchar(4000)
declare @ValList nvarchar(4000)
declare @SQL1 nvarchar(1000)
declare @SQL2 nchar(10)
declare @SQL3 nchar(1000)

SET @TableName = '<YourTableName>'      --  '<YourTableName>'
SET @WhereClause = ''       -- limit scope of inserts
SET @DebugMode = 0                -- set to 1 if you only want a script

SET @IdentityInsert = 0                -- set to 1 if you want to force IDENTITY_INSERT statements

SET @ColList = ''
SET @ValList = ''
SET @SQL1 = 'select replace(''insert into ' + @TableName + ' ('
SET @SQL2 = ') values ('
SET @SQL3 = ')'', ''''''null'''''', ''null'') from ' + @TableName

IF @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3

declare csrColumns cursor LOCAL fast_forward FOR
  SELECT c.name, c.xtype, c.STATUS
  FROM syscolumns c
    INNER JOIN sysobjects o
      ON o.id = c.id
  WHERE o.name = @TableName
    AND o.xtype IN ('U', 'S')
  ORDER BY ColID

open csrColumns
fetch next FROM csrColumns INTO @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
  SET @ColList = @ColList + ' ' + @ColName
  IF @ColType IN (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165)    -- numeric types (nulls not supported yet)
    SET @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+'''
  else IF @ColType IN (175, 239, 231, 231, 167)                            -- uid and string types
    SET @ValList = @ValList + ' ''''''+isnull(' + @ColName + ',''null'')+'''''''
  else IF @ColType IN (58, 61)                                             -- dates (nulls not supported yet)
    SET @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ')+'''''''
  else IF @ColType = 36                                                    -- uniqueidentfiers (nulls not supported yet)
    SET @ValList = @ValList + ' ''''{''+convert(varchar(200),' + @ColName + ')+''}'''''
  IF @DebugMode = 1             begin print '-- @ValList: ' + rtrim(@ValList) end
  IF (@ColStatus & 0x80) = 0x80 begin SET @IdentityInsert = 1 end          -- Check if column has Identity attribute
  fetch next FROM csrColumns INTO @ColName, @ColType, @ColStatus
end

close csrColumns
deallocate csrColumns

SET @ColList = REPLACE(ltrim(@ColList), ' ', ', ')
SET @ValList = REPLACE(ltrim(@ValList), ' ', ', ')

IF @IdentityInsert = 1
  print 'set identity_insert ' + @TableName + ' on'

IF @DebugMode = 1
  print @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause
else
  exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)

IF @IdentityInsert = 1
  print 'set identity_insert ' + @TableName + ' off'
 

 

handling occasional deadlocks

I was talking to Wilce the other day about how to handle queries that occasionally fail because of deadlocks, and I told him about this SQL I found on the web.
It looks like a stored procedure, but really it’s just regular T-SQL that can be executed just like any other query.
What you do is add your SQL to the query below, and it will try to run your query in a transaction.
If it fails due to a deadlock, it will try again, up to 3 times.
The nice thing about it is that you don’t have to add any transaction / rollback / retry code to your program - it’s all handled in SQL Server.

 

Caveat: I haven’t tried to use this in production yet.

 

 

DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
    BEGIN TRY

-- Add the original SQL that you want to execute here

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        PRINT 'Rollback Transaction'
        ROLLBACK TRANSACTION
        DECLARE @DoRetry bit; -- Whether to Retry transaction or not
        DECLARE @ErrorMessage varchar(500)
        SET @doRetry = 0;
        SET @ErrorMessage = ERROR_MESSAGE()
        IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
        BEGIN
            SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
        END
        IF @DoRetry = 1
        BEGIN
            SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
            IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
                BEGIN
                        RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if
                             -- still deadlock occurred after three retries
                END
            ELSE
                BEGIN
                        WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
                        GOTO RETRY      -- Go to Label RETRY
                END
        END
        ELSE
        BEGIN
            RAISERROR(@ErrorMessage, 18, 1)
        END
END CATCH

 

 

pseudo-boolean bit values

Case ... WHEN ... THEN ... ELSE ... END

 

SELECT
        (CASE CHARINDEX('Matched', ExpData.[DocStatus])
                WHEN 0 THEN 0
                ELSE 1
        END) AS Matched
FROM Exported_DATA AS ExpData
WHERE DocStatus IS NOT NULL

 

See also: Stackoverflow -- boolean NOT in T-SQL

 

Multi-row inserts

This is is for MSSQL2005+ only, AFAIK
INSERT INTO contacts (
   firstName
   , lastName
   , title
)
SELECT 'Clark', 'Kent', 'Reporter' UNION ALL
SELECT 'Kal', 'El', 'A completely different person';

 

 

And this is for MSSQL2008+ and above, only?

INSERT INTO contacts (
   firstName
   , lastName
   , title
)
VALUES ('Clark', 'Kent', 'Reporter'),
('Kal', 'El', 'A completely different person'),
('Brenda', 'Star', 'Girl Reporter'),
('Mary', 'Worth', 'Venerable Landlady and Agony Aunt');

 

auto-populated Create_Date field

I like to have one of these around as a small-time “audit” feature -- just when, exactly, was this data _really_ committed to the DB?

 

as part of your CREATE TABLE statement:

 

[CreateDate] [smalldatetime] NOT NULL CONSTRAINT [DF_table_name_CreateDate]  DEFAULT (getdate()),

 

(Temporary) Backup table

-- temporary backup table
SELECT *
INTO dbo.[newname]
FROM
dbo.[oldname]

 

 

Stupid SSMS tricks

Where SSMS := [MicroSoft] SQL Server Management Studio
MS: Using SQL Server Management Studio

print execution time in SSMS

 

Refresh Intellisense: Edit -> Intellisense -> Refresh Local Cache (CTRL+Shift+R)

 

 

Using PowerShell to extract headers and data

Extracting data with headers using PowerShell  - SQL Server Central article; may require (free) membership to view.

 

Code also at https://gist.github.com/MichaelPaulukonis/a75cf5d5b16627293014 - pmwiki gist-embed is failing. waaah!

 

 

SSIS

MSDN SQL Server resources: SQL Server Integration Services (SSIS)
SSIS 2008 tutorial
MSDN: Lesson 1 (SSIS Tutorial): Creating the Project and Basic Package

 

 

 

See Also

StackOverflow query references
SQL server datetime “workbench”
DotNet.LINQ
SqlServerManagementStudio

 

 

Category tags

SQL Database Query


 

Comments

Comment Page  

  yasmin   - 05.10.2016 - 02:36    
hi

Useful information with full of technical news. Nice one. I am really very happy with the part of this blog.
https://www.gangboard.com/big-data-training/data-science-training(approve sites)
https://www.gangboard.com/big-data-training/hadoop-admin-training(approve sites)

  melbahenry   - 26.09.2016 - 03:27    

This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles, I guess I am
not the only one having all the enjoyment here! keep up the good work. <a href=“http://www.gangboard.com/software-testing-training/selenium-training(approve sites)”>Selenium Online Training</a>

 

 

 

Add Comment

Heading:
 Your Message
 
 Enter value ← Have you entered the code number?
Author: