SQL Server Data Types Test Tables
These tables can be used for testing various data types of SQL Server. You can extend as needed.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
-- Create XML Schema Collection
CREATE XML SCHEMA COLLECTION PartitionMapSchema AS
N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="https://www.w3.org/2001/XMLSchema">
<xs:element name="Partition_Map">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Value" type="xs:date" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>' ;
GO
-- Create Table DataType
CREATE TABLE [dbo].[Table_DataType](
[Table_DataType_ID] [bigint] IDENTITY(1,1) NOT NULL,
[FK_Table_FK_01_ID] [bigint] NOT NULL,
[col_guid] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [Guid_Default] DEFAULT (newsequentialid()),
[col_bigint] [bigint] NOT NULL CONSTRAINT [DF_Table_DataType_col_bigint] DEFAULT ((0)),
[col_bit] [bit] NOT NULL CONSTRAINT [DF_Table_DataType_col_bit] DEFAULT ((1)),
[col_binary] [binary](1) NULL CONSTRAINT [DF_Table_DataType_col_binary] DEFAULT ((2)),
[col_char] AS (left([col_nchar],N'3')),
[col_datetime] AS (getdate()),
[col_decimal] [decimal](18, 9) NULL CONSTRAINT [DF_Table_DataType_col_decimal] DEFAULT ((5)),
[col_float] [float] NULL CONSTRAINT [DF_Table_DataType_col_float] DEFAULT ((6)),
[col_image] [image] NULL,
[col_int] [int] NOT NULL CONSTRAINT [DF_Table_DataType_col_int] DEFAULT ((8)),
[col_money] [money] NOT NULL CONSTRAINT [DF_Table_DataType_col_money] DEFAULT ((9)),
[col_nchar] [nchar](100) NULL CONSTRAINT [DF_Table_DataType_col_nchar] DEFAULT (N'10'),
[col_ntext] [ntext] NOT NULL CONSTRAINT [DF_Table_DataType_col_ntext] DEFAULT (N'11'),
[col_numeric] [numeric](18, 9) NOT NULL CONSTRAINT [DF_Table_DataType_col_numeric] DEFAULT ((12)),
[col_nvarchar] [nvarchar](256) NOT NULL CONSTRAINT [DF_Table_DataType_col_nvarchar] DEFAULT (N'13'),
[col_real] [real] NOT NULL CONSTRAINT [DF_Table_DataType_col_real] DEFAULT (N'14'),
[col_smalldatetime] [smalldatetime] NOT NULL CONSTRAINT [DF_Table_DataType_col_smalldatetime] DEFAULT (N'15'),
[col_smallint] [smallint] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallint] DEFAULT ((16)),
[col_smallmoney] [smallmoney] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallmoney] DEFAULT ((17)),
[col_sql_variant] [sql_variant] NOT NULL CONSTRAINT [DF_Table_DataType_col_sql_variant] DEFAULT ((18)),
[col_sysname] [sysname] NULL,
[col_text] [text] NOT NULL CONSTRAINT [DF_Table_DataType_col_text] DEFAULT ('20'),
[col_timestamp] [timestamp] NULL,
[col_tinyint] [tinyint] NOT NULL CONSTRAINT [DF_Table_DataType_col_tinyint] DEFAULT ((22)),
[col_uniqueidentifier] [uniqueidentifier] NULL,
[col_varbinary] [varbinary](1) NOT NULL CONSTRAINT [DF_Table_DataType_col_varbinary] DEFAULT ((24)),
[col_varchar] [varchar](1024) NOT NULL CONSTRAINT [DF_Table_DataType_col_varchar] DEFAULT ('25'),
[col_xml] [xml](CONTENT [dbo].[PartitionMapSchema]) NULL CONSTRAINT [DF_Table_DataType_col_xml] DEFAULT (CAST(N'<Partition_Map><Value>1900-01-01</Value><Value>2049-12-31</Value></Partition_Map>' AS xml)),
CONSTRAINT [PK_Table_DataType] PRIMARY KEY CLUSTERED
(
[Table_DataType_ID] ASC,
[col_bigint] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-- Create Table FK
CREATE TABLE [dbo].[Table_FK_01](
[Table_FK_01_ID] [bigint] IDENTITY(1,1),
[col_bigint] [bigint] NOT NULL CONSTRAINT [DF_Table_FK_01_col_bigint] DEFAULT ((0)), -- 1
CONSTRAINT [PK_Table_FK_01] PRIMARY KEY CLUSTERED
(
[Table_FK_01_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Create Constraints
ALTER TABLE [dbo].[Table_DataType] WITH CHECK ADD CONSTRAINT [FK_Table_DataType_Table_FK_01] FOREIGN KEY([FK_Table_FK_01_ID])
REFERENCES [dbo].[Table_FK_01] ([Table_FK_01_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Table_DataType] CHECK CONSTRAINT [FK_Table_DataType_Table_FK_01]
GO
Technorati Tags: SQL, Test, Table, SQL Server, XML, Datatype