01 - Table (DDL)

Hi,

 

Here we’ll cover below –

 

-          Table

-          Constraints

-          SP

-          Functions

-          Index

-          Triggers

 

 

Computed Column

 

·          A column defined within a CREATE TABLE statement can be derived from a computation, and is called a Computed Column.

·          Computed columns are not actually stored in the table.

·          Computed columns removes the needof placing calculations in the SELECT query itself, and redistribute the overhead to the table level.

 

Computed columns:

 

·          Cannot use sub-queries in the computation

·          Cannot be used with a DEFAULT definition

·          Cannot be use for a FOREIGN KEY definition

·          Cannot be the target of an INSERT

·          Cannot be the target of an UPDATE

 

·          Can use other columns in the table for the computation

·          Can use deterministic functions or variables

·          Can use mathematical operators such as *, /, +, and -

·          Can be referenced in SELECT expressions and elements within a SELECT statement (WHERE, ORDER BY, GROUP BY, HAVING)

·          Can define, or be a part of, a primary key

·          Can define, or be a part of, a unique constraint

 

 

E.g.

CREATE TABLE BookRepository14.dbo.BookSales

(iBookId int NOT NULL,

moBookPrice money NOT NULL,

moTaxes money NOT NULL,

moTotalSales money NOT NULL,

moTotalPrice as (moBookPrice + moTaxes))

 

INSERT BookSales (iBookId, moBookPrice, moTaxes) VALUES (2, 55.00, 1.50)

 

Notice that the moTotalPrice calculated column is not included in the INSERT statement. Rather its value will automatically be inserted.

 

 

IDENTITY Property

 

·          The IDENTITY property allows you to define an automatically incrementing numeric value for a specific column or columns.

·          The data type for an identity column can be INT, TINYINT, SMALLINT, BIGINT, DECIMAL, or NUMERIC.

·          Tables may only have one identity column defined, and the defined IDENTITY column cannot have a DEFAULT or rule settings associated with it.

 

 

CREATE TABLE <table_name> (

<column_name_1> <datatype_for_column_1> Nullability,

<column_name_2> <datatype_for_column_2> Nullability

IDENTITY (seed, increment) <NOT FOR REPLICATION>)

 

 

SET IDENTITY_INSERT  -

 

·          While insertion, no need to provide value for this column. However, If for some reason you have to insert an explicit value into an IDENTITY column, you could use the clause to enable updates.

 

SET IDENTITY_INSERT Books ON

GO

INSERT Books (iBookId, vchBookName, moBookPrice)

VALUES (12, 'I Claudius', 6.99)

GO

SET IDENTITY_INSERT Books OFF

GO

 

DBCC CHECKIDENT

 

·          Used to check the current maximum IDENTITY value for a table

·          Used to set the IDENTITY seed value explicitly

 

DBCC CHECKIDENT (MyTable, RESEED)

DBCC CHECKIDENT (MyTable, RESEED, 50)

 

Use ROWGUIDCOL

 

·          Unlike the IDENTITY column, which guarantees uniqueness within the defined table, the ROWGUIDCOL property ensures uniqueness across all databases in the world.

·          This unique ID is generated by a combination of the ROWGUIDCOL property, the UNIQUEIDENTIFIER data type, and the NEWID() function

 

CREATE TABLE BookShipment

(unqShipmentID uniqueidentifier ROWGUIDCOL CONSTRAINT defID DEFAULT NEWID(),  vchShipmentDescription varchar(100) NULL)

 

 

Add Column to Existing Table

 

ALTER TABLE <tablename> ADD <column_definition>

 

·          When adding a new column, the column must either accept NULL values or have a DEFAULT constraint defined.

 

 

Drop Column

 

·          You cannot drop a column In below cases.

 

·          Any constraints or rules are referencing the column; this includes PRIMARY KEY, FOREIGN KEY, UNIQUE, DEFAULT and CHECK constraints.

·          The column is participating in replication.

·          The column is used in an index.

 

ALTER TABLE Books DROP CONSTRAINT cnsCoverType

ALTER TABLE Books DROP COLUMN chCover

 

 

 

Change a Column Definition

 

·          You can change a column definition using ALTER TABLE and ALTER COLUMN.

·          This includes changing the column data type, nullability, and collation.

 

·          Column cannot be modified in below cases.

 

·          Data types TEXT, NTEXT, IMAGE, or TIMESTAMP.

·          Id used in a PRIMARY KEY, FOREIGN KEY, CHECK, or UNIQUE constraint.

·          If the column data type is of variable length, the column can have the data type length increased for CHECK  or UNIQUE

·          The column, if referenced by a CREATE STATISTICS statement, must DROP STATISTICS first. This does not include automatically generated statistics.

·          If currently replicated in a publication.

·          If used in an index, unless expanding the column size of a VARCHAR, NVARCHAR, or VARBINARY data type.

·          If you are changing to a new data type, the new data type must be compatible and convertible from the old type. The new data type may not be a timestamp. If the column is an IDENTITY column, the new data type must support the IDENTITY property.

 

 

 

 

Drop a Table

 

Table cannot be dropped in below situation –

 

·          You cannot drop a table that is referenced by a foreign key constraint.

·          If you drop a table being referenced by a view or stored procedure, these objects must either be dropped or changed to reference a different table.

·          Also, you cannot drop a table being published for replication; you must drop the article from the publication first.

 

 

Temporary Table

 

·          Temporary tables are defined just like regular tables, only they are stored in the tempdb database.

·          Temporary tables are often used to store temporary results, for use in queries, updates, and stored procedures.

·          They can be used to return or pass a set of results from a called procedure to a calling stored procedure.

·          Developers often use temporary tables to avoid Transact-SQL cursors.

 

·          Temporary tables are defined like regular tables, and can have indexes, constraints (but not foreign key constraints), and other related elements just like regular tables.

·          Temporary tables suffers due to the performance overhead that they sometimes incur, including excessive transaction logging, and locking of database objects.

·          There are two different temporary table types: local and global.

 

Local Temporary Table

 

·          Available for use by the current user connection that created them.

·          They are defined by adding a two pound (##) signs.

·          Multiple connections can create the same named temporary table for local temporary tables without encountering conflicts

·          The internal representation of the local table is given a unique name so as not to conflict with other temporary tables with the same name created by other connections in the tempdb.

·          Local temporary tables are dropped by using the DROP statement or are automatically removed when the user connection is closed.

 

CREATE TABLE #Authors

(iAuthorID int NOT NULL,

vchLastName varchar(100) NULL,

vchFirstName varchar(100) NULL)

 

 

Global Temporary Table

 

·          Once a connection creates a global temporary table, any user with access to the current database can access.

·          the tableThey are defined by adding a single pound (#) sign.

·          You cannot create simultaneous versions of a global temporary table, as this will generate a naming conflict.

·          Global temporary tables are removed if explicitly dropped by DROP TABLE or, automatically, after the last connection exits and the global temporary table is no longer referenced by any connections.

 

CREATE TABLE ##Authors

(iAuthorID int NOT NULL,

vchLastName varchar(100) NULL,

vchFirstName varchar(100) NULL)

 

 

Table Variable

 

Provide advantage over temporary table.

 

·          Can sometimes work more efficiently than a temporary table, as SQL Server automatically clears it after it has been used; you do not need to DROP a table variable explicitly.

·          Table variables can incur less locking and logging than a temporary table, because they are only available for the duration of the batch, function, or stored procedure where they are used.

·          Table variables can also reduce recompilations of a stored procedure, compared to the temporary table counterparts.

 

DECLARE @<variable_name> TABLE (<table_definition>)

 

DECLARE @Authors TABLE

(iAuthorId int NOT NULL ,

vchLastName varchar (50) NULL ,

vchFirstName varchar (50) NULL)

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead