Learn SQL Server basics in this tutorial for beginners new to SQL Server, please find below few basic topics and features.

Last updated on December 26, 2008

  1. Working with Databases
    1. Create new Database:
      CREATE DATABASE NewTestDatabase
    2. Delete Database:
      DROP DATABASE database_name [, database_name...]
    3. Rename a database
      USE master
         EXEC sp_dboption myOldData, 'Single User', True
         EXEC sp_renamedb 'myOldData', 'myNewData'
         EXEC sp_dboption myNewData, 'Single User', False
    4. Working With Tables

      TCRUD - Create, Read, Update, and Delete

      1. Create a Table
        1. syntax:
            CREATE TABLE tablename (column_name column_type [(column_width[,column_precision])] ...)
          CREATE TABLE practice
           fname     VARCHAR(20)   NOT NULL,
           lname     VARCHAR(30)   NOT NULL,
           hire_date SMALLDATETIME NULL,
           ssn       CHAR(11)      NOT NULL
          -- 'NOT NULL' is the default.
          EXECUTE sp_help practice -- shows structure of table
        2. ALTER TABLE
      2. "SELECT INTO"

        Tables can also be created with the "SELECT INTO" construct SELECT select_list INTO new_table_name FROM table_list WHERE search_condition

        SELECT * INTO #tempcustomers2 FROM customers
        SELECT * FROM #tempcustomers2

        Temp tables go away when the server is restarted or at the end of the connection.

      3. Delete a table: DROP TABLE table_name
      4. Add a column: ALTER TABLE SDSurveys ADD KQLogic VARCHAR (1000) NULL
      5. Add a column with check for existence first

           WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'newcolumn')
           ALTER TABLE MyTableName ADD newcolumn varchar(32) NOT NULL DEFAULT ''
      6. How to delete, or drop, a column:

        Always a good idea to test if the column is there if you are running from a script

        IF EXISTS (SELECT * FROM dbo.syscolumns 
            WHERE id = object_id(N'[dbo].[Projects]')
            AND name = 'ProjectManager')
               ALTER TABLE Projects DROP COLUMN [ProjectManager]
      7. Show list of columns in a table: sp_columns table_name
    5. Working With Rows
      1. Inserting Rows into a Table
        1. Syntax:
          INSERT INTO table_name [(column_id1,column_id2...)]
             VALUES (value1,value2,...)
          INSERT INTO autos (1996,'ford') -- INTO is an optional keyword in SQLServer
          INSERT tablename VALUES ('a','b',DEFAULT,i) -- DEFAULT is a key word
          INSERT INTO title VALUES(1001,'The Odyssey','Homer',NULL)
        2. add multiple rows to a table
          INSERT INTO table (c17,c4,c8,c3) SELECT a,b,c,d FROM ...
        3. add multiple rows to a table with stored procedure
          INSERT INTO table
          EXECUTE sp_mystored_procedure
        4. INSERT rows from this database into a table in another database
          INSERT INTO OtherDatabaseName..users 
          SELECT * FROM users WHERE loginid ='mfincher'  
      2. UPDATEing Rows in a Table
        1. UPDATE changes the values in an existing row
          UPDATE tbl SET col = value|col|expr
          UPDATE table_name SET column_id = expr  WHERE condition
        2. Examples:
              update users set password = 'newpass' WHERE user = 'quest'
              update users set password = 'newpass' WHERE (UserID > 1) AND (UserID < 113)
              update users set password = 'newpass', change='Y' WHERE user = 'quest'
              UPDATE discounts SET discount = discount + .1 WHERE lowqty >= 100
              UPDATE employees SET LNAME = UPPER(LNAME)
        3. updating a value based on other tables
          UPDATE titles SET ytd_sales = 
          (SELECT SUM(qty)
           FROM sales
           WHERE sales.title_id = titles.title_id
           AND ord_date BETWEEN '01/01/94' AND '12/31/94')
        4. example of adding a record based on data in another table
          INSERT adult SELECT
            ju.member_no,  ad.street,  ad.city,  ad.state,  ad.zip,
            DATEADD(YY, 1, GETDATE())
            juvenile ju -- aliasing juvenile to ju
            adult ad    -- aliasing adult to ju
            ju.adult_member_no = ad.member_no
            (DATEADD(YY, 18, ju.birth_date) < GETDATE())
      3. Deleting rows from a Table DELETE FROM table_name [WHERE search_condition] example:
        DELETE FROM mytable WHERE userid < 50
        DELETE tablename -- deletes all rows in a table (see "DROP TABLE" to delete a table)
        TRUNCATE TABLE tablename -- deletes all rows, but doesn't log transaction
        DELETE titles WHERE title_id in (SELECT title_id FROM ...)
      4. How to format a number with two decimal places

        -- this will return a field 13 characters wide, 10 before the decimal point and two after
        select  CONVERT(DECIMAL(12,2),myField) from myTable
        -- if your number is larger than that you get this error:
        -- Msg 8115, Level 16, State 8, Line 1
        -- Arithmetic overflow error converting int to data type numeric.
        -- or you can use the STR function
        select str(myField, 12, 2) from myTable
      5. Example of CAST, Datepart, and STR

        In the example below, "dtime" is a column containing the datetime. "datepart(yy,dtime)" produces the year, "str(datepart(dy,dtime),3)" produces a right justified day-of-the-year.

        SELECT cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3)  as 'Year  Day', 
               COUNT(time) AS 'Key Question Pages',
               avg(time) AS 'avg time (msec)' 
        FROM sdrespondentpagetimes
        WHERE name='Key'
        group by cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3)
         order by cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3) asc

        The results:

        Year  Day Key Question Pages avg time (msec) 
        --------- ------------------ --------------- 
        2004  51  13                 768
        2004  54  10                 747
        2004  56  6                  1090
        2004  57  34                 702
        2004 351  1                  6000
        2004 362  14                 548
        2005   4  3                  629
        2005   5  20                 834
        2005   7  10                 469
        2005  10  1                  3593
        2005  11  1228               269
    6. SQL Server Utilities [Note: use osql only if your version of Sql Server is earlier than 2005. Use "sqlcmd" for new versions. You are better off using Invoke-Sqlcmd inside PowerShell.]
      1. osql - the command line batch interface to SQL Server before 2005
        1. To EXECUTE a single statement use the -Q option
               osql -Umoi -P"mypassword"
                  -Q"INSERT DATA_TABLE (column1, column2) values col1_value, col2_value"
        1. To have osql run a script file, (e.g., testSQL.SQL), use the -i option
          osql -Umyuserid -P"mypassword" -itestSQL.SQL
        2. Then to get things back out use the -o option:
          osql -Umoi -P
          -Q"SELECT column1, column2 FROM DATA_TABLE" -oOutput_file
        3. To specify a host: -H delphi
        4. To specify a server: -S delphi
        5. To specify a server running SQLExpress: -S "delphi\SqlExpress"
        6. to specify a database: -d db3
        7. to surpress the headers: -h-1
        8. to specify a separator: -s!
        9. to set the line width to 5000: -w5000

        A better way to handle the password is to set an environmental variable, "OSQLPASSWORD", so the "-U" option may be omitted. In "sqlcmd" you can use the "-E" option and use your current identity.

      2. To get a list of all databases on a server

      3. To see which service pack (if any is installed)
        SELECT SERVERPROPERTY('ProductLevel')
        'RTM' = shipping version.
        'SPn' = service pack version
        'Bn', = beta version.
      4. To impersonate a user:
             SETUSER 'mynewname'
             (SQL statements)

        use SETUSER to get back to the original user
      5. To add a login for a new user for a database:
             EXECUTE sp_adduser 'newloginid'
             EXECUTE sp_addlogin 'newloginid','password'
      6. To find the size of all databases
        EXEC sp_helpdb  
        -- or for one database
        EXEC sp_helpdb 'dbname'
      7. To get a list of all databases on a server

        SELECT name FROM master..sysdatabases
        -- or 
        EXEC sp_databases
      8. To grant a user permission to a database:
             USE mydatabase
             EXECUTE sp_adduser newloginid
      9. To grant a user permission to a database table:
        GRANT [EXECUTE|ALL] ON TableName TO myloginid
      10. Note: If a stored procedure is not the first item in a series of SQL statements, it must be prefaced with 'EXECUTE'.
      11. To unload a dll: dbcc xp_netq (FREE)
      12. To have SQLServer run a simple diagnostic on a db named mydb:
        dbcc checkdb(mydb) 

        also try dbcc checkalloc(db4)

      13. To get the name of the current database
        SELECT db_name()
      14. to show the names of all databases
        USE master
        SELECT name FROM sysdatabases
      15. to get the date
        SELECT getDate()
    7. SQL Server Results
      1. Three ways to change column headings
        SELECT First = au_fname,
               au_lname 'Last Name',
               au_id AS 'Identification Number'
        FROM authors
        First      Last Name                  Identification Number 
        Johnson    White                      172-32-1176           
        Marjorie   Green                      213-46-8915           
      2. Retrieving Results
        1. General to get unique values:
          SELECT DISTINCT column_name FROM mytable -- note: this also does an implicit sort
        2. to get frequency of column values:
          SELECT column_name, COUNT(column_name) as mycount FROM table_name
                GROUP BY column_name ORDER BY mycount [ASC | DESC]
        3. to join two tables the SQLServer way:
          SELECT tablea.A1, tableb.B1 FROM tablea, tableb
              WHERE tablea.mykey = tableb.mykey
        4. to get info from two tables
          UPDATE table1 SET mycolumn = '2' WHERE userid IN
                   ( SELECT userid FROM table2 WHERE table2.dissat <> 'somevalue')
      3. Aliasing tables
        SELECT a.title_id, a.au_id, b.au_id
        FROM titleauthor a
        INNER JOIN titleauthor b
        ON a.title_id = b.title_id
        WHERE a.au_id < b.au_id
      4. how to preface info in a column with a string, 'x-',
        UPDATE mytable SET mycolumn = 'x-'+mycolumn
      5. wildcards used in the LIKE clause:
        Wildcard Meaning
        % Any string of zero or more characters
        _ Any single character
        [ ] Any single character within the specified range ([a-f]) or set ([abcdef])
        [^] Any single character not within the specified range ([^a-f]) or set ([^abcdef])
        LIKE 'Mc%' searches for all names that begin with the letters "Mc" (McBadden).
        SELECT column_name FROM table WHERE mystring LIKE '%[XYZ]' 
        To find all fields which contain a carriage return:
        SELECT userid, Zip FROM mytable WHERE Zip like '%'+char(10)+'%'
        To find fields containing an '_', which is tricky since '_' is usually the "any character" flag.
        SELECT labelInfo FROM mytable WHERE labelInfo like '%[_]%'
      6. Advanced Topics in Retrieving Results
        1. Limit the number of rows returned

          (note this can be very dangerous since it affects all subsequent commands)

          SET rowcount 4 
          SELECT TableName,ColumnName,Language Title,LU_Table,Description,Logic,Type FROM DataGroup 
          SET rowcount 0 -- turns off rowcounting
        2. UNION This concatenates result set together
          USE sqldemo
          SELECT * FROM authors   --23 rows
          SELECT * FROM newauthors  --8 rows
          ORDER BY authors.au_lname

          UNION will surpress duplicates, use UNION ALL to show all rows

        3. GROUP BY and HAVING
          **  This query will search through the member_no column looking for
          **  member numbers that have duplicate values and also show the number
          **  of duplicates found for each member number.
            SELECT member_no, Number_of_Dups = count(*)
            FROM member
            GROUP BY member_no
            HAVING count(*) > 1
    8. Stored Procedures
      1. General syntax of stored procedure
        CREATE PROCEDURE pname
           @var vartype[=defaultvalue][,@var2 vartype]
      2. Declaring Variables
        DECLARE @varname type[,@varname type]* -- to define a variable
        DECLARE @x int
        SELECT @x = 5
      3. Simple Example
        CREATE PROCEDURE emplist
          SELECT empname, title, salary
          FROM emp
          ORDER BY title
      4. Simple Example with one argument
        CREATE PROCEDURE AuthorState
        @statevar CHAR(2) = '__' -- if no arg passed, '__' is the default
        AS SELECT state, au_fname, au_lname FROM authors WHERE state LIKE
        @statevar ORDER BY au_lname GO To test it: EXECUTE AuthorState
      5. Fun things to know and tell about Stored Procedures
        1. Stored Procedures can have up to 255 parameters
        2. EXECUTE sp_helptext mystoredproc -- shows the source code
        3. EXECUTE sp_depends mystoredproc -- see what tables are associated with the sp
        4. SELECT name FROM sysobjects WHERE type = 'P'-- to see all the stored procedures
        5. sp_makestartup -- makes an existing sp a startup procedure
        6. sp_ummakestartup -- removes an existing sp a startup procedure
        7. in transact sql to get the results of a select into a variable:
          SELECT @LanguageStringID = MAX(LanguageStringID) FROM
      6. How to drop a stored procedure
        IF EXISTS ( SELECT name FROM sysobjects WHERE type = 'P' AND name = 'addadult' )
           DROP PROCEDURE addadult

        or you can do this:

        IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
           DROP PROCEDURE [dbo].[YourStoredProcName]
      7. example of complete sp:
        CREATE PROCEDURE addadult
            @lastname         shortstring,
            @firstname        shortstring,
            @middleinitial    letter = NULL,
            @street           shortstring = NULL,
            @city             shortstring = NULL,
            @state            statecode = NULL,
            @zip              zipcode = NULL,
            @phone_no         phonenumber = NULL
            DECLARE  @member_no  member_no
            IF  @middleinitial = NULL OR
                @street        = NULL OR
                @city          = NULL OR
                @state         = NULL OR
                @zip           = NULL OR
                @phone_no      = NULL 
              PRINT "   "
              PRINT "   Please reenter this Adult Member."
              PRINT "   You must provide Last name, First name, Middle initial, "
              PRINT "    Street, City, State, Zip, and Phone number."
              PRINT "   "
            SELECT @member_no = (SELECT max(member_no) FROM member HOLDLOCK) + 1
                INSERT member 
                    ( member_no,  lastname,  firstname,  middleinitial, photograph)
                    (@member_no, @lastname, @firstname, @middleinitial, NULL )
                IF @@error <> 0
                   ROLLBACK TRAN
                INSERT adult
                    ( member_no,  street,  city,  state,  zip,  phone_no, expr_date)
                    (@member_no, @street, @city, @state, @zip, @phone_no, dateadd(year,1,getdate()) )
                IF @@error != 0
                   ROLLBACK TRAN
                 PRINT "      "
                 PRINT "        *** Adult Member added ***   "
                 PRINT "      "
      8. Another Example
        CREATE PROCEDURE overdue_books 
        SELECT member.lastname,copy_no,due_date FROM loan 
        JOIN member ON loan.member_no = member.member_no
            WHERE DATEDIFF(DAY,GETDATE(),due_date)>1
      9. OUTPUT parameter

        Example using the OUTPUT parameter

        ** This script file creates a procedure, LOANCOUNT, 
        ** that returns a status of 0 if a member has any 
        ** loans. If a member has no loans, it returns a
        ** status of -1. 
        USE library 
        GO CREATE PROC loancount @member_no member_no, @loancount int OUTPUT AS IF EXISTS (SELECT
        * FROM loan WHERE member_no = @member_no) 
        @loancount=COUNT(*) FROM loan WHERE member_no = @member_no RETURN
        (0) END ELSE RETURN (-1) 
        --NOTE: if any syntax errors in a batch, nothing in batch runs 
        -- all the statements are rolled together for syntax checking 
        -- if any statements have problems the "GO" is not executed.
    9. How to write a FUNCTION

      Functions are more flexible to use than stored procedures. Here's a simple function that returns an integer.

      IF OBJECT_ID(N'NumberProcessedInTimeSpan', N'FN') IS NOT NULL 
         DROP function [dbo].[NumberProcessedInTimeSpan]
      CREATE function NumberProcessedInTimeSpan (@startDate DateTime, @endDate DateTime ) 
      returns int 
      RETURN (select COUNT(*)
         from JobMetrics WITH(NOLOCK)
            ((StartTime < @endDate) and (StartTime > DATEADD(HOUR,-3,@startDate)))
      -- test case
      DECLARE @q int
      exec @q = NumberProcessedInTimeSpan '2014-11-11 22:01:00',  '2014-11-11 22:11:00'
      print @q
    10. Control of Flow
      1. Control of Flow
        1. IF
            IF (SELECT COUNT(member_no) 
                FROM loan
                WHERE member_no = @param) <= 4
            RETURN 1
            RETURN 2
        2. WHILE
          1. Syntax
            WHILE condition BEGIN ... END BREAK/CONTINUE
          2. Example of simple WHILE statement
            set nocount on
            declare @i int SELECT @i = 0
            while (@i < 10)
               SELECT 'i = ' + convert(char(5),@i)
               SELECT @i = @i + 1
            set nocount off

            Code to insert 3000 rows into the database

            DECLARE @i INT
            SET @i=10000
            WHILE(@i <= 30000)
               INSERT INTO mytable (date, thread, level, logger, server, userid, message) 
               (GETDATE(),'0000','INFO','XYZ1','LogFile',@i,'my message')
               SET @i=@i+1
        3. Delete rows from a table in small batches like slicing a salami.

          Using the WHILE statement this stored procedure deletes records older than a specific number of days. Deleting all the rows can cause the database to freeze or the query can timeout.

          -- trims table to only keep specific number of previous days in table which must have a datetime field named 'dtime'
          -- example:  EXEC sd_trimTable 'practice', 50
          --           this would remove all rows older than 50 days in slices of 1000
          IF OBJECTPROPERTY(object_id('dbo.sd_trimTable'), N'IsProcedure') = 1
             DROP PROCEDURE dbo.sd_trimTable
          CREATE PROCEDURE sd_trimTable @tableName char(128), @trimDays int
             DECLARE @trimDate datetime
             DECLARE @cmd char(240)
             DECLARE @counter int
             SET @trimDate = DATEADD(day,-@trimDays,GETDATE())
             SET @counter = 1
             SET @cmd = 'DELETE TOP (1000) FROM '+ (@tableName)+'  WHERE dtime <  ''' + CONVERT(VARCHAR(10), @trimDate, 111) + ''''
             SELECT @cmd
             WHILE 1 = 1
                  EXEC (@cmd)
                  IF @@ROWCOUNT = 0
                  WAITFOR DELAY '00:00:00.2' --give other processes time to access table
        4. Example to show space used by each table
          DECLARE @next VARCHAR(30)
          SELECT @next = ' '  --Makes it "not null"
          WHILE @next IS NOT NULL
            SELECT @next = MIN(name)
            FROM sysobjects
            WHERE type = 'U'  --User Tables
            AND name > @next
            ORDER BY name
            EXECUTE sp_spaceused @next
        5. RETURN used for 1 non-null integer
          CREATE PROCEDURE checkmem
            @param INT
            IF (SELECT COUNT(member_no) 
                FROM loan
                WHERE member_no = @param) <= 4
            RETURN 1
            RETURN 2
          To use this construct:
          DECLARE @ret INT
          EXECUTE @ret = checkmem 100
      2. Misc commands
        1. Example to create many databases
          set nocount on
          declare @i int SELECT @i = 2
          declare @si char(1)
          while (@i < 5)
             SELECT @si = convert(char(1),@i)
             SELECT 'i = ' + convert(char(5),@i)
             exec('CREATE DATABASE db'+@si+' ON sndat = 1 LOG ON snlog = 1')
             SELECT @i = @i + 1
          set nocount off
        2. How to prevent division by zero errors

          Use the NULLIF function. If @total is zero, NULL is returned for the division operation.

            SELECT @mydate as 'Date Time',@total AS 'total', @completes AS 'Number of completes', 

          You can also set Sql Server to just report divide by zero problems as 0

        3. To see which programs are connecting to your sql server instance.

          SELECT program_name, count(*)
          WHERE ecid=0
          GROUP BY program_name
          ORDER BY count(*) desc
        4. To count how many processes are blocked

          SELECT count(*) from master..sysprocesses 
          WHERE blocked <> 0 
        5. To find processes blocked for more than 6 minutes (360000 millisecs)

          SELECT * from master..sysprocesses WHERE blocked <> 0 and waittime > 360000
        6. To get information on which jobs are blocked and by whom

              p1.SPID AS blockedSPID, p2.SPID AS blockingSPID
          ,p1.kpid, p1.blocked, db_name(p1.dbid) AS 'db_name', p1.status,
          p1.waittime, p1.lastwaittype,p1.cpu, p1.physical_io, p1.memusage,
          p1.login_time, p1.last_batch
          FROM master..sysprocesses p1
              master..sysprocesses p2 ON p1.blocked = p2.spid
        7. How to trim down the size of log files in MSSQL 2008

          This is dangerous for production machines, but for development boxes where you really don't care about recovery this should reduce the log size.

          BACKUP log [MyDatabaseName] WITH TRUNCATE_ONLY
          DBCC SHRINKDATABASE ([MyDatabaseName], 10, TRUNCATEONLY)
        8. How to see all collations in a database
        9. How to write the SQL to change all collation sequences

          from Paul Deen, http://members.chello.nl/p.deen7/knowledgebase/sql_change_collation.htm

          SELECT 'Alter Table [' + table_catalog + '].[' + table_schema + '].[' + table_name + '] alter column [' +  column_name + '] ' + case data_type when 'char' then 'char' when 'varchar' then 'varchar' when 'text' then 'text' end + case when data_type <>'text' then '(' + convert(varchar,character_maximum_length) + ')' else '' end + ' collate SQL_Latin1_General_CP1_CI_AS ' + case when is_nullable='YES' then 'NULL ' else 'NOT NULL' end
          WHERE collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
          AND data_type in ('char', 'varchar', 'text')
          AND table_name in (select table_name from INFORMATION_SCHEMA.tables where table_type = 'BASE TABLE')
        10. How To Obtain The Size Of All Tables In A SQL Server Database

          From http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

          SET NOCOUNT ON 
          DBCC UPDATEUSAGE(0) 
          -- DB size.
          EXEC sp_spaceused
          -- Table row counts and sizes.
          CREATE TABLE #t 
              [name] NVARCHAR(128),
              [rows] CHAR(11),
              reserved VARCHAR(18), 
              data VARCHAR(18), 
              index_size VARCHAR(18),
              unused VARCHAR(18)
          INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
          SELECT *
          FROM   #t
          -- # of rows.
          SELECT SUM(CAST([rows] AS int)) AS [rows]
          FROM   #t
          DROP TABLE #t 
        11. Print the current time immediately in seconds

          we use RAISERROR since it's supposably not buffered

          DECLARE @str char(240)
          SELECT @str = convert(varchar(19), getdate(), 121)
          RAISERROR('Trimming 100,000 rows at %s', 0, 1, @str) WITH NOWAIT
        12. sp_tables
          sp_tables -- to show a list of tables in a database:
          -- or
          SELECT name FROM sysobjects WHERE type = 'u' -- to see the user tables only
      3. Change the size of varchar column

        You may need to add the 'NOT NULL' option if your column is an indexed column

        ALTER TABLE MyTableName ALTER COLUMN MyColumnName varchar(32)
      4. Show all constraints of type DEFAULT_CONSTRAINT

        SELECT * FROM sys.objects
        WHERE type_desc LIKE '%CONSTRAINT'
        AND type_desc = 'DEFAULT_CONSTRAINT'
      5. Read more than 256 characters from an ntext field

        OK, there's the right way to do this with pointer and READTEXT, but you can cheat and use the SUBSTRING command to read from an ntext field

        SELECT TOP 1000
              ,[Duration]/1000 as durationMs
          FROM [zzzzz].[dbo].[SDWhistory_1103]
          where cpu is not NULL and textdata is not null and cpu > 0
          order by duration desc
      6. Modifying column properties with constraints

        Sometimes you can't modify a column if it has a constraint. First you must drop the constraint, then modify the column, then re-add the constraint.

        IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SampleSources]') AND name = N'PK_SampleSources')
           ALTER TABLE [dbo].[SampleSources] DROP CONSTRAINT [PK_SampleSources]
        alter table SampleSources Alter Column name varchar(65)  NOT NULL
        /****** Object:  Index [PK_SampleSources]    Script Date: 08/06/2010 09:11:22 ******/
        ALTER TABLE [dbo].[SampleSources] ADD  CONSTRAINT [PK_SampleSources] PRIMARY KEY CLUSTERED 
        	[name] ASC
      7. Working with parameterized SQL

        You have to declare and set the variable

        declare @id int 
        select @id = 4140
        SELECT userid FROM mytable WHERE userid > @id ORDER BY userid
      8. loop thru all the tables in a database and UPDATE their Statistics
        set nocount on
        declare @next varchar(30)
        SELECT @next = ' '
        while @next IS NOT NULL
         SELECT @next = MIN(name) FROM sysobjects WHERE type = 'u' and name > @next
         SELECT 'next is ' + @next
          IF @next IS NOT null
                   EXECUTE ('UPDATE STATISTICS ' + @next)
        set nocount on
      9. Loop through all the tables in a database and print the number of rows

        set nocount on
        declare @tablename varchar(90)
        declare @mycount varchar(90)
        SELECT @mycount = ' '
        SELECT @tablename = ' '
        while @tablename IS NOT NULL
         SELECT @tablename = MIN(name) FROM sysobjects WHERE type = 'u' and name > @tablename
         IF @tablename IS NOT null
                   SELECT @mycount = 'select '''+@tablename+''', count(*) from ' + @tablename
                   execute (@mycount)
      10. loop thru all the databases and print the name. (Useful for performing operations on each database).
        set nocount on
        use master
        declare @next varchar(30)
        SELECT @next = ' '
        while @next IS NOT NULL
         SELECT @next = MIN(name) FROM sysdatabases WHERE dbid > 5 AND name > @next
         --SELECT 'next is ' + @next
          IF @next IS NOT null
                   SELECT 'Looking at database',@next
        set nocount on
      11. RAISERROR

        This writes a message to the console and event log

        RAISERROR('** this is my message',16,1) WITH LOG
        RAISERROR('** this is my message',servity,state) WITH LOG
        --error serverity 1-16 ok, 17-18 serious, 19-25 fatal
      12. Adding Space to a Database: Add 1 MEG:
        ALTER DATABASE library ON library_dev1 = 1 -- MEG to increase by

        To increase size of log file:

        ALTER DATABASE library ON logdevname = size in 2k
      13. To make the log file less verbose

      14. PRINT
              PRINT 'this is a print'
              PRINT 'SELECT sorta does the same thing, but not quite, sometimes you need a PRINT'

        The PRINT statement is buffered. To get your output immediately (unfortunately there's no FLUSH command) you can use RAISERROR instead.

                set @msg = convert(char(25),GETDATE()) + ': my cool message'
                RAISERROR ( @msg,0,1) WITH NOWAIT
      15. CASE statement
        SELECT Category = 
           CASE type
                   WHEN 'popular_comp' THEN 'Popular Computing'
                   WHEN 'mod_cook'     THEN 'Modern Cooking'
                   WHEN 'business'     THEN 'Business'
                   WHEN 'psychology'   THEN 'Psychology'
             WHEN 'trad_cook'    THEN 'Traditional Cooking'
                   ELSE 'Not Yet Categorized'
               CONVERT(varchar(30), title) AS 'Shortened Title',
        FROM titles
        WHERE price IS NOT NULL
        ORDER BY type
        COMPUTE AVG(price) BY type
        SELECT m.member_no, 
               'Member Type' = 
                    WHEN m.member_no IN
                      (SELECT member_no 
                 FROM juvenile j
                       WHERE j.member_no = m.member_no)
              THEN 'Juvenile'
                    ELSE 'Adult'
        FROM member m, adult a, juvenile j
        WHERE j.adult_member_no = a.member_no
        AND (m.member_no = a.member_no OR m.member_no = j.member_no)
        AND expr_date < GETDATE()
        ORDER BY m.member_no

  2. Please find below few useful links which are explained in detail about "SQL SERVER".
    Sql Server Central
    Free educational web site covering SQL concepts.

    Sql Tutorial
    Free educational web site covering SQL concepts and clauses

    Sams Teach Yourself SQL in 24 Hours
    Sams Teach Yourself SQL in 24 Hours, Third Edition presents the key features of SQL (Structured Query Language) in an easy to understand format with updated code examples, notes, diagrams, exercises, and quizzes.
    SQL Tutorial - Learn SQL
    This SQL tutorial will teach you how to use commonly used SQL commands so you will be able to apply most of the knowledge gathered from this SQL tutorial to many different databases. 
    Introduction to Structured Query Language
    This page is a tutorial of the Structured Query Language (also known as SQL). 

    A Gentle Introduction to SQL
    Interactive SQL tutorial. SQL Server, Oracle, MySQL, PostgreSQL and Access. SELECT statements including joins and functions. 
    Sql Course
    Interactive/On-line SQL Tutorial with SQL Interpreter & live practice database
    In our SQL tutorial you will learn how to use SQL to access, define, and manipulate the data in a database system, like Oracle, DB2, Sybase, Informix, Microsoft SQL Server, Access. 

    SQL Tutorial
    This SQL Tutorial site is aimed to teach beginners the building blocks of SQL. 
    SQL Tutorial
    A short sql tutorial that covers the basic commands. 
    This unique SQL Tutorial is the sequel to the highly successful SQLCourse.com site and will provide you with more advanced easy-to-follow SQL Instruction and the ability to practice what you learn on-line with immediate feedback. 
    SQL Tutorial
    This document is a general tutorial on the database sublanguage - SQL. 
    SQL Syntax
    This document is written for users who wish to run their own queries of the database..