Code Simplified – Viral Sarvaiya

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

Posts Tagged ‘Sql Server’

« Previous Entries

SPACE() function of SQL Server

Posted by Viral Sarvaiya on February 16, 2012

Usually when we concatenate two fields like firstname and lastname we use ‘ ‘ between them like below

select firstname + ' ' + lastname from tblEmployee

And if we want more space then we use long space between them, but sql server provide function for print space between query.

Syntax:
SPACE(NO)

For example

select firstname + SPACE(2) + lastname from tblEmployee

It will return like below.

Viral  Sarvaiya

Thanks.

Enjoy..

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

SQL Reporting Services Error- Maximum request length exceeded

Posted by Viral Sarvaiya on January 16, 2012

Today i upload all report of my project and get error

Error : There was an Exception running the extension specified in the config file –> maximum request length exceeded.

The basic problem here, is that your posting an amount of data to a web app larger than it is configured to accept.  Hence, it is throwing an error, and simply saying “no!”

It’s an easy fix though! You’ve got to tweak the web.config for the web app, which in the case of reporting server, is usually somewhere like this:

C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLEXPRESS\Reporting Services\ReportServer

Find the web.config file for your reporting services instance, open it up, and track down the line that looks something like this

<httpRuntime executionTimeout = "9000" />

Now just add a max request length attribute in there to fix the problem, adjust your size as needed.  This is 5meg.

<httpRuntime executionTimeout = "9000" maxRequestLength="500000" />

And now you’ll need to restart IIS.  start->run->”iisreset”

Enjoy….

Posted in Report | Tagged: , , , , , , , , | 1 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 »

Ways to check whether a trigger exists in SQL Server

Posted by Viral Sarvaiya on December 13, 2011

Hi,

How to find there are trigger is exists in database or not?

there are 2 ways to find the list of the trigger in database


select * from dbo.sysobjects
where OBJECTPROPERTY(id, 'IsTrigger') = 1

or


select * from sys.triggers

and if you want to find trigger from particular table,


exec sp_helptrigger 'TableName'

or


select * from sys.triggers where name = 'TableName'

 

Enjoy…

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

SQL Server: Return Multiple Values from a Function

Posted by Viral Sarvaiya on February 10, 2011

hi

we know that function can return value and stored procedure cannot return value.

but function can return more than 1 value,

but the return type should be table.

so how can we do that..?

Click here.

thank you…

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

Remove duplicate records from the database table

Posted by Viral Sarvaiya on October 18, 2010

In my recent post http://viralsarvaiya.wordpress.com/2010/09/27/removing-duplicate-records-from-datasetdatatable/, I explain how to remove duplicate records form the dataset or datatable.

But if you want to delete duplicate records directly from the database then…..
First find the duplicate records,


SELECT MAX(ID) as ID, Name, Company FROM Employee GROUP BY Name, Company HAVING COUNT(*) > 1

This records give me the total duplicate records

Now to delete the duplicate records,


DELETE FROM Employee WHERE ID IN (SELECT MAX(ID) FROM Employee GROUP BY Name, Company HAVING COUNT(*) > 1)

Enjoy Coding…..

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

Difference between count(*) and count(column_name)

Posted by Viral Sarvaiya on September 13, 2010

what is count(*), count(column_name) and count(distinct column_name) ?

suppose we have employee table and having 100 records.

you all know what is count(*) in sql, this all function return same result if there is no record null in column.

select count(*) from employee
select count(emp_name) from employee
select count(distinct emp_name) from employee

that return the total employee from the employee table,

answer of this 3 query is 100.

but what happen if some records approx 20 of the emp_name is null?

now this 3 query give same answer?

no.

select count(*) from employee

return 100

select count(emp_name) from employee

return 80

select count(distinct emp_name) from employee

return 80

count(*) count the null records while count(column_name) omits the null value so it return 80(100 – 20) record.

hope you are clear with the count(*) count(column_name) and distinct column_name).

thank you.

Posted in Sql Server | Tagged: , , , , , | 1 Comment »

Paging with Stored Procedure

Posted by Viral Sarvaiya on September 6, 2010

Lets consider a simple table called employee( emp_id, name, salary). Now, suppose that we need to create a gridview with paging. One option is to bring all the data from database and do the paging in client-side and another one is to do selective fetch. In both cases we can use a stored procedure that takes some parameter and returns a resultset.


CREATE PROCEDURE GetEmployees
@Status int,
@StartIndex int,
@PageSize int
AS

WITH FilteredList( [emp_id],[name], [salary], [RowNumber])
AS
(
SELECT
[emp_id],
[name],
[salary],
ROW_NUMBER() OVER ( ORDER BY [ID] DESC) AS [RowNumber]

FROM
Employee
)

SELECT
*
FROM
FilteredList
WHERE
RowNumber BETWEEN (@StartIndex + 1) AND (@StartIndex + @PageSize)

Enjoy……

Posted in Sql Server | Tagged: , , | 1 Comment »

Retrieving Identities on set-based inserts

Posted by Viral Sarvaiya on January 20, 2010

A common situation where one is tempted to use cursors is this: You have a table with an identity column. You need to insert multiple rows to this table, then use the identities for further processing, such as inserting rows with those values into a child table.

for more details click Here.

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 »

« Previous Entries
 
Follow

Get every new post delivered to your Inbox.