How can we delete or update first 3 records in a table ?

By using TOP clause we can delete or update the first 3 records in a table.

Example :

CREATE TABLE Top_Clause
  (
     id     INT IDENTITY(1, 1),
     name   VARCHAR(10),
     gender CHAR(1) DEFAULT 'M'
  )

INSERT INTO Top_Clause
SELECT 'Satya',
       'M'
UNION ALL
SELECT 'Naga',
       'M'
UNION ALL
SELECT 'Venkat',
       'M'
UNION ALL
SELECT 'Krishna',
       'M'
UNION ALL
SELECT 'Reeta',
       'F'
UNION ALL
SELECT 'Meeta',
       'F'
UNION ALL
SELECT 'Geetha',
       'F'
UNION ALL
SELECT 'Heetha',
       'F'

-- Fetching 3 records using TOP clause
SELECT TOP 3 *
FROM   Top_Clause

-- Updating top 3 records using Top clause
UPDATE TOP (3) Top_Clause
SET    name = name + '3'

-- Deleting top 3 records using Top clause
DELETE TOP (3) FROM Top_Clause

How to identify which code is executing in a Sessionid or SPID?

DBCC inputbuffer(SPID)

How to find the currently active locks in a database?

SELECT Object_name(resource_associated_entity_id),
       *
FROM   sys.dm_tran_locks
WHERE  resource_database_id = Db_id()

Current running queries in database :

SELECT DISTINCT [Spid] = session_Id,
                [Database] = Db_name(sp.dbid),
                [User] = nt_username,
                [Status] = er.status,
                [Wait] = wait_type,
                [Individual Query] = Substring (qt.text, er.statement_start_offset / 2,
                ( CASE
                  WHEN er.statement_end_offset = -1
                  THEN Len(CONVERT(NVARCHAR(MAX), qt.text)) * 2

                  ELSE er.statement_end_offset
                  END - er.statement_start_offset ) / 2),
                [Parent Query] = qt.text,
                Program = program_name,
                Hostname,
                nt_domain,
                start_time
FROM   sys.dm_exec_requests er
       INNER JOIN sys.sysprocesses sp
               ON er.session_id = sp.spid
       CROSS APPLY sys.Dm_exec_sql_text(er.sql_handle)AS qt
WHERE  session_Id NOT IN ( @@SPID )
ORDER  BY 1, 2

How to find the IP address and Machine name of SQL Server ?

-- IP address and Machine name of SQL Server
SELECT Connectionproperty('local_net_address')       AS "IP Address",
       Serverproperty('ComputerNamePhysicalNetBIOS') "Machine Name"

-- Client and Server Ip Address and Mahine name
SELECT Serverproperty('ComputerNamePhysicalNetBIOS') "Machine Name",
       local_net_address                             AS "Server IP Address",
       client_net_address                            AS "Client IP Address"
FROM   SYS.DM_EXEC_CONNECTIONS
WHERE  SESSION_ID = @@SPID

How to find the Number of records in a Table ?

-- To fetch the number of records in a table
SELECT ss.name            AS [schema],
       Object_name(si.id) AS TableName,
       Max(si.rowcnt)     AS EstimatedRows
FROM   dbo.sysindexes si
       INNER JOIN sys.tables st
               ON st.object_id = si.id
       INNER JOIN sys.schemas ss
               ON ss.schema_id = st.schema_id
WHERE  indid < 2 -- clustered index or table entry
       AND Objectproperty(si.id, 'IsUserTable') = 1
       AND Objectproperty(si.id, 'IsMSShipped') = 0
       AND Permissions(id) != 0 -- User must have at least some privileges on the object.
       AND Object_name(si.id) LIKE '%Table Name%'
GROUP  BY Object_name(si.id),
          ss.name

ORDER  BY 2

How to fetch the database restore Information ?

-- DB last restore Date

WITH RestoreInformation AS
(
SELECT
    d.name "Database Name",
    d.create_date ,
    d.compatibility_level ,
    d.collation_name ,
    rh.*,
     ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY rh.restore_date DESC)  "RowNumber"
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = d.Name where  d.name ='Database Name'
)
SELECT *
FROM RestoreInformation
WHERE RowNumber = 1


--To fetch the DB restore History
SELECT
    d.name "Database Name",
    d.create_date ,
    d.compatibility_level ,
    d.collation_name ,
    rh.*,
     ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY rh.restore_date DESC)  "RowNumber"
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = d.Name
where d.name ='Database Name' order by RowNumber asc

What is the difference between @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT ?

@@IDENTITY ==> Is a system function that returns the last-inserted identity value for any table in the current session, across all scopes.

SCOPE_IDENTITY() ==> Returns the last identity value inserted into an identity column for any table in the current session and in the same scope.

IDENT_CURRENT ==> Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

GO

-- create a main table

CREATE TABLE test_main(id INT IDENTITY (1,1), name VARCHAR(10))

GO

-- insert some records

INSERT INTO test_main(name) VALUES('A'),('B'),('C')

GO

-- create child table

CREATE TABLE test_child(id INT IDENTITY (1,1), name VARCHAR(10))

GO

-- create a trigger on main table to insert the reccords into child table

CREATE TRIGGER test_trigger ON test_main FOR INSERT

AS

BEGIN

INSERT INTO test_child SELECT name FROM INSERTED

END

GO

-- check the identity on main table

SELECT * FROM test_main

-- result

1 A

2 B

3   C

GO

-- now insert records into main table and check the identity

INSERT INTO test_main(name) SELECT 'D'

-- now check the below result

select @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT('test_main')

-- result

1 4 4

Useful System Procedures :

sp_help ==> To get a table / object definition

Example :   EXEC sp_help object_name

sp_helptext ==> To get the procedure / view text.

Example :  EXEC sp_helptext Proc_name

sp_helpindex ==> To get the index information on a table

Example :  EXEC sp_helpindex Proc_name

sp_helpconstraint ==> To get the constarints on a table

Example :  EXEC sp_helpconstraint Proc_name

sp_depends ==> To get the dependencies on table / procedure / view.

Example :  EXEC sp_depends object_name

sp_spaceused ==> To get the size of a table or database, if you didn`t pass any input it will show the size of the current database.

Example :  EXEC sp_spaceused table_name

sp_helptrigger  ==> To get the triggers on a table

Example :  EXEC sp_helptrigger table_name

sp_rename ==> To rename database objects and columns.

Example : EXEC sp_rename 'table_name' , 'new_table_name'
     EXEC sp_rename 'table_name.column_name' , 'new_column_name'

sp_tables ==> To get the tables with the name like a given syntax / pattern.

Example : EXEC sp_tables '%user%'

sp_stored_procedures ==> To get the stored procedure names with the name like a given syntax / pattern.

Example : EXEC sp_stored_procedures '%user%'