Table Variables

Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. In many cases a table variable can outperform a solution using a temporary table, although we will need to review the strengths and weaknesses. Scope Unlike the majority of the other data types in SQL Server, you cannot use a... » read more


Used to convert rows to columns. Example 1: Example 2: Sources:

Add Additional Partition to an Existing Partition Table

-- Add additional file group. ALTER DATABASE Test02 ADD FILEGROUP [fg202001_tbTable] GO -- Assoicate file to file group. ALTER DATABASE Test02 ADD FILE (NAME = N'fg202001_tbTable', FILENAME = N'D:\MSSQL\DATA\Test02\fg202001_tbTable.ndf' , SIZE = 1000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) TO FILEGROUP [fg202001_tbTable] GO -- Add additional file group to partition scheme. ALTER PARTITION SCHEME... » read more

Creating Partitions on a Table

Object Creation File Group (with correct folder structure) Partition Function Partition Schema Partition Table Note: If the database is part of Always On, make sure the folder structure has been created on the secondary servers as well. Create File Group and Associate File to File Group ALTER DATABASE Test02 ADD FILEGROUP [fg201701_tbImageFpMatchARCH] GO ALTER DATABASE... » read more

Checking for Partitions

Return all partition table in the database. The following query returns one or more rows if the table PartitionTable is partitioned. If the table is not partitioned, no rows are returned. SELECT * FROM sys.tables AS t JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[type] IN (0,1) JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id... » read more

Get All Indexes for a Table

Get all Index for a table… EXEC sys.sp_helpindex @objname = N'tblXXXXX' Or query from the indexes system table… SELECT TableName =, IndexName =, IndexId = ind.index_id, ind.* FROM sys.indexes ind INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE = 'tblXXXXX' For those tables with multiple partitions… SELECT objectname, indexname, partition_id,... » read more

List of Child Objects

SELECT name, SCHEMA_NAME(schema_id) AS schema_name, type_desc, type FROM sys.objects WHERE parent_object_id = (OBJECT_ID('dbo.tblXXXXX')) ORDER BY name; Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint FN = SQL scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF... » read more

Database Table Definition with Extended Property

Extended properties can be all sorts of annotations added about an object. They can be added manually, or by a tool. The extended properties has no effect on queries accessing the object. For specifying extended properties, the objects in a SQL Server database are classified into three levels: 0, 1, and 2. Level 0 is... » read more

Query for all child objects

SELECT name, SCHEMA_NAME(schema_id) AS schema_name, type_desc , type FROM sys.objects WHERE parent_object_id = (OBJECT_ID(‘dbo.tblXXXX’)) AND type IN (‘C’,’F’,’PK’,’D’, ‘UQ’) order by name; GO