Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘SQL Query’

ROWCOUNT in Sql server

Posted by Viral Sarvaiya on June 6, 2012


Causes SQL Server to stop processing the query after the specified number of rows are returned.

SET ROWCOUNT 10;
 SELECT * FROM Employee;

If Employee table has 100 reocrds, query return only 10 records. SET ROWCOUNT works as a TOP.

Syntex:
SET ROWCOUNT { number | @number_var }

where “number | @number_var” is Integer value rows to be processed before stopping the specific query.

To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

SET ROWCOUNT is not affect DELETE, INSERT and UPDATE statement so for that similar behavior use TOP in select Statement.

http://msdn.microsoft.com/en-us/library/ms188774.aspx

Advertisements

Posted in Sql Server | Tagged: , , , , , | Leave a Comment »

RESEED/Reset Table Identity Value in sql server

Posted by Viral Sarvaiya on January 6, 2012


DBCC CHECKIDENT can reset the identity value of the table,

For example if table has 500 reords and not we want to delete all records or want to start data from 600 then this command will help us.

DBCC CHECKIDENT (Table_name, reseed, 0)

OR

DBCC CHECKIDENT (Table_name, reseed, 600)

Most of our identity we start with 1 so must have to take care that identity set to 0.

If identity is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

Enjoy…

Posted in Sql Server | Tagged: , , , , , , , , , , , | Leave a Comment »

List of Table Constraints in sql server

Posted by Viral Sarvaiya on November 18, 2009


There five type of constrains provided by SQL Server. We shall see each one of then in details

1. Primary Key Constraint

A primary key is used to uniquely identify each row in a table. A primary key can consist of one or more then more columns in table. When they consist of more then one column they are called composite key. It ensures unique non-null key.


CREATE TABLE Student(
StudID INT PRIMARY KEY,
StudName VARCHAR(50)
)

or


ALTER TABLE Department ADD PRIMARY KEY (DeptID)

2. Foreign Key Constraint

A foreign key is a field in a table that matches the primary key column of another table. It establishes and enforces a link between data of two tables. It ensures that value points to a valid key.


CREATE TABLE Subjects(
SubID INT,
SubName VARCHAR(50),
StudID INT FOREIGN KEY REFERENCES Student(StudID),
TotalMarks INT,
)

or


ALTER TABLE Subjects(ADD CONSTRAINT StudID_Fk
FOREIGN KEY (StudID) REFERENCES Student(StudID)

3. Unique Constraint

It is similar to Primary Key Constraint. It ensures that every value in the column is unique value. It allows one NULL value.


CREATE TABLE Employee(
EmpID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DeptID INT,
Salary INT,
ProjectCode INT UNIQUE,
)

or


ALTER TABLE Employee ADD UNIQUE (ProjectCode)

4. Nullability Constraint

It defines whether a column will accept a NULL value or not. It is referred to as nullability of the column.


CREATE TABLE Employee(
EmpID INT,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50),
DeptID INT,
Salary INT,
ProjectCode INT
)

or


ALTER TABLE Employee ALTER COLUMN FirstName VARCHAR(50) NOT NULL;

5. Check Constraint

Check Constraint is extensively used to enforcement of general data-validation rules or simple business rules. Such as correct date range, max value that can be inserted into a table, etc.


CREATE TABLE Employee(
EmpID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DeptID INT,
Salary INT,
ProjectCode INT,
CONSTRAINT chk_sal CHECK (Salary BETWEEN 1000 AND 25000)
)

or


ALTER TABLE Employee
ADD CONSTRAINT chk_salary CHECK (Salary BETWEEN 1000 AND 25000)

Now in the below example show the all the Constraints in one table,


CREATE TABLE Employee(
EmpID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50),
DeptID INT FOREIGN KEY REFERENCES Department(DeptID),
Salary INT,
ProjectCode INT UNIQUE,
CONSTRAINT chk_salary CHECK (Salary BETWEEN 1000 AND 25000)
)

Posted in Sql Server | Tagged: , , , , , , , , , , , , , | 2 Comments »

 
%d bloggers like this: