Notebook SQL Server.

Intro.

This is not a tutorial nor a quick reference.
This document is just my personal memo block for SQL.

Content.

Add column to table
Insert-statement
Select into a new table
Select Exists / Not Exists
Update-statement
Creating a testset on a testserver.
Setting Identity Column to zero.
Changing table owner.
Execute a character string within TSQL.
Cast int to string.
Convert datetime to string.
Convert string to DateTime.
Carriage Return + Linefeed in a string.
Retrieve Database size.
Case in a select statement.
Comparing two arguments when one or both are NULL.
Check whether or not table exists.
User-defined function samples.
    User defined function returning a scalar value.
    User defined function returning a table.
Using extended properties.
Adding leading zeros to a string.
The transaction log for database '<databasename>' is full.
The scope of a variable & GO statement.
META Information in sql server.
Executing sql script from commandline.
Grant.
Application role.
Database role.
Application role vs Database role.
Cursor.
Dropping a Database Role

SQL Server 2000 Script generation and foreign key relations
Setting the default database in SQL Server Management Studio 2005
 

Add column to table

IFNOT EXISTS(select * from sys.columns where Name = N'<column_name>' and Object_ID = Object_ID(N'<table_name>'))
BEGIN
    ALTER TABLE <table_name> ADD <column_name> datatype
END

Insert-statementttttt

insert into <tablename> ([<fieldname>,...])
values ('<value>',...)

INSERT INTO <tablename> (<fieldname>,...)
SELECT <fieldname>,...
FROM <?>;

Select into a new table.

SELECT <fieldname>,...
INTO <tablename1>
FROM <tablename2>

Select Exists / Not Exists

SELECT * FROM suppliers
WHERE EXISTS (select * from orders where suppliers.supplier_id = orders.supplier_id);

SELECT * FROM suppliers
WHERE NOT EXISTS (select * from orders Where suppliers.supplier_id = orders.supplier_id);

Update-statement

Updating a field from another table with the same key:

update Table1
set Field1 = T2.Field1
from Table1 T1, Table2 T2
where T1.Code = T2.Code
 

Creating a testset on a testserver.

Suppose you have some data on a production server.
You want to take a part of that data, and create a testset on a test server (which is NOT physically connected to the production server).
How would you do that?

• Use Export function of SQL Server. Export to Access or Excel, and Import on test server.
      Pro: easy
      Contra: manual action required each time
• Use BCP.
  See SQL Server Help and Commonly Asked BCP Utility Questions in SQL.
• Create an sql file (".sql") with insert statements.
      Pro: scriptable
      Contra: Requires some initial manual work.
  
      The little trick below uses a select statement on the production server to create an insert statement that can be used on the test server.
      You can run this select statement in the Query Analyzer, and save the results-grid as a tab-delimited ascii file (use the extension ".sql").
      Then move that ascii-file to your test server and call it using the Query Analyzer or OSQL.EXE.
  
      select 'INSERT INTO [dbo].[tbl_DSB_Dataset](
      [ID],
      [RunID],
      [Naam],
      [Deelstelsel],
      [IteratieNummer],
      [VerwerkingsStatus]',
      ')',
      ' VALUES(',
      [ID],', ',
      [RunID],', ',
      '''',[Naam],'''',', ',
      [Deelstelsel],', ',
      [IteratieNummer],', ',
      '''',[VerwerkingsStatus],'''',
      ')'
      from tbl_DSB_Dataset
      where RunID = 3
   

Setting Identity Column to zero.

After a table with an indentity column has been flushed, it remembers the highest identity that was put in the table.
To reset the identity to 0, use:

dbcc checkident (<tablename>,reseed,0)

Changing table owner.

EXEC sp_changeobjectowner '<objectname>', '<username>'

<objectname> can be an existing table, view, user-defined function, or stored procedure.

Execute a character string within TSQL.

--Declare and set some variables
DECLARE @InputTabel varchar(40)
DECLARE @SQL nvarchar(4000), @Count int
SET @InputTabel = 'Test'

--Fill the string
SET @SQL = 'SELECT Kolom,sum(Waarde) as Waarde from ' + @InputTabel + ' group by Kolom'

--Execute the string
EXEC @Count = sp_executesql @SQL

Cast int to string.

CAST(R.[Jaar] as varchar(4))

Convert datetime to string.

Convert(varchar(20), <datetime field>, 105) + ' '
+ Convert(varchar(20), <datetime field>, 108)

Carriage Return + Linefeed in a string.

DECLARE @CRLF CHAR(2)
SET @CRLF=Char(13)+Char(10)

DECLARE @msg varchar(1000)
SET @msg = 'regel1' + CRLF + 'regel2'

Database-grootte opvragen.

sp_helpdb <database-naam>

Case in a select statement.

From MSDN Online:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.
For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).

select
    Field1,
    Case NumberField
        When 1 Then 'een'
        When 2 Then 'twee'
    Else 'overig'
    End as [Hoeveelheid]
from MyTable.

using 'is null' in a case statement

case Field1
when is null then Field2
else Field1 + ' ' + Field2
end as Label

gives an error message

case Field1
when null then Field2
else Field1 + ' ' + Field2
end as Label

returns null when Field1 is null

case
when Field1 is null then Field2
else Field1 + ' ' + Field2
end as Label

works ok.

Comparing two arguments when one or both are NULL.

Comparison operators return UNKNOWN if either or both arguments are NULL.
i.e.
declare @test1 int
declare @test2 int

set @test1 = null
set @test2 = null

if (@test1 = @test2)
returns false.

use:

if ((@test1 = @test2) or (@test1 is null and @test2 is null))

Check whether or not table exists.

User defined table:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<tablename>]') AND type in (N'U'))

Temporary table:
IF OBJECT_ID('tempdb..#<temptablename>') IS NOT NULL

User-defined function samples.

The essential statements for the type of function are made bold-italic.

User-defined function returning a scalar value.

CREATE FUNCTION [DBO].[udf_DSB_Tijdreeks_Familie_ContainsGoederenGroepen] (
@TijdreeksID int,
@FamilieCode int)
RETURNS bit AS
BEGIN

declare @retval as bit
declare @teller as int

set @teller = (
select COUNT(*) from tbl_DSB_Tijdreeks
where TijdreeksID = @TijdreeksID
and FamilieCode = @FamilieCode
and (NOT RijCode IS null))

if @teller = 0
set @retval=0
else
set @retval=1

return @retval

END

User-defined function returning a table.

CREATE FUNCTION [DBO].[udf_DSB_Tijdreeks_Select_All_ByFamilieCodeAndCategory]
( @TijdreeksID int,
@FamilieCode int,
@CategoryCode int
)

returns @udf_DSB_Tijdreeks_Select_All_ByFamilieCodeAndCategory_TABLE Table(
Jaar int,
Periode varchar,
Status varchar,
Waarde float
)
as
begin
declare @GG int
 

SET @GG = (SELECT [dbo].[udf_DSB_Tijdreeks_Familie_ContainsGoederenGroepen](@TijdreeksID, @FamilieCode))
if @GG = 1
    begin
        insert into @udf_DSB_Tijdreeks_Select_All_ByFamilieCodeAndCategory_TABLE
        select
        T.Jaar as Jaar,
        T.Periode as Periode,
        T.Status as Status,
        T.Waarde as Waarde
        from tbl_DSB_Tijdreeks T
        where T.TijdreeksID = @TijdreeksID
        and T.FamilieCode = @FamilieCode
        and T.RijCode = @CategoryCode
        order by T.Jaar,T.Periode,T.Status
    end
else
    begin
        insert into @udf_DSB_Tijdreeks_Select_All_ByFamilieCodeAndCategory_TABLE
        select
        T.Jaar as Jaar,
        T.Periode as Periode,
        T.Status as Status,
        T.Waarde as Waarde
        from tbl_DSB_Tijdreeks T
        where T.TijdreeksID = @TijdreeksID
        and T.FamilieCode = @FamilieCode
        and T.KolomCode = @CategoryCode
        order by T.Jaar,T.Periode,T.Status
    end
return
end

Using extended properties.

-- This sample shows the usage of extended properties
-- Is uses the school database from http://msdn.microsoft.com/en-us/library/bb399731.aspx
-- (MSDN, ADO.NET Entity framework, Getting started, Quickstart, Creating the shool sample database)
-- The samples below Add,Read,Update and Delete extended properties for table, column, procedure and parameter
use school

--======================
--Add extended property
--======================
--Table
EXEC sys.sp_addextendedproperty 'MS_Description', 'Table comment1.', 'SCHEMA', [dbo], 'TABLE', Person, null,null
--Table column
EXEC sys.sp_addextendedproperty 'MS_Description', 'Column comment1.', 'SCHEMA', [dbo], 'TABLE', Person, 'COLUMN',PersonID
--Stored procedure
EXEC sys.sp_addextendedproperty 'MS_Description', 'Stored Procedure comment1.', 'SCHEMA', [dbo], 'PROCEDURE', 'InsertPerson', null,null
--Stored procedure parameter
EXEC sys.sp_addextendedproperty 'MS_Description', 'Parameter comment1.', 'SCHEMA', [dbo], 'PROCEDURE', InsertPerson, 'PARAMETER', '@LastName'

--======================
--Read extended property
--======================
-- table
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'Course', null, null);
-- table columns
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'Course', 'column', default);
-- procedure
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', 'InsertPerson', null, null);
-- parameters
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', 'InsertPerson', 'PARAMETER', default);

--=========================
--Update extended property
--=========================
--Table
EXEC sys.sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Table comment2.',
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'TABLE', @level1name = Person,
@level2type = Null, @level2name =null

-- Table column
EXEC sys.sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Column comment2.',
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'TABLE', @level1name = Person,
@level2type = N'COLUMN', @level2name=PersonID

--Stored procedure
EXEC sys.sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Stored Procedure comment1.',
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'PROCEDURE', @level1name = InsertPerson,
@level2type = Null, @level2name=Null

--Stored procedure parameter
EXEC sys.sp_updateextendedproperty
@name = N'MS_Description',
@value = N'Stored Procedure comment1.',
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'PROCEDURE', @level1name = InsertPerson,
@level2type = N'PARAMETER', @level2name='@LastName'

--========================
--Delete extended property
--========================

--Table
EXEC sys.sp_dropextendedproperty 'MS_Description', 'SCHEMA', [dbo], 'TABLE', Person, null,null
--Table column
EXEC sys.sp_dropextendedproperty 'MS_Description', 'SCHEMA', [dbo], 'TABLE', Person, 'COLUMN',PersonID
--Stored procedure
EXEC sys.sp_dropextendedproperty 'MS_Description', 'SCHEMA', [dbo], 'PROCEDURE', 'InsertPerson', null,null
--Stored procedure paremeter
EXEC sys.sp_dropextendedproperty 'MS_Description', 'SCHEMA', [dbo], 'PROCEDURE', InsertPerson, 'PARAMETER', '@LastName'

--=========================================
-- Read MS_Descriptions van procedures, functies en tabellen
--=========================================

SELECT objtype,objname,value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', default, null, null)
WHERE ( NOT (objname LIKE '%sp_%')) AND name = 'MS_Description'

SELECT objtype,objname,value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'FUNCTION', default, null, null)
WHERE ( NOT (objname LIKE '%sp_%')) AND name = 'MS_Description'

SELECT objtype,objname,value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'TABLE', default, null, null)
WHERE ( NOT (objname LIKE '%sp_%')) AND name = 'MS_Description'
 

SELECT objtype,objname,value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', 'InsertPerson', 'PARAMETER', default)
WHERE ( NOT (objname LIKE '%sp_%')) AND name = 'MS_Description'

--=========================================
-- Read MS_Descriptions van kolommen van <tablename>
--=========================================
SELECT objtype,objname,value
|FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'TABLE', '<tablename>', 'COLUMN', default)
WHERE ( NOT (objname LIKE '%sp_%')) AND name = 'MS_Description'

--===============================================
-- Read MS_Descriptions van parameters van procedure <routinename>
--===============================================
SELECT objtype,objname,value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', '<routinename>', 'PARAMETER', default)
WHERE ( NOT (objname LIKE '%sp_%')) AND name = 'MS_Description'

--===============================================
-- Read MS_Descriptions van parameters van function <routinename>
--===============================================
SELECT objtype,objname,value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'FUNCTION', '<routinename>', 'PARAMETER', default)
WHERE ( NOT (objname LIKE '%sp_%')) AND name = 'MS_Description'

Adding leading zeros to a string.

RIGHT('0' + CAST(@subperiode AS VARCHAR(2)),2)

The transaction log for database '<databasename>' is full.

select
  log_reuse_wait,
  log_reuse_wait_desc
from sys.databases
where name = '<databasename>'

see Troubleshooting a Full Transaction Log (Error 9002) and Managing Long-Running Transactions in MSDN Help.

The scope of a variable & GO statement.

The scope of a variable is the range of Transact-SQL statements that can reference the variable.
The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. For example, the following script generates a syntax error because the variable is declared in one batch and referenced in another:

DECLARE @MyVariable int;
SET @MyVariable = 1;
-- Terminate the batch by using the GO keyword.
GO
-- @MyVariable has gone out of scope and no longer exists.

-- This SELECT statement generates a syntax error because it is
-- no longer legal to reference @MyVariable.
SELECT BusinessEntityID, NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;

META Information in sql server.

Microsoft SQL Server 2005 provides the following collections of system views that expose metadata:

Catalog Views
Information Schema Views
Compatibility Views
Replication Views
Dynamic Management Views and Functions
Notification Services Views


Catalog views
-------------
Catalog views return information that is used by the Microsoft SQL Server 2005 Database Engine.
We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way
to obtain, transform, and present customized forms of this information.
All user-available catalog metadata is exposed through catalog views.

Information Schema Views
------------------------
An information schema view is one of several methods SQL Server 2005 provides for obtaining metadata.

Important:
Some changes have been made to the information schema views that break backward compatibility.
These changes are described in the topics for the specific views.

Note:
Information schema views provide an internal, system table-independent view of the SQL Server metadata.
Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables.
The information schema views included in SQL Server 2005 comply with the SQL-92 standard definition for the INFORMATION_SCHEMA.

Executing sql script from commandline.

Use SQLCMD

rem -S server_name [ \ instance_name ]
rem  -d db_name
rem  -i input_file
rem -E trusted connection
rem -w column_width (Specifies the screen width for output.)

sqlcmd -S %DBSERVER% -d %DBDATABASE% -E -i %script% -w 120 >> %LOGFILE%

OSQL is old and will be removed in a future version of SQL Server. (bron: SQL2008 documentation)

Grant

Grants permissions (Select,Insert,Exec atc.) on a securable (Table, SP, etc) to a principal (ApplicationRole, DatabaseRole, etc.).

Tables:
GRANT SELECT, INSERT, DELETE, UPDATE ON [dbo].[<TableName>] TO <ApplicationRole> | <DatabaseRole>

Sp's, Udf's:
GRANT EXEC ON [dbo].[<UspName>|<UdfName>] TO <ApplicationRole> | <DatabaseRole>

Application role

An application role is a database principal that enables an application to run with its own, user-like permissions.
You can use application roles to enable access to specific data to only those users who connect through a particular application.
Unlike database roles, application roles contain no members and are inactive by default.
Application roles work with both authentication modes.
Application roles are enabled by using sp_setapprole, which requires a password.
Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest.
Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.

Database role

To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals.
They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.

-- members for database-role: dbr_<RoleName>
Exec sp_AddRoleMember 'dbr_<RoleName>', '<DomainName>\<GroupName>'

Application role vs Database role

Use an Application role to give an application access to the database objects.
Use a Database role to give a group of users access to the database objects. (An Application role can even be added to the Database role)

Cursor

DECLARE @<Field1> <type>
DECLARE @<Field2> <type>

DECLARE db_cursor CURSOR FOR
SELECT <Field1>,<Field2>
FROM <?>
WHERE <?>

OPEN db_cursor
FETCH NEXT FROM db_cursor into @<Field1>,@<Field2>
WHILE @@FETCH_STATUS = 0
    BEGIN <?>
    FETCH NEXT FROM db_cursor INTO @<Field1>,@<Field2>
END
CLOSE db_cursor
DEALLOCATE db_cursor

Dropping a Database Role

Drop Role members before dropping Role, otherwise:
"The role has members. It must be empty before it can be dropped."

DECLARE @RoleName sysname
set @RoleName = N'dbr_XXXX_Reader'
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
    DECLARE @RoleMemberName sysname
    DECLARE Member_Cursor CURSOR FOR
    select [name] from sys.database_principals where principal_id in ( select member_principal_id
    from sys.database_role_members
    where role_principal_id in  (
        select principal_id
        FROM sys.database_principals
        where [name] = @RoleName AND type = 'R' ))
    OPEN Member_Cursor;
    FETCH NEXT FROM Member_Cursor into @RoleMemberName
        WHILE @@FETCH_STATUS = 0
    BEGIN
    exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
    FETCH NEXT FROM Member_Cursor into @RoleMemberName
    END;
    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
End
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'dbr_XXXX_Reader' AND type = 'R')
    DROP ROLE [dbr_XXXX_Reader]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'dbr_XXXX_Reader' AND type = 'R')
CREATE ROLE [dbr_XXXX_Reader] AUTHORIZATION [dbo]
GO