Archive for the ‘Sql Server’ Category

How to find 2nd highest value in a table ?

March 21st, 2010 Jaffar

To Find the 2nd Maximum Of Mark in a Data Set

SELECT * FROM Student a WHERE 2=(SELECT COUNT(DISTINCT Mark)
FROM Student b WHERE a.Mark<=b.Mark)

To Find the 2nd Minimum Of Mark in a Data Set

SELECT * FROM Student a WHERE 2=(SELECT COUNT(DISTINCT Mark)
FROM Student b WHERE a.Mark>=b.Mark)

To Find nth Maximum and Minimum

SELECT * FROM Student a WHERE n=(SELECT COUNT(DISTINCT Mark)
FROM Student b WHERE a.Mark<=b.Mark)
SELECT * FROM Student a WHERE n=(SELECT COUNT(DISTINCT Mark)
FROM Student b WHERE a.Mark>=b.Mark)

replace n with the corresponding number

Popularity: 2% [?]

Posted in Sql Server, Tips And Tricks 2 Comments »

How to Get The List Of All Tables in a Database – Sql Server 2005

June 22nd, 2009 joy

We a can get the list of all tables of a database in different ways : -

1.

SELECT *
FROM sys.TABLES

2.

SELECT *
FROM sysobjects
WHERE TYPE='U'

3.

SELECT *
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Popularity: 2% [?]

Posted in Sql Server Tags: No Comments »

View Existing Triggers – Drop One or More Triggers in Sql Server 2005

June 19th, 2009 joy

To view the existing triggers in the database

SELECT * FROM sys.triggers
OR
SELECT * FROM sysobjects WHERE xtype='TR'

To view the existing triggers in a specified table

sp_helptrigger tablename

To drop a trigger 

DROP TRIGGER triggername

To drop more triggers at a time

DROP TRIGGER [trigger1],[trigger2],[trigger3]...[triggern]

Popularity: 12% [?]

Posted in Sql Server Tags: No Comments »

CASE Function in SQL Server 2005

March 29th, 2009 admin

Sql Case Function :

  • The CASE function allows you to evaluate a column value on a row against multiple criteria, where each criterion might return a different value
  • CASE is just a searched (or lookup) expression – you cannot RETURN from inside it – it’s kind of like IF() in Excel


Sql Case Function has two Types.

  • Simple Function
  • Searched Function

Simple Function :

Syntax :

case @Type
when expression then Result
when expression then Result
else Result

Example for Simple Function:

Set @state=’IND’

Select
state,
case @state
when ‘IND’ then ‘INDIA’
when ‘PAK’ then ‘Pakistan’
when ‘RSA’ then ‘South Africa’
when ‘Lanka’ then ‘Sri Lanka’
end as State Name
from State_name

Result:

State State_name
==== =========
IND INDIA

Searched Function :

Syntax :

case
when boolean_expression then Result
when boolean_expression then Result
else Result

Example for Searched Function:

Table :
Code State
=== ====
1 I
2 IND
3 PAK
4 RSA
5 SL

Select
state,
case
when state in (‘IND’,'I’) then ‘INDIA’
when state in (‘PAK’,'P’) then ‘Pakistan’
when state in (‘RSA’,'SA’) then ‘South Africa’
when state in (‘Lanka’,'SL’) then ‘Sri Lanka’
end as State Name
from State_name

Result :

State State Name
==== =========
I INDIA
IND INDIA
PAK Pakistan
RSA South Africa
SL Sri Lanka

Popularity: 44% [?]

Posted in Sql Server No Comments »

Create backup copy of table in SQLServer – (Select Into)

December 29th, 2008 admin

SELECT INTO statement can be used to create backup copy of a table.This sql statement selects records from one table according to the selection we make and inserts the result set into another new table with the table name we specify.

Syntax given below is used to make a exact copy of the table.That is it creates the table with the same no of columns and all records in the old table.

Syntax : Exact copy of old table

SELECT *
INTO new_table_name
FROM old_table_name

Example :

select * into employee_backup from employee

In this example a exact copy of the table employee is backed up as a new table employee_backup.

Syntax given below is used to make a new table with selected columns from old table.

Syntax : Selected columns from old table

SELECT column_name(s)
INTO new_table_name
FROM old_table_name

Example :

select employee_id,employee_name into employee_backup from employee

In this example a copy of the table employee with only columns “employee_id,employee_name” is backed up as a new table employee_backup.

Popularity: 12% [?]

Posted in Sql Server Tags: , No Comments »

Usage of Stored Procedures

December 16th, 2008 admin

Reduce network traffic: You have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.

Caching query plan: The first time the stored procedure is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.

Ability to use output parameters: If you send inline SQL that returns one row, you can only get back a recordset. With stored procedures you can get them back as output parameters, which is considerably faster.

Permissions: When you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a stored procedure

Separation of logic: Remove the SQL-generating code and segregate it in the database.

Ability to edit without recompiling: This can be controversial. You can edit the SQL in a sproc without having to recompile the application.

Find where a table is used: With stored procedures, if you want to find all SQL statements referencing a particular table, you can export the stored procedure code and search it. This is much easier than trying to find it in code.

Optimization: It’s easier for a DBA to optimize the SQL and tune the database when stored procedures are used. It’s easier to find missing indexes and such.

SQL injection attacks: Properly written inline SQL can defend against attacks, but stored procedures are better for this protection.

Popularity: 1% [?]

Posted in Sql Server IQ, What-is Tags: No Comments »

Using OVER() with Aggregate Functions in Sql Server 2005

December 2nd, 2008 admin

1.you can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function.

2.SUM(..) OVER(..) is most useful for calculating a Total or percentage of a total for each row

3.SUM() function works with any of the other aggregate functions as well, such as MIN() or AVG()

Partition by

Divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

select
StudentId,
StudentName,
Subject,
Mark,
sum(Mark) OVER (Partition by StudentId) as Total
from
Student

Output
=====
StudentId StudentName Subject Mark Total
======= ========== ===== ==== =====
1 Raja Physics 80 240
1 Raja Maths 90 240
1 Raja Biology 70 240
2 Sam Physics 90 150
2 Sam Maths 60 150

Popularity: 4% [?]

Posted in Sql Server 1 Comment »

Create Database,Table and Procedure in Sql Server – Beginners

November 25th, 2008 admin

Database Creation :

CREATE DATABASE EMPLOYEE

Table Creation :

CREATE TABLE EMPINFO
(
ROWID INT IDENTITY(1,1) NOT NULL,
EMPID INT NOT NULL,
EMPNAME VARCHAR(50) NOT NULL,
EMPDEPT VARCHAR(3) NOT NULL,
EMPSAL INT NOT NULL,
EMPADDRESS VARCHAR(100) NOT NULL,
ACTIVE CHAR(1) NOT NULL DEFAULT ‘T’
)

Procedure Creation :

CREATE PROC EMPINFO_ADD_EDIT
(
@iEMPID INT,
@vEMPNAME VARCHAR(50),
@vEMPDEPT VARCHAR(3),
@iEMPSAL INT ,
@vEMPADDRESS VARCHAR(100),
@cACTIVE CHAR(1)
)
AS
DECLARE @CNT INT;
BEGIN
SET @CNT = 0;
SELECT @CNT = COUNT(*) FROM EMPINFO WHERE EMPID = @iEMPID;
IF(@CNT = 0)
BEGIN

INSERT INTO EMPINFO(EMPID,EMPNAME,EMPDEPT,EMPSAL,EMPADDRESS,ACTIVE)VALUES @iEMPID,@vEMPNAME,@vEMPDEPT,@iEMPSAL,@vEMPADDRESS,@cACTIVE)

END
ELSE
BEGIN

UPDATE EMPINFO SET EMPNAME = @vEMPNAME,EMPDEPT = @vEMPDEPT,EMPSAL = @iEMPSAL, EMPADDRESS = @vEMPADDRESS,ACTIVE = @cACTIVE WHERE EMPID = @iEMPID

END

END

Popularity: 1% [?]

Posted in Sql Server Tags: No Comments »

Check Sql Server database is Case Sensitive or Insensitive (Collation)

November 21st, 2008 admin

If the collation property of a data base is

1.Latin1_General_BIN its case-sensitive
2.SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AI its case-insensitive

There are two ways to find out the SQL Server Database Collation

1. Using DATABASEPROPERTYEX

DATABASEPROPERTYEX gets the current setting of the specified database option or property for the specified database.

Syntax :-
DATABASEPROPERTYEX ( database , property )

SELECT DATABASEPROPERTYEX(‘testdb’, ‘Collation’)

This returns the testdb Database Collation property value say if this testdb is a case-sensitive database then it returns

Latin1_General_BIN

2.Using Sql Server Management Studio

Step 1 : Open Sql Server Management Studio.

Step 2 : Right Click on database and click its properties


After clicking the properties you get a database properties modal box such as

The red box shows the collation property value of that database.

Popularity: 5% [?]

Posted in Sql Server No Comments »

SQL Server Connection Using Integrated Security

November 14th, 2008 admin

When we are connecting sql server using windows authentication then we do not need to give the user name and pwd in the connection string. We have to provide the Integrated Security property to true.

SqlConnection con = new SqlConnection(“Server=servername;Integrated Security=true; Database=emp”);

Note : If your sql server is of windows authentication and if you dont provide Integrated Security you may get an error such as follows
Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.

Popularity: 1% [?]

Posted in Sql Server No Comments »
RSS FeedSubscribe to our RSS feed!
Follow us on Twitter!Follow us on Twitter!

  • Sponsor

Designed by: Business Web Hosting | Thanks to Buy Icons, travel tips and Used Cars