SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.
-Get Currtent Date-
GETDATE()
-GET UTC Date
GETUTCDATE()
-Get UTC DATE WITHOUT TIME
cast(floor(cast(GETDATE() as float)) as datetime)
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
-Return a Parameter back-
@bnGroupId bigint OUT,
SET @bnGroupId = SCOPE_IDENTITY()
-SQL SURFACE to enable remote logging-
1) Select Start Menu>SQL Server 2005>Configuration Tools>Surface area Configuration
2) Click Serface area Connection
3) Select Using Both TCP/IP and named piped
Network Configuration
1)Select Start Menu>SQL Server 2005>Configuration Tools>Server Configuration Manager
2)Click Nenwork Configuration
3)Select Protocol for SQL Server
4)Enable TCP/IP
How to test Romtote Logging to SQL
SQLCMD -E -S CT-MCDDEVSQL\SQLEXPRESSCLIENT,2301
-The SQL EXIST Statement-
IF EXISTS ( SELECT * FROM [tblData]
WHERE MessageId]=@MessageId
AND [RecipId]=@vcRecipId
AND [TypeId]= @TypeId
AND @vcStatus = @vcPendingStatus
)
BEGIN
--query
END
-How to Catch Sql Errors-
DECLARE @vcErrorNumber varchar(10)-- hold the error number
BEGIN TRY
INSERT INTO [tblDestinationInfoClientInfo]/--------/
END TRY
BEGIN CATCH
SET @vcErrorNumber = ERROR_NUMBER()
-- primary key vialation error number
IF (@vcErrorNumber = '2627')
BEGIN
/--------------/
END
END CATCH
-Define Transactions-
BEGIN TRAN
COMMIT TRAN
ROLLBACK TRAN
-How to Retrieve All Specific Table Columns from SysTable-
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp')
--sysobjects table contains all tables information
SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp'
In SQL Server the is a unique identification number for every tables. So we retrieve specified User Defiened table ID.
--syscolumns table contains all tables column information
We can retrieve specific table Columns by passing specific table Id.
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = /*Table Id*/
-Define a Cursor [Column Cursor]-
DECLARE @fieldname varchar(200)
DECLARE temptable_fields_cursor CURSOR FOR
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblClientRelationships')
OPEN temptable_fields_cursor
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname
WHILE @@FETCH_STATUS = 0
BEGIN
----implementation----------
--------------
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname
END --End While--
CLOSE temptable_fields_cursor
DEALLOCATE temptable_fields_cursor
-Create & Execute a Dynamic Query-
SET @SqlQuery = 'Select * From Emp'
EXEC (@SqlQuery )
-Define SQL Paging for a .NET GridView.-
declare @nPageNum INT
declare @nPageSize INT
set @nPageNum=1
set @nPageSize=10;
Note:-
--Following Select statement result will be stored in to 'Search' variable and we should need to retrieve data from 'Search' in the next statement.
--We can query 'Search' only one time.
WITH Search AS
(
SELECT rownum, Records.[Name] as PersonName , Records.[Age] as PersonAge ,Records.[ID], Records.[Bdate]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID,Bdate) AS rownum ,
dbo.Person.[Name] , dbo.Person.[Age] ,dbo.Person.[ID]
,dbo.Person.[Bdate] FROM dbo.Person)
AS Records
)
SELECT PersonName,PersonAge,Search.[ID],Search.[Bdate],(SELECT COUNT(*) FROM Search) AS RecordCount
FROM Search
WHERE rownum BETWEEN (@nPageNum-1)*@nPageSize+1 AND @nPageNum*@nPageSize
ORDER BY Search.[Bdate]
-SQL SERVER – 2005 Locking-
ROWLOCK
Use row-level locks when reading or modifying data.
PAGLOCK
Use page-level locks when reading or modifying data.
TABLOCK
Use a table lock when reading or modifying data.
DBLOCK
Use a database lock when reading or modifying data.
UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.
XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is
no longer required.
NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.
Examples:
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000
UPDATE Products (WITH NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'
http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/
View SQL Server Connected Users:-
sp_who or sp_who2
Get SQL users that are connected and how many sessions they have
SELECT login_name, count(session_id) as session_count
FROM sys.dm_exec_sessions
GROUP BY login_name
