MSSQL Server

All posts containing MSSQL-related scripts and patterns.

MSSQL Create Database With Multiple Filegroups (QuickRef)

/ MSSQL Server /

Query to create a database with multiple filegroups.

CREATE DATABASE [DatabaseName]
ON PRIMARY
  ( Name = 'DBNamePFG', FILENAME = 'D:\DEF\DBNamePFG.mdf', SIZE = 100 MB, FILEGROWTH = 100 MB ),
FILEGROUP [FileGroup2]
  ( Name = 'DBNameFG2a', FILENAME = 'E:\DEF\DBNAmeFG2a.ndf', SIZE = 100 MB, FILEGROWTH = 100 MB ),
  ( Name = 'DBNameFG2b', FILENAME = 'F:\DEF\DBNAmeFG2b.ndf', SIZE = 100 MB, FILEGROWTH = 100 MB )
LOG ON
  ( Name = 'DBNameLog', FILENAME = 'L:\DEF\DBNameLog.ldf', SIZE = 1 GB, FILEGROWTH = 500 MB )

MSSQL Set All Databases to FULL Recovery

/ MSSQL Server /

This script sets all databases to FULL recovery (except for master, model and tempdb). We can avoid using a cursor by making use of a temporary table. First a temptable gets filled with execution commands, and then the script executes each row until the record id equals the total number of records.

-- Declarations and temptables...
DECLARE @iNumRecs int, @iRecNum int = 1, @vCommand nvarchar(max)
CREATE TABLE #tCommands (ID int IDENTITY(1,1), Command nvarchar(max))

-- Insert Commands into temptable...
INSERT INTO #tCommands (Command)
SELECT N'ALTER DATABASE [' + name + '] SET RECOVERY FULL WITH NO_WAIT' 
FROM [master].[sys].[databases]
WHERE recovery_model_desc = 'SIMPLE' AND name NOT in ('master', 'msdb', 'tempdb')

-- Execute commands from temptable...
SELECT @iNumRecs = COUNT(*) FROM #tCommands
WHILE @iRecNum <= @iNumRecs
BEGIN
 SELECT @vCommand = Command FROM #tCommands
 WHERE ID = @iRecNum
 -- Execute command and increase record number...
 -- EXECUTE(@vCommand) --Alternative, Not preferred...
 EXECUTE sys.sp_executesql @stmt = @vCommand
 SET @iRecNum += 1
END

-- Cleanup...
DROP TABLE #tCommands

MSSQL Retreive Column Info From Tables

/ MSSQL Server /

With this script you can retrieve column info (datatype, length, precision, scale and collation) from all tables in a specific database.

-- Retrieve column info from all tables in a database...
USE [Databasename]
GO

-- Declare variables...
DECLARE @vTableName AS nvarchar(256)

-- Create temptable...
CREATE TABLE #tDBColumnInfo (
  [tablename] [varchar] (100) NULL,
  [columnname] [varchar] (100) NULL,
  [datatype] [varchar] (50) NULL,
  [length] [int] NULL,
  [xprec] [int] NULL,
  [xscale] [int] NULL,
  [collation] [varchar] (100) NULL
  ) 

-- Get Tablenames...
DECLARE curTableNames CURSOR FOR
SELECT [name] from sys.tables

-- Get columns from table...
OPEN curTableNames
  FETCH NEXT FROM curTableNames INTO @vTableName
  WHILE @@FETCH_STATUS = 0   
BEGIN
  INSERT INTO #tDBColumnInfo
  SELECT 
       @vTableName AS 'tablename',
       sc.[name] AS [columnname], 
       st.[name] AS [datatype], 
       sc.[length], 
       sc.[xprec], 
       sc.[xscale], 
       sc.[collation] 
  FROM syscolumns sc
  LEFT OUTER JOIN systypes st on sc.xtype = st.xtype
  WHERE id = ( SELECT id FROM sysobjects 
               WHERE [type] = 'U' 
               AND [Name] = @vTableName )
  FETCH NEXT FROM curTableNames INTO @vTableName
END

-- Cleanup cursor...
CLOSE curTableNames
DEALLOCATE curTableNames

-- Display Result and cleanup temptable...
SELECT * FROM #tDBColumnInfo
DROP Table #tDBColumnInfo

MSSQL Get Settings and Counters w. PowerShell

/ MSSQL Server / 
# Query Server Configuration...
Invoke-Sqlcmd -Query "SELECT * FROM sys.configurations WHERE description LIKE '%server memory%'"

# Server performance counter Page life expectancy (>(300s/4G))...
# Server performance counter Lazy writes/sec (take 2 samples with 1s between samples)...
Invoke-Sqlcmd -Query "SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)', 'Page life expectancy', 'Lazy writes/sec')"

MSSQL 2012 New T-SQL Features

/ MSSQL Server /

The example query below uses a new feature in SQL Server 2012 to divide the result of a query in pages. This particular example will start from the second record and retrieves the following 2 records.

SELECT [name], [value] FROM Table1
ORDER BY id
OFFSET 2 ROWS
FETCH NEXT 2 ROWS

The next query adds a derived column to the result with a cumulative sum based on a specific column. When using PARTITION BY the sum is only applied to the specific group.

SELECT [name], [value], 
SUM(value) OVER (
    --PARTITION BY [name] --SUM by column name only...
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cummulativeValue
FROM Table1

MSSQL combine SUMS from 2 tables

/ MSSQL Server /

Below query combines the SUMS of two tables into a total SUM for a specific column.

WITH combiTable ([Table], xName, xValue)
AS ( SELECT 1 AS [Table], name, SUM(value) FROM Table1 GROUP BY name
     UNION
     SELECT 2 AS [Table], name, SUM(value) FROM Table2 GROUP BY name )
SELECT xName AS Name, SUM(xValue) AS Total FROM combiTable
GROUP BY xName
ORDER BY Total DESC
GO

MSSQL Select with XML Output

/ MSSQL Server /

The following query returns a result in XML format. the root1 option (root element) adds a root element named “root element” to the file. The option elements creates elements instead of attributes. The auto option creates standard elements with the table name and column names mapped to elements.

SELECT * FROM [tablename] FOR XML auto, root('rootelement'), elements

Another example with a custum (nested) XML layout…

-- Retrieve instancename and other info from database and place info under element intances.instance...
SELECT   i.instance AS [Name]
       , i.edition AS [Edition]
       , i.patchlevel AS [Patchlevel]
       -- Retrieve info from database table on key instance_id place info under child element Databases...
       , ( SELECT   d.[database] AS [Name]
                  , d.RecoveryModel AS [RecoveryModel] 
                  -- Retrieve ref data from OTAP table...
                  , ( SELECT e.OTAP FROM OTAP e WHERE e.ID_OTAP = d.ID_OTAP ) AS [OTAP]
                  -- Retrieve info from application to which this database belongs...
                  , ( SELECT   a.Applicatienaam AS [Name]
                             , a.AuthenticatieModus As [AuthType]
                      FROM applicatie a WHERE a.ID_applicatie = d.ID_applicatie 
                      FOR XML PATH ('Application'), Type )
           FROM [database] d 
           WHERE d.instance_id = i.instance_id 
           FOR XML PATH ('Database'), Type 
           ) AS [Databases]
FROM [dbo].[instances] i FOR XML PATH('Instance'), root('Instances'), Elements

XML Output:

<instances>
  <instance>
    <name>SERVER1\INSTANCE</name>
    <edition>Enterprise Edition</edition>
    <patchlevel>11.00.8888.00 (SPX)</patchlevel>
    <databases>
      <database>
        <name>Database1</name>
        <recoverymodel>SIMPLE</recoverymodel>
        <otap>Production</otap>
        <applicatie>
          <name>Application1</name>
          <authtype>SQL Server</authtype>
        </applicatie>
      </database>
    </databases>
  </instance>
  <instance>
    <name>SERVER2</name>
    <edition>Enterprise Edition</edition>
    <patchlevel>11.00.9999.00 (SPX)</patchlevel>
    <databases>
      <database>
        <name>Database2</name>
        <recoverymodel>FULL</recoverymodel>
        <otap>Production</otap>
        <applicatie>
          <name>Application2</name>
          <authtype>Windows</authtype>
        </applicatie>
      </database>
      <database>
        <name>Database3</name>
        <recoverymodel>FULL</recoverymodel>
        <otap>Testing</otap>
        <applicatie>
          <name>Application2</name>
          <authtype>Windows</authtype>
        </applicatie>
      </database>
    </databases>
  </instance>
</instances><instances>
</instances>

MSSQL Table Switch IN/OUT examples

/ MSSQL Server /

MSSQL table switch IN, switch OUT examples…

-- Switch in/out procedure examples...
-- Tim van Kooten Niekerk


-- Partition function en schema maken...
--DROP PARTITION FUNCTION pfTable_1
--DROP PARTITION SCHEME psTable_1
CREATE PARTITION FUNCTION pfTable_1 (int) AS RANGE LEFT FOR VALUES (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
CREATE PARTITION SCHEME psTable_1 AS PARTITION pfTable_1 ALL TO ([PRIMARY])


-- Make  table with a partition funciton on column jaar (year)...
CREATE TABLE [dbo].[Table_3](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] NOT NULL
) ON [psTable_1]([jaar])


-- Check partitioned table...
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Table_3') ORDER BY partition_number

-- Make Switch OUT and truncate table so you can reload 2015...
-- Make Switch OUT table without constraints...
CREATE TABLE [dbo].[Table_3_OUT](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] NOT NULL 
)
ALTER TABLE Table_3 SWITCH PARTITION 5 TO Table_3_OUT
TRUNCATE TABLE [dbo].[Table_3_OUT] 


-- Now you can reload (INSERT) new values for 2015 in the original table... 
-- Or... Make Switch IN with using a switch IN table for 2015...

 
-- Switch IN table with constraint on jaar = 2015...
CREATE TABLE [dbo].[Table_3_IN](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] CHECK (jaar = 2015) NOT NULL 
)
-- INSERT new values in switch IN table and make switch...
ALTER TABLE Table_3_IN SWITCH TO Table_3 PARTITION 5


-- Extend partition function with a new year...
ALTER PARTITION SCHEME psTable_1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION pfTable_1 () SPLIT RANGE (2021)

MSSQL Exchange names 2 databases

/ MSSQL Server /

Using the following Stored Procedure you can swap the names of two databases. For example, to bring a recently updated database online, after which the old database can be filled again. To exchange database names, a third name is required. This is the name of database2 with the addition ‘_OLD’.

/****** Object:  StoredProcedure [dbo].[DbNameExchange]    Script Date: 12/01/2012 09:15:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==============================================================
-- Author:		Tim van Kooten Niekerk
-- Create date: 2012-12-01-A
-- Description:	Exchange database names between 2 databases...
-- ==============================================================

CREATE PROCEDURE [dbo].[DbNameExchange]
    @vDatabase1 nvarchar(256),
    @vDatabase2 nvarchar(256)
    
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @H1 AS int
  DECLARE @vDatabaseT AS nvarchar(256)
  DECLARE @vQuery1 AS nvarchar(512)

  -- T.b.v. wisselen van database namen is er een derde naam nodig...
  SET @vDatabaseT = @vDatabase2 + N'_OLD'

  -- Check rename action prefered state before rename action 1/3...
  IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
  BEGIN
    -- Geef de secundaire db een tijdelijke naam...
    BEGIN TRY
      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase2 + '] MODIFY NAME = [' + @vDatabaseT + '];ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
      EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END TRY
    BEGIN CATCH
    END CATCH
    -- Check rename action prefered state before rename action 2/3...
    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 0
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
    BEGIN
      -- Geef de primaire db de naam van de secundaire db...
      BEGIN TRY
	    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase1 + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
      END TRY
      BEGIN CATCH
      END CATCH
  	  -- Check rename action prefered state before rename action 3/3...
      IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 0
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
      BEGIN
        -- Geef de db met de tijdelijke naam de naam van de primaire db...
        BEGIN TRY
	      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        END TRY
	    BEGIN CATCH
        END CATCH
	    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
	    BEGIN
	      PRINT 'INFO: Rollout actie correct uitgevoerd...'
	    END
	    ELSE BEGIN
          PRINT 'ERROR: Rollout actie is niet volledig uitgevoerd, probeer het nomaals...'
	      BEGIN TRY 
	        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
            EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
          END TRY
	      BEGIN CATCH
          END CATCH
          -- Niet gelukt, dan in ieder geval multi_user activeren...
		  SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
	    END
	  END
    ELSE BEGIN
	    -- Something went wrong trying to rollback...
	    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
  	  END
    END
    ELSE BEGIN
    -- Something went wrong trying to rollback...
    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
    EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END
    -- Unprepare statements...
    EXEC sp_unprepare @H1
  END
  ELSE BEGIN
    PRINT 'ERROR: Startsituatie niet correct...'
  END
END

GO