Difference Between Sql Server CHAR, VARCHAR and NVARCHAR Data Type
April 8, 2013 Category: SQL
Comments:
Introduction to Web Service
April 6, 2013 Category: WEB SERVICE
Comments:
System Function in SQL-Server
April 5, 2013 Category: SQL
Comments:
New Features in SQL Server 2008
April 5, 2013 Category: SQL
Comments:
Difference between TRUNCATE, DELETE in SQL
April 5, 2013 Category: SQL
Comments:
DELETE
|
TRUANCATE
|
DELETE is DML Command.
|
TRUNCATE is DDL Command.
|
DELETE Command is slow
|
TRUNCATE is Faster
|
The DELETE command is used to remove specified rows from a table.
|
TRUNCATE removes all rows from a table.
|
DELETE Can be used with or without a WHERE clause
|
No WHERE clause is used
|
DELETE operation you need to COMMIT or ROLLBACK
|
The operation cannot be rolled back
|
DELETE does not reset identity of the table.
|
TRUNCATE Resets identity of the table.
|
Trigger get fired
|
Trigger doesn’t get fired
|
e.g
e.g
Difference between stored procedure and functions in SQL
April 5, 2013 Category: SQL
Comments:
Store Procedure | Function |
S.P need not be return a value | Functions MUST return a value |
S.P can be called independently usingexec keyword whereas | Function is called using SELECTstatements.
|
S.P can be used for performing business logic
|
Functions are used forcomputations |
Can be used EXEC inside an S.P |
EXEC command can’t be used inside a Function |
S.P takes IN and OUT parameters. | Function parameters are always IN, no OUT is possible
|
Transaction related statement can be handled in S.P | Can’t be handled in function.
|
XML parameters passed in S.P | Can’t pass XML Parameters |
S.P can affect the state of the database by using insert, delete, update and create operations. | Functions cannot affect the state of the database which means we cannot perform insert, delete, update |
Parsed and Compiled during design time itself | Compiled and executed during run time |
Combine multiple rows into a comma separated Column in SQL
April 5, 2013 Category: SQL
Comments:
If we required comma separated values form one to column in multiple rows.
Example
CREATE TABLE EMP
(
EID INT IDENTITY,
ENAME VARCHAR(100),
DEPT VARCHAR(100)
)
INSERT INTO EMP(ENAME,DEPT)
VALUES(‘ABC’,'SALE’),
(‘PQR’,'IT’),
(‘XYZ’,'HR’),
(‘MNL’,'ADMIN’)
DECLARE @DEPT VARCHAR(255)
SELECT @DEPT=COALESCE(@DEPT + ‘, ‘, ”) + CAST(DEPT AS VARCHAR(100))
FROM EMP
SELECT @DEPT as ‘Department’
Difference between @@IDENTITY,SCOPE_IDENTITY() and IDENT_CURRENT() in SQL
April 5, 2013 Category: SQL
Comments:
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
SELECT @@IDENTITY
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
SCOPE_IDENTITY() will return the last IDENTITY value produced on a connection and by a statement in the same scope, without based on the table that produced the value. So we can say that this function is some identical to @@IDENTITY with one exception. like @@IDENTITY will return the last identity value created in the current session, but it will also limit it to your current scope as well . So that means it will return identity value inserted in Table.
SELECT IDENT_CURRENT(‘tablename’)
IDENT_CURRENT will reutrn returns the last IDENTITY value produced in a table, Without based on the connection that created the value, and Without based on the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session., So it will retrieve the last generated table identity value.