SQL Server is tied to Transact-SQL (T-SQL), an implementation of SQL from Microsoft that adds a set of proprietary programming extensions to the standard language.
Here's an example of a T-SQL CREATE TABLE statement that includes all the data types and all the possible constraints:
CREATE TABLE ExampleTable
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Column1 BIT NOT NULL CHECK (Column1 IN (0, 1)),
Column2 TINYINT CHECK (Column2 > 0),
Column3 SMALLINT,
Column4 INT FOREIGN KEY REFERENCES OtherTable(ID),
Column5 BIGINT UNIQUE,
Column6 DECIMAL(10,2) DEFAULT 0.00,
Column7 NUMERIC(5,2),
Column8 MONEY,
Column9 SMALLMONEY,
Column10 FLOAT(24),
Column11 REAL,
Column12 CHAR(10),
Column13 VARCHAR(50),
Column14 NCHAR(10),
Column15 NVARCHAR(100),
Column16 TEXT,
Column17 NTEXT,
Column18 BINARY(8),
Column19 VARBINARY(16),
Column20 IMAGE,
Column21 DATE,
Column22 DATETIME,
Column23 DATETIME2(3),
Column24 SMALLDATETIME,
Column25 TIME,
Column26 TIMESTAMP,
Column27 XML,
Column28 UNIQUEIDENTIFIER,
Column29 CONSTRAINT CK_Column1 CHECK (Column1 IN (0,1)),
Column30 CONSTRAINT DF_Column3 DEFAULT 0,
Column31 CONSTRAINT PK_Column5 PRIMARY KEY CLUSTERED,
Column32 CONSTRAINT FK_Column4 FOREIGN KEY (Column4) REFERENCES OtherTable(ID),
Column33 CONSTRAINT UQ_Column6 UNIQUE NONCLUSTERED,
Column34 CONSTRAINT CK_Column7 CHECK (Column7 >= 0),
Column35 CONSTRAINT DF_Column8 DEFAULT 0,
Column36 CONSTRAINT DF_Column9 DEFAULT 0.00,
Column37 CONSTRAINT DF_Column10 DEFAULT 0,
Column38 CONSTRAINT DF_Column11 DEFAULT 0.0,
Column39 CONSTRAINT CK_Column12 CHECK (LEN(Column12) = 10),
Column40 CONSTRAINT CK_Column13 CHECK (LEN(Column13) <= 50),
Column41 CONSTRAINT CK_Column14 CHECK (LEN(Column14) = 10),
Column42 CONSTRAINT CK_Column15 CHECK (LEN(Column15) <= 100),
Column43 CONSTRAINT DF_Column18 DEFAULT 0x00,
Column44 CONSTRAINT UQ_Column19 UNIQUE NONCLUSTERED,
Column45 CONSTRAINT DF_Column21 DEFAULT GETDATE(),
Column46 CONSTRAINT DF_Column22 DEFAULT GETDATE(),
Column47 CONSTRAINT DF_Column23 DEFAULT GETDATE(),
Column48 CONSTRAINT DF_Column24 DEFAULT GETDATE(),
Column49 CONSTRAINT DF_Column25 DEFAULT '00:00:00.0000000',
Column50 CONSTRAINT DF_Column27 DEFAULT '<Data></Data>',
Column51 CONSTRAINT DF_Column28 DEFAULT NEWID(),
Column52 CONSTRAINT CK_Column29 CHECK (LEN(Column16) <= 8000),
Column53 CONSTRAINT CK_Column30 CHECK (LEN(Column17) <= 4000),
Column54 CONSTRAINT CK_Column33 CHECK (LEN(Column27) <= 8000)
);In this example, we have created a table called ExampleTable with 54 columns, each with a different data type and constraint:
ID: This column is defined as an INT data type with an IDENTITY property that automatically generates a new value for each new row added to the table, and a PRIMARY KEY constraint that uniquely identifies each row in the table.
ID: column is defined as an integer with an identity property, meaning it will auto-increment by 1 starting from 1 as new rows are inserted. It is also the primary key of the table.
Column1: This column is defined as a BIT data type that can have one of two states: 0 or 1.
Column2: This column is defined as a TINYINT data type with a CHECK constraint that ensures that the value in this column is greater than 0. Representing an 8-bit integer value. It can store integer values between 0 and 255.
Column3: This column is defined as a SMALLINT data type that can store whole numbers within the range of -32,768 to 32,767. It takes 2 bytes.
Column4: This column is defined as an INT data type that can store numbers from -2,147,483,648 to 2,147,483,647. It takes 4 bytes with a FOREIGN KEY constraint that references the ID column of the OtherTable table.
Column5: This column is defined as a BIGINT data type that ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. It takes up 8 bytes with a UNIQUE constraint that ensures that each value in this column is unique.
Column6: This column is defined as a DECIMAL data type with a precision of 10 and a scale of 2, and a DEFAULT constraint that sets the default value for this column to 0.00.
Column7: This column is defined as a NUMERIC data type with a precision of 5 and a scale of 2, with no additional constraints.
Column8: This column is defined as a MONEY data type with no additional constraints.
Column9: This column is defined as a SMALLMONEY data type with no additional constraints.
Column10: This column is defined as a FLOAT data type with a precision of 24, with no additional constraints.
Column11: This column is defined as a REAL data type with no additional constraints.
Column12: This column is defined as a CHAR data type with a length of 10, with a CHECK constraint that ensures that the length of each value in this column is 10 characters.
Column13: This column is defined as a VARCHAR data type with a maximum length of 50 characters, with a CHECK constraint that ensures that the length of each value in this column is less than or equal to 50 characters.
Column14: This column is defined as an NCHAR data type with a length of 10, with a CHECK constraint that ensures that the length of each value in this column is 10 characters.
Column15: This column is defined as an NVARCHAR data type with a maximum length of 100 characters, with a CHECK constraint that ensures that the length of each value in this column is less than or equal to 100 characters.
Column16: This column is defined as a TEXT data type with a maximum length of 8000 characters, with a CHECK constraint that ensures that the length of each value in this column is less than or equal to 8000 characters.
Column17: This column is defined as an NTEXT data type with a maximum length of 4000 characters, with a CHECK constraint that ensures that the length of each value in this column is less than or equal to 4000 characters.
Column18: This column is defined as a BINARY data type with a length of 8 bytes, with a DEFAULT constraint that sets the default value for this column to 0x00.
Column19: This column is defined as a VARBINARY data type with a maximum length of 16 bytes, with a UNIQUE constraint that ensures that each value in this column is unique.
Column20: This column is defined as an IMAGE data type with no additional constraints.
Column21: This column is defined as a DATE data type with no additional constraints.
Column22: This column is defined as a DATETIME data type with no additional constraints.
Column23: This column is defined as a DATETIME2 data type with a scale of 3, with no additional constraints.
Column24: This column is defined as a SMALLDATETIME data type with no additional constraints.
Column25: This column is defined as a TIME data type with no additional constraints.
Column26: This column is defined as a TIMESTAMP data type with no additional constraints.
Column27: This column is defined as an XML data type with a maximum length of 8000 characters, with a DEFAULT constraint that sets the default value for this column to an empty XML element
Column28: UNIQUEIDENTIFIER data type is used to store a globally unique identifier (GUID) value. The DEFAULT constraint is used to set the default value of the column to a new GUID value generated using the NEWID() function.
Column29: A CHECK constraint is used to limit the possible values for the BIT data type to either 0 or 1.
Column30: The DEFAULT constraint is used to set the default value of the SMALLINT data type to 0.
Column31: The PRIMARY KEY constraint is used to define the column as the primary key of the table. The CLUSTERED keyword is used to specify that the index created for this constraint should be clustered, meaning the physical order of data in the table should be based on the primary key values.
Column32: The FOREIGN KEY constraint is used to define a relationship between this column and the ID column of another table called OtherTable.
Column33: The UNIQUE constraint is used to ensure that the values in this column are unique, but it is not used as the primary key of the table. The NONCLUSTERED keyword is used to specify that the index created for this constraint should be non-clustered.
Column34: A CHECK constraint is used to ensure that the values in the NUMERIC(5,2) data type are greater than or equal to 0.
Column35: The DEFAULT constraint is used to set the default value of the MONEY data type to 0.
Column36: The DEFAULT constraint is used to set the default value of the SMALLMONEY data type to 0.00.
Column37: The DEFAULT constraint is used to set the default value of the FLOAT(24) data type to 0.
Column38: The DEFAULT constraint is used to set the default value of the REAL data type to 0.0.
Column39: A CHECK constraint is used to ensure that the CHAR(10) data type has a length of exactly 10 characters.
Column40: A CHECK constraint is used to ensure that the VARCHAR(50) data type has a length of no more than 50 characters.
Column41: A CHECK constraint is used to ensure that the NCHAR(10) data type has a length of exactly 10 characters.
Column42: A CHECK constraint is used to ensure that the NVARCHAR(100) data type has a length of no more than 100 characters.
Column43: The DEFAULT constraint is used to set the default value of the BINARY(8) data type to a byte array with all values set to 0x00.
Column44: The UNIQUE constraint is used to ensure that the values in this column are unique, but it is not used as the primary key of the table. The NONCLUSTERED keyword is used to specify that the index created for this constraint should be non-clustered.
Column45: The DEFAULT constraint is used to set the default value of the DATE data type to the current system date using the GETDATE() function.
Column46: The DEFAULT constraint is used to set the default value of the DATETIME data type to the current system date and time using the GETDATE() function.
Column47: The DEFAULT constraint is used to set the default value of the DATETIME2(3) data type to the current system date and time with a precision of 3 decimal places using the GETDATE() function.
Column48: The DEFAULT constraint is used to set the default value of the SMALLDATETIME data type to the current system date and time using the GETDATE() function.
Column49: "TIMESTAMP" data type with a default value of '00:00:00.0000000'
Column50: "XML" data type with a default value of '<Data></Data>'
Column51: "UNIQUEIDENTIFIER" data type with a default value of a new unique identifier generated by the NEWID() function
Column52: "TEXT" data type with a length check constraint of 8000 characters or less
Column53: "NUMERIC(5,2)" data type with a length check constraint of 4000 characters or less
Column54: "NTEXT" data type with a length check constraint of 8000 characters or less
This example demonstrates the flexibility of T-SQL in defining columns with different data types and constraints. It is important to note that not all data types and constraints are suitable for all situations, and the choice of data type and constraint should be based on the specific needs of your application or database.
Comments