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

Comments

• Anonymous
  August 25, 2007
  These tables can be used for testing various data types of SQL Server. You can extend as needed. SET