I imagine you are just looking for simple syntax in order to reseed the identity column of a table you just deleted from. Here is the quick version: DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) And here is an extended example: — populate a table with identity SELECT ID = IDENTITY(int,1,1) ,name INTO ##reseed_example FROM dbo.sysobjects — delete [...]
Archive of posts filed under the DDL category.
Indexed Views
To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved SELECT statement, nothing else. It is not explicitly compiled, nor does it contain any data. When you select from a view, it goes to [...]
Alter Schema – Move object to another schema
As easy as this syntax is, I had to keep looking it up for about a year. I suppose that’s why you are here. Well, here it is: ALTER SCHEMA newschema TRANSFER oldschema.Table This will transfer the table defined under “oldschema” and transfer it to “newschema”. Popular search terms:ALTER SCHEMA TRANSFERalter schemasql server ALTER SCHEMAsql [...]
Add Column Constraint
To add a constraint to an existing table use the alter table statement with the add constraint command. There are four different types of constraints: Primary Key Constraints – Enforces unique values for specified column, can be referenced. Foreign Key Constraints – Enforces a reference to a primary key Unique Constraints – Ensures unique values [...]
Add Column Default Value
In SQL Server there are two ways to add a column with a default value. Add Default Value to Existing Column — Add default to existing column DateOfHire: ALTER TABLE [dbo].[Employees] ADD DEFAULT (getdate()) FOR [DateOfHire] — Add default value to existing column IsTerminated ALTER TABLE [dbo].[Employees] ADD DEFAULT ((0)) FOR [IsTerminated] Add New Column [...]
Add Foreign Key
To Add a foreign key to a column in an existing table, use ALTER TABLE ADD CONSTRAINT ALTER TABLE dbo.President_Lookup ADD CONSTRAINT fk_PresidentID FOREIGN KEY (PresidentID) REFERENCES dbo.Presidents (PresidentID) Popular search terms:how to set foreign key in sql server 2008add foreign key constraint in sql server 2008add foreign key sql server 2008how to add foreign [...]
Add Constraint
To add a constraint to an existing table use the alter table statement with the add constraint command. There are four different types of constraints: Primary Key Constraints – Enforces unique values for specified column, can be referenced. Foreign Key Constraints – Enforces a reference to a primary key Unique Constraints – Ensures unique values [...]
Create Index
To add an index in SQL Server use the CREATE INDEX statements. When adding indexes remember there can only be one clustered index per table. The main options when creating an index are clutered or nonclustered or unique vs non unique. Using SQL 2005+ you can also specify columns to include at the leaf level [...]
Add Primary Key
In order to add a primary key to an existing table we need to use the Alter Table command. Add a primary key for a new column ALTER TABLE dbo.Presidents ADD PresidentID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_PresidentID PRIMARY KEY NONCLUSTERED Note however that the ordering of the identity field will not be predictable for [...]
Create Table
Below is the basic syntax for the create table script: CREATE TABLE dbo.Employees ( EmployeeID int IDENTITY (1,1) NOT NULL CONSTRAINT PK_EmployeeID PRIMARY KEY NONCLUSTERED ,FirstName varchar(50) NULL ,LastName varchar(50) NULL ,SSN varchar(9) NULL CONSTRAINT ssn_unique CHECK (SSN like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘) UNIQUE ,IsTerminated bit NOT NULL DEFAULT 0 ,DateAdded datetime DEFAULT GETDATE() ,Comments varchar(255) SPARSE NULL [...]
