intellectually constipated

patrick gallucci's technology brain drain

SQL Server Data Types Test Tables

SQL Server Data Types Test Tables

Rate This
  • Comments 1

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="http://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: , , , , ,
 
Comments
  • These tables can be used for testing various data types of SQL Server. You can extend as needed. SET

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 5 and 3 and type the answer here:
  • Post