Action in Sql server 2005

@@ERROR
Returns the error number for the last Transact-SQL statement executed.
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = "172-32-1176"

IF @@ERROR = 547
print "A check constraint violation occurred"


@@ROWCOUNT
Topic last updated -- July 2003

Returns the number of rows affected by the last statement.
UPDATE FMP_tbluser SET username = username

IF @@ROWCOUNT = 0
print 'Warning: No rows were updated'


IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.


IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.


SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.


IDENT_INCR
Returns the increment value (returned as numeric(@@MAXPRECISION,0)) specified during the creation of an identity column in a table or view that has an identity column.

Syntax
IDENT_INCR ( 'table_or_view' )

Arguments


SELECT TABLE_NAME, IDENT_INCR(TABLE_NAME) AS IDENT_INCR
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_INCR(TABLE_NAME) IS NOT NULL
---------------------------
Error
---------------------------
A Runtime Error has occurred.
Do you wish to Debug?

Line: 39
Error: 'leftSection.style' is null or not an object
---------------------------
Yes No
---------------------------

How to know the stored procedure names that are created and modified on spacified
---------------------------------------------------------------------------------date
SELECT name ,modify_date,create_date
FROM sys.objects
WHERE type = 'P'
AND modify_date > '08/27/2008'



Insert data of a table From one DataBase to other DataBase
Select * from SS_user from fmp.dbo.FMP_tbluser



Listing C: NOLOCK sample


CREATE PROCEDURE dbo.procSample
AS
SET NOCOUNT ON DECLARE @recCount int
SELECT @recCount = COUNT(au_id) FROM authors WITH (NOLOCK)
RETURN @recCount
SQL Server Interview Questions and Answers - Part 3
April 17, 2007 by pinaldave

What is a NOLOCK?
Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).



Rename a Table

sp_rename ExistingTableName, TableNewName;
e.g--->sp_rename 'tblmanaudit' ,'tbltriggeraction'

Rename a Column
sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'
sp_rename 'tblmanaudit.Action, 'Actionnew', 'COLUMN'

Delete a Column


ALTER TABLE TableName
DROP COLUMN ColumnName


Create Function

Syntax--->
CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN
RETURN Expression
END

Example
CREATE FUNCTION MyFunction()
RETURNS Decimal(8, 2)
AS
BEGIN
RETURN 22.66
END;
GO