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
         GO 
         EXEC sp_dboption myOldData, 'Single User', True
         EXEC sp_renamedb 'myOldData', 'myNewData'
         EXEC sp_dboption myNewData, 'Single User', False
         GO 
      
    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])] ...)
          
          example:
          CREATE TABLE practice
          (
           fname     VARCHAR(20)   NOT NULL,
           lname     VARCHAR(30)   NOT NULL,
           hire_date SMALLDATETIME NULL,
           ssn       CHAR(11)      NOT NULL
          )
          GO
          -- 'NOT NULL' is the default.
          
          EXECUTE sp_help practice -- shows structure of table
          
        2. ALTER TABLE
          ALTER TABLE XYZ ADD mi CHAR(1) NULL
          GO
          
          
      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

        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'newcolumn')
        BEGIN
           ALTER TABLE MyTableName ADD newcolumn varchar(32) NOT NULL DEFAULT ''
        END
        
      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,...)
          examples:
          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
          (c17,c4,c8,c3)
          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,
            ad.phone_no,
            DATEADD(YY, 1, GETDATE())
          FROM
            juvenile ju -- aliasing juvenile to ju
          JOIN
            adult ad    -- aliasing adult to ju
          ON
            ju.adult_member_no = ad.member_no
          WHERE
            (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

        SELECT DISTINCT name FROM SYSDATABASES
        
      3. To see which service pack (if any is installed)
        SELECT SERVERPROPERTY('ProductLevel')
        Returns:
        'RTM' = shipping version.
        'SPn' = service pack version
        'Bn', = beta version.
        
      4. To impersonate a user:
             SETUSER 'mynewname'
             GO
             (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
        Produces:       
        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
          UNION
          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]
        AS
           ...
        GO
        
      2. Declaring Variables
        DECLARE @varname type[,@varname type]* -- to define a variable
        DECLARE @x int
        SELECT @x = 5
        
      3. Simple Example
        CREATE PROCEDURE emplist
        AS
          SELECT empname, title, salary
          FROM emp
          ORDER BY title
        GO
        
        
      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
        'UT'
        
      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
          LanguageString
      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]
        GO
        
        
      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
        AS 
            DECLARE  @member_no  member_no
        
            IF  @middleinitial = NULL OR
                @street        = NULL OR
                @city          = NULL OR
                @state         = NULL OR
                @zip           = NULL OR
                @phone_no      = NULL 
            BEGIN
              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 "   "
              RETURN
            END
        
            BEGIN TRANSACTION
        
            SELECT @member_no = (SELECT max(member_no) FROM member HOLDLOCK) + 1
        
                INSERT member 
                    ( member_no,  lastname,  firstname,  middleinitial, photograph)
                VALUES    
                    (@member_no, @lastname, @firstname, @middleinitial, NULL )
                IF @@error <> 0
                 BEGIN
                   ROLLBACK TRAN
                   RETURN
                 END
                INSERT adult
                    ( member_no,  street,  city,  state,  zip,  phone_no, expr_date)
                VALUES
                    (@member_no, @street, @city, @state, @zip, @phone_no, dateadd(year,1,getdate()) )
                IF @@error != 0
                 BEGIN
                   ROLLBACK TRAN
                   RETURN
                 END
        
                 PRINT "      "
                 PRINT "        *** Adult Member added ***   "
                 PRINT "      "
        
        
            COMMIT TRANSACTION
        GO
        
      8. Another Example
        CREATE PROCEDURE overdue_books 
        AS
        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
        GO
        
        
      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) 
        
        BEGIN SELECT
        @loancount=COUNT(*) FROM loan WHERE member_no = @member_no RETURN
        (0) END ELSE RETURN (-1) 
        GO 
        --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]
      GO
      CREATE function NumberProcessedInTimeSpan (@startDate DateTime, @endDate DateTime ) 
      returns int 
      AS 
      BEGIN
      RETURN (select COUNT(*)
         from JobMetrics WITH(NOLOCK)
            where
            ((StartTime < @endDate) and (StartTime > DATEADD(HOUR,-3,@startDate)))
             )
      END
      GO
      -- 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(condition)
            BEGIN
            ...
            END
          ELSE
            BEGIN
            ...
            END
           
          
          Example:
          
            IF (SELECT COUNT(member_no) 
                FROM loan
                WHERE member_no = @param) <= 4
            RETURN 1
            ELSE
            RETURN 2
          GO
          
          
        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)
               BEGIN
               SELECT 'i = ' + convert(char(5),@i)
               SELECT @i = @i + 1
               END 
            set nocount off
            

            Code to insert 3000 rows into the database

            DECLARE @i INT
            SET @i=10000
            WHILE(@i <= 30000)
               BEGIN
               INSERT INTO mytable (date, thread, level, logger, server, userid, message) 
               VALUES
               (GETDATE(),'0000','INFO','XYZ1','LogFile',@i,'my message')
               SET @i=@i+1
            END
            
        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
          GO
          CREATE PROCEDURE sd_trimTable @tableName char(128), @trimDays int
          AS
             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
                BEGIN
                  EXEC (@cmd)
                  IF @@ROWCOUNT = 0
                    BREAK
                  WAITFOR DELAY '00:00:00.2' --give other processes time to access table
              END
          GO
          
        4. Example to show space used by each table
          DECLARE @next VARCHAR(30)
          SELECT @next = ' '  --Makes it "not null"
          WHILE @next IS NOT NULL
          BEGIN
            SELECT @next = MIN(name)
            FROM sysobjects
            WHERE type = 'U'  --User Tables
            AND name > @next
            ORDER BY name
            EXECUTE sp_spaceused @next
          END
          
        5. RETURN used for 1 non-null integer
          CREATE PROCEDURE checkmem
            @param INT
          AS
            IF (SELECT COUNT(member_no) 
                FROM loan
                WHERE member_no = @param) <= 4
            RETURN 1
            ELSE
            RETURN 2
          GO
          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)
             BEGIN
             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
             END 
          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', 
                  @completes*100/NULLIF(@total,0)
          

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

          SET ARITHABORT OFF 
          SET ANSI_WARNINGS OFF
          
        3. To see which programs are connecting to your sql server instance.

          SELECT program_name, count(*)
          FROM
          Master..sysprocesses
          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

          SELECT 
              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
              JOIN
              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.

          ALTER DATABASE [MyDatabaseName]  SET RECOVERY SIMPLE
          go
          BACKUP log [MyDatabaseName] WITH TRUNCATE_ONLY
          go
          DBCC SHRINKDATABASE ([MyDatabaseName], 10, TRUNCATEONLY)
          go
          
          
        8. How to see all collations in a database
          SELECT TABLE_NAME, COLUMN_NAME,DATA_TYPE,COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS
          WHERE COLLATION_NAME IS NOT null
          
        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
          FROM INFORMATION_SCHEMA.COLUMNS
          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
               SUBSTRING([TextData],1,255)
              ,SUBSTRING([TextData],256,400)
              ,[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
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
        
      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
         BEGIN
         SELECT @next = MIN(name) FROM sysobjects WHERE type = 'u' and name > @next
         SELECT 'next is ' + @next
          IF @next IS NOT null
             BEGIN
                   EXECUTE ('UPDATE STATISTICS ' + @next)
             END
         END
        
        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
         BEGIN
         SELECT @tablename = MIN(name) FROM sysobjects WHERE type = 'u' and name > @tablename
         IF @tablename IS NOT null
             BEGIN
                   SELECT @mycount = 'select '''+@tablename+''', count(*) from ' + @tablename
                   execute (@mycount)
              END
         END
        
        
      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
         BEGIN
         SELECT @next = MIN(name) FROM sysdatabases WHERE dbid > 5 AND name > @next
         --SELECT 'next is ' + @next
          IF @next IS NOT null
             BEGIN
                   SELECT 'Looking at database',@next
                   END
         END
        
        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

        ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
        
      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
        -- SIMPLE CASE EXPRESSION
        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'
                 END,
               CONVERT(varchar(30), title) AS 'Shortened Title',
               Price
        FROM titles
        WHERE price IS NOT NULL
        ORDER BY type
        COMPUTE AVG(price) BY type
        
      16. SEARCHED CASE EXPRESSION
        SELECT m.member_no, 
               m.lastname,
               'Member Type' = 
                  CASE 
                    WHEN m.member_no IN
                      (SELECT member_no 
                 FROM juvenile j
                       WHERE j.member_no = m.member_no)
              THEN 'Juvenile'
                    ELSE 'Adult'
                  END,
               expr_date
        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
    W3Schools
    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. 
     
    SQLCourse2.com
    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..