Sql Server: Unique Constraint with Example

Unique Constraints to make sure that no duplicate values are entered in specific columns and UNIQUE constraints allow single value NULL in specific column. By default its create NonCluster index.

Add Unique constraint:
1- After Created Table
ALTER TABLE <>ADD CONSTRAINT <>UNIQUE( <>)
2- At the Time of table creation
A) in column level
create table <>
(
<> datatype ,
<> datatype unique
)
Example :
CREATE TABLE Emp ( Empid int primary key, Empcode int unique )
b) In Table Level
create table <> (
<> datatype , <> datatype
CONSTRAINT <> unique( <>)
)
Example :
CREATE TABLE Emp
(
Empid int primary key, Empcode int
CONSTRAINT UQ_INEmpCOde unique(Empcode)
)
Drop Constraints:
ALTER TABLE <> DROP CONSTRAINT < Constraint Name
Example:
Alter table emp
drop constraint UQ_INEmpCOde

Sql Server: Reset identity Value.

dbcc checkident([tableName],reseed/NORESEED,[Reset_Start_Vaue])

Parameter Description :

1. NORESEED Returns the current identity value and the current maximum value of the identity column, without reseeding. These values are usually (and should be) the same.
2. RESEED Changes the current identity value, using the maximum value in the identity column, if the current identity value is less than the maximum identity value stored in the identity column.
3. newreseedvalue Specifies the new seed value when reseeding. If the table is empty, the first identity value (after executing DBCC CHECKIDENT) will equal newreseedvalue. If the table contains data, the next identity value will equal newreseedvalue + the current increment value (the default is 1). This behavior is new to SQL Server 2005 (and remains in 2008). SQL Server 2000 always increments the seed value.
4. WITH NO INFOMSGS Suppresses all informational messages.
   
   Example:
   
   dbcc checkident('tblDetails',reseed,0)

Sql Server : Shrink Database

-- Sql Server 2000
1 -
dbcc shrinkfile(dbName_log,1)
Go
2 -
BACKUP LOG dbName WITH TRUNCATE_ONLY
Go
3 -
select * from sysfiles
OR
-- File sixe in MB.
SELECT name, size*8192/(1024*1024) AS sizeInMB , * FROM sysfiles


--- Sql Server 2008

Alter Database Test Set Recovery Simple
GO

Alter Database Test Set Recovery Full
GO

DBCC SHRINKFILE ('Test_log', 1)
GO
Note: Test is a database name.

Sql Server : Numeric Datatype

1. tinyint Allow Integer data from 0 – 255.
2. smallint Allows whole numbers between -32,768 and 32,767 or from -2^15 through 2^15 - 1
3. int Allows whole numbers between -2,147,483,648 and 2,147,483,647 or from -2^31 through 2^31 - 1
4. bigint Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 or from -2^63 through 2^63-1

Sql Server: Add & delete identity in existing table and column

************ Drop identity *******************
update syscolumns
set colstat =0
where id = object_id('tbl1') and name = 'rowid1'

Explanation:
tbl1 is a table name
rowid1 is a column name
if colstat = 0 means drop identity in a column

************ ADD identity IN EXISTING column *******************
Step 1:
update syscolumns
set colstat = 1
where id = object_id('tbl1') and name = 'rowid1'

Step 2: If data already exists in table and column is primary key use is

DBCC CHECKIDENT ('tbl1', RESEED,XX)

Explanation:
xx is a last column value + 1 as int.
tbl1 is a table name
rowid1 is a column name
if colstat = 1 means add identity in a column

Sql Server: set DEFAULT constraint value after table creation

DEFAULT CONSTRAINTS:
DEFAULT constraints allow you to specify a value that the database will use to populate fields that are left blank in the input source.

ALTER ATBLE [Table Name]
ADD CONSTRAINT [Constraint Name] DEFAULT [Default Value] FOR [Column Name]


Example:
Alter table tblabc
ADD CONSTRAINT c_default default getdate() for inDateTime