SQL Server 2005 : Split Function

CREATE FUNCTION dbo.Splitter(@strList VARCHAR(8000),@strSeperator VARCHAR(10))
Returns @tblSplit TABLE (splitValue VARCHAR(255))
AS
BEGIN
DECLARE @tmp_FnCommaSplitter TABLE (splitValue VARCHAR(255))
DECLARE @strTmp VARCHAR(5000)
DECLARE @pos INT
DECLARE @lenSeperator INT
SELECT @lenSeperator = LEN(@strSeperator)
SELECT @strTmp = @strList
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
WHILE (@pos <> 0)
BEGIN
IF SUBSTRING(@strTmp,1,@pos-1) <> ''
INSERT @tmp_FnCommaSplitter VALUES (SUBSTRING(@strTmp,1,@pos-1))

SELECT @strTmp = SUBSTRING(@strTmp,@pos+@lenSeperator,5000)
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
END
IF @strTmp <> ''
INSERT @tmp_FnCommaSplitter VALUES (LTRIM(RTRIM(@strTmp)))
INSERT @tblSplit SELECT * FROM @tmp_FnCommaSplitter
RETURN
END

SQL Datechecking

DECLARE @datestring varchar(8)
SET @datestring = '12/21/98'
SELECT ISDATE(@datestring)

Procedure to get the record Count instead of using count(*)

sp_spaceused '' for getting the counts.
This will be faster instead of using select count(*)

Cleaning the Transaction Logs in SQL Server2005

To Back up the transaction log file :
BACKUP LOG TO DISK = ''
Eg.
BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

To Shrink the transaction log file:

DBCC SHRINKFILE (, ) WITH NO_INFOMSGS

How to clean the transaction Log in SQL Server

use master
go
dump transaction with no_log
go
use
go
DBCC SHRINKFILE (, 100) -- where 100 is the size you may want to shrink it to in MB, change it to your needs
go
-- then you can call to check that all went fine
dbcc checkdb()

(or)

To Truncate the log file:

Backup the database
Detach the database, either by using Enterprise Manager or by executing : *Sp_DetachDB [DBName]*
Delete the transaction log file. (or rename the file, just in case)
Re-attach the database again using: *Sp_AttachDB [DBName]*
When the database is attached, a new transaction log file is created.
To Shrink the log file:

Backup log [DBName] with No_Log
Shrink the database by either:

Using Enterprise manager :- Right click on the database, All tasks, Shrink database, Files, Select log file, OK.

Using T-SQL :- *Dbcc Shrinkfile ([Log_Logical_Name])*

You can find the logical name of the log file by running sp_helpdb or by looking in the properties of the database in Enterprise Manager.

Replace the First Charater in SQL

Query for replacing the first character only.

--select replace(left('Mahesh',1),left('Mahesh',1),'H') + right('Mahesh',len('Mahesh') - 1)

SQL Stored Procedure,Triggers, Cursors.

Stored Procedure:
Pre-Compiled set of SQL Statments..
Increases Performance
Reduces Network Traffic

Syntax:
Create Procedure Proc_name(param list)
As
Begin
// SQL Statements
End


Trigger:
Equivalent to event raising, ie, doing certain action based on events like insert, update...

syntax:
create trigger trg_name
As
begin
// sql statements
end

Cursor:
cursors are used when row by row processing is needed.
syntax
DECLARE cur_name CURSOR
FOR
SELECT username
FROM table

OPEN cur_name

FETCH NEXT FROM cur_name
INTO @usrID

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM cur_nmae
INTO @usrID

END

CLOSE cur_name
DEALLOCATE cur_name

What's the difference between char,nvarchar,ntext and nvarchar(max) ? (SQL2005)

see the below link:

http://geekswithblogs.net/claeyskurt/archive/2006/02/04/68161.aspx

SQL Server : Sum of 2 querries which has already sum function

There are 2 ways for summing the 2 sub querries:
Method : 1
select
(select SUM(substring(SP_Product, 5, 2) * SP_Opening) AS Muliplied from tblStock_Packed_Factory
where SP_Oil = 'Cocounut'and SP_Product like '%kg' and Len(SP_Product)=8 )
+
(select SUM(substring (SP_Product, 5, 3) * SP_Opening) AS Muliplied from tblStock_Packed_Factory
where SP_Oil = 'Cocounut'and SP_Product like '%kg' and Len(SP_Product)=9 ) AS Muliplied
------------
Method: 2
select Sum(tmp.Muliplied) As Muliplied
From (
select SUM(substring (SP_Product, 5, 2) * SP_Opening) AS Muliplied from tblStock_Packed_Factory
where SP_Oil = 'Cocounut'and SP_Product like '%kg' and Len(SP_Product)=8
union
select SUM(substring (SP_Product, 5, 3) * SP_Opening) AS Muliplied from tblStock_Packed_Factory
where SP_Oil = 'Cocounut'and SP_Product like '%kg' and Len(SP_Product)=9 ) as tmp

Duplicate Records Count

SELECT empname,COUNT(empname) FROM test2 GROUP BY empname
HAVING COUNT(empname) > 1
SELECT COUNT(*),empname FROM test2 GROUP BY empname HAVING COUNT(*)>1

Deleting Duplicate Records in the Table

Table Name : Test2
Fields : empid,empname

Query to Delete the Duplicate values and keeping one value in the table.

set rowcount 1
select 1
while @@rowcount > 0
delete test2
where 1 < (select count(*) from test2 a2 where test2.empid = a2.empid)
set rowcount 0

SQL-Server: Finding the Nth Highest & Nth Lowest in the SQL:

In the below way we can display any record, it can be fifth or 12th record from top. If we change the order by command to start in ascending order by changing the DESC to ASC ( or removing it , by default it is ASC ) we can get the lowest to highest and get the records from lowest ( last ) mark.

SELECT * FROM `student` where class='Six' ORDER BY mark desc LIMIT 0,1

SQL- Server: Case Statement

Sample for Case Statment:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

SQL-Server :Example of COALESCE

To bring the Multiple Column in to single column using comma separator:

declare @var varchar(800)
select @var=COALESCE(@var +',', '')+ CAST( AS varchar(250)) from
print @var

SQL-Server : How to Kill the Database Users?

To view the Users who are all using the Database:
--------------------------------------------------
sp_who2

To Kill the User:
------------------

kill 'SPID'
eg: kill 58

SQL-Server : To view the Column Name,type,size in the Table

To view the Columns in the table called "Activity"
-----------------------------------------------------
Type: 1
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Activity'

Type: 2
select fname=col.name, dtype=type.name, col.length, col.status from syscolumns col
inner join sysobjects obj
on col.id = obj.id
inner join systypes type
on col.xtype = type.xtype
where obj.name= 'Activity'
and type.xtype = type.xusertype order by colid



To Get the total number of Columns in the Table:
---------------------------------------------------
SELECT Count(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Activity'

Keys Concept in DBMS.

1) PRIMARY KEY:-A primary key is a field that uniquely identifies each record in a table. As it uniquely identify each entity, it cannot contain null value and duplicate value.eg:-Consider the customer table, which has field :customer_number, customer_socialsecurity_number, and customer_address.here customer_number of each entity in customer table is distinct so customer-number can be a primary key of customer-table.
2) SUPER KEY :- If we add additional attributes to a primary key, the resulting combination would still uniquely identify an instance of the entity set. Such augmented keys are called superkey.A primary key is therefore a minimum superkey.
3) CANDIDATE KEY:-A nominee's for primary key field are know as candidate key.eg:-From above example of customer table, customer_socialsecurity_number is candidate key as it has all characteristics of primary key.
4) ALTERNATE KEY:-A candidate key that is not the primary key is called an Alternate key.eg:- In above example, customer_socialsecurity_number is a candidate key but not a primary key so it can be considered as alternate key.
5) COMPOSITE KEY:- Creating more than one primary key are jointly known as composite key.eg:-In above example, if customer_number and customer_socialsecurity_number are made primary key than they will be jointly known as composite key.
6) FOREIGN KEY:- Foreign key is a primary key of master table, which is reference in the current table, so it is known as foreign key in the current table. A foreign key is one or more columns whose value must exist in the primary key of another table.eg:-Consider two tables emp(contains employees description) and emp_edu(contains details of employee's education), so emp_id which is primary key in emp table will be referred as foreign key in emp_edu table.

Define candidate key, alternate key, composite key?

A candidate key is one that can identify each row of a table uniquely.Generally a candidate key becomes the primary key of the table. If thetable has more than one candidate key, one of them will become theprimary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What is Canditate Key?

A table which is having more that one combination of column that could uniqly identify the rows in a table. Each combination is a canditate key.
Eg. In the supplier table SupplierID, Supplier name are the canditate key but we can pick up only the supplierID as a primary key.