While we are deleting the records form a big table which have billion or million of records. Instead of deleting the records at one time we are deleting the records in batch.
Below is the script to delete the records in a batch.
|
-- Process
10,000 rows at a time SET ROWCOUNT
10000; BEGIN DELETE FROM Table_name WHERE condtion; BREAK; -- No more rows left to delete END SET ROWCOUNT 0; |
Recently we have worked on a SSIS package. This package is used to extract the data from the source system and load it into the destination stage table. Using this package we are loading around 100 table. It does not have any complex mapping or transformation.
My problem is that we are doing
frequently changes in this package. Means while we are also doing same
modification on the existing table like altering, dropping or adding some
column in the table. Some time our some DFT got unmapped. It was very difficult
to find which DFT has unmapped.
The best way to run the package
and see the stage table data. Again if we will write the query like select *
from tablename it will return the all
data from this table or we need to write the Select top (n) * from tablename.
Like below
|
SELECT * From
Sales.SalesTaxRate SELECT * From
Sales.PersonCreditCard SELECT * From
Person.PersonPhone SELECT * From
Sales.SalesTerritory SELECT * From
Person.PhoneNumberType SELECT * From
Production.Product SELECT * From
Sales.SalesTerritoryHistory SELECT * From
Production.ScrapReason SELECT * From
HumanResources.Shift SELECT * From
Production.ProductCategory SELECT * From
Purchasing.ShipMethod |
Syntex : SET ROWCOUNT
See the example
SET ROWCOUNT 5
From each table it will return
the 5 records.
It limits the number of
rows returned by all subsequent SELECT statements within the current session by
using the keyword SET ROWCOUNT.
The SQL Server ROWCOUNT Set
Function causes the server to stop the query processing after the specified
numbers are returned. This setting is only applicable for the current session.
If we set ROWCOUNT 0 then it will return all rows.
We can set this using the Tools menu
bar see below
In General provide the number in
SET ROWCOUNT list.
Click ok. Now run the select
statement.
When we are writing the script and running this batch scrip. There are three steps to running a batch:
Ø Parse
Ø Compile
Ø Execute
Parse only is use to validate the syntax of the batch. If we
only want to check the syntax of the query, we can use the SET PARSEONLY ON
statement. When it is ON, SQL Server will only parse the statements (they will
not be compiled and executed). SQL Server will verify whether the code consists
of valid T-SQL statements.
When NOEXEC is ON, SQL Server will parse and compile the query without executing it. It means not only syntax checks will be performed, but also permissions and missing objects will be checked. Note: NOEXEC supports deferred name resolution, which means no error will be thrown if some objects referenced in the code are missing.
SET FMTONLY is used to set if the SQL Query should return the rows output to the client or only Meta Data (i.e. only columns and not the rows). If this statement is set with ON then we will get only the column headers and not the rows as output to your client. SET FMTONLY setting to ON and executes a SELECT statement. The setting causes the statement to return the column information only; no rows of data are returned.
Syntex
SET FMTONLY ON/OFF
See the example below
If we use SET FMTONLY ON; then we can see the column only.
SET FMTONLY ON;
If we set OFF the we can see the rows.
By Default it is OFF.
Real time example where we can use this setting
An application or ETL tool needs to know the structure of
the result set (column names & data types) of a complex query or stored
procedure without actually running it (to avoid modifying data or
long-running execution).
Below system function and sps is used to get the get the
meta data.
Ø
sys.sp_describe_first_result_set
Ø
sys.dm_exec_describe_first_result_set
Ø
sys.dm_exec_describe_first_result_set_for_object
Ø
sys.sp_describe_undeclared_parameters
sys.sp_describe_first_result_set this SP is used to
get the table meta data information see below.
|
EXEC
sp_describe_first_result_set @tsql = N'SELECT * FROM Production.Product' |
Deadlock is occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. So in this case, SQL Server intervenes and ends the deadlock by cancelling one of the transactions, so the other transaction can move forward.
Read more : Deadlock in
transaction in SQL Server
https://bageshkumarbagi-msbi.blogspot.com/2016/06/deadlock-in-transaction-in-sql-server.html
SQL Server automatically detects
deadlocks, chooses one of these sessions as a victim, and rolls back the
transaction, allowing the other session to proceed. SQL Server determines the
victim session based on its estimations and picks up the less costly transaction
to roll back.
We can set the deadlock using SET
DEADLOCK_PRIORITY. If a deadlock occurs and other sessions in the deadlock
chain have lower priorities, the current session will not be chosen as a
deadlock victim and will proceed. If the sessions have equal priorities, SQL
Server chooses the victim based on its estimations.
DEADLOCK_PRIORITY can be
· HIGH
· NORMAL
· LOW
Syntex
SET DEADLOCK_PRIORITY HIGH
See the example
Create two tables for demo
|
--Two global temp tables with sample data CREATE TABLE ##TableA ( ID INT IDENTITY, Val CHAR(1) ) GO INSERT INTO ##TableA (Val) VALUES ('A'), ('B') GO CREATE TABLE ##TableB( ID INT IDENTITY, Val CHAR(1) ) GO INSERT INTO ##TableB (Val) VALUES ('C'), ('D') GO |
Window 1
|
-- code for query window 1 BEGIN TRANSACTION SELECT @@SPID AS ProcessID --1 UPDATE ##TableA SET Val = 'E' WHERE ID = 1 ------------------------------------ WAITFOR DELAY '00:00:10' --3 UPDATE ##TableB SET Val= N'G' WHERE ID = 1 ------------------------------------------------------------- COMMIT SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1 SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1 |
|
-- code for query window 2 BEGIN TRANSACTION SELECT @@SPID AS ProcessID --2 UPDATE ##TableB SET Val = N'F' WHERE ID = 1 -------------------------------------- WAITFOR DELAY '00:00:10' --4 UPDATE ##TableA SET Val = N'H' WHERE ID = 1 COMMIT SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1 SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1 |
A deadlock occurs as both transactions try to update the same resource in the opposite order. The first transaction is chosen as a deadlock victim and rolled back in this case. The second one succeeded. As we haven’t set the deadlock priority, SQL Server decides which transaction to roll back.
Setting the priority
Window 1
|
-- code for query window 1 SET DEADLOCK_PRIORITY high GO BEGIN TRANSACTION SELECT @@SPID AS ProcessID --1 UPDATE ##TableA SET Val = 'E' WHERE ID = 1 ------------------------------------ WAITFOR DELAY '00:00:10' --3 UPDATE ##TableB SET Val= N'G' WHERE ID = 1 ------------------------------------------------------------- COMMIT SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1 SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1 |
Window 2
|
-- code for query window 2 SET DEADLOCK_PRIORITY low GO BEGIN TRANSACTION SELECT @@SPID AS ProcessID --2 UPDATE ##TableB SET Val = N'F' WHERE ID = 1 -------------------------------------- WAITFOR DELAY '00:00:10' --4 UPDATE ##TableA SET Val = N'H' WHERE ID = 1 COMMIT SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1 SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1 |
Where we can use this setting
· SET DEADLOCK_PRIORITY tells SQL Server which session to kill first if a deadlock occurs.
· If two sessions are deadlocked, the one with the lower priority will be chosen as the deadlock victim.
· we have two types of operations running on your database:
o Critical Transaction: Processes salary payments (must complete, never be killed if possible)
o Non-Critical Transaction: Generates a reporting summary (can be retried later if killed)
· We want SQL Server to always prefer killing the non-critical session if a deadlock happens.
The SYSDATETIMEOFFSET () function returns the current date and time of the system including the time zone offset from UTC. It’s useful when we need date-time with time zone. Let’s see the example where we can use this function. Suppose we need to create an application which is used by users in multiple time zones (e.g., India, USA, Europe).
Let see the demo
Creating a table
|
CREATE TABLE EmpLoginAudit ( Emp_Audit_ID BIGINT
IDENTITY, Emp_User_Name VARCHAR(100), Emp_Login_Time
DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET() ); |
|
INSERT INTO EmpLoginAudit (Emp_User_Name) VALUES ('Bagesh'); INSERT INTO EmpLoginAudit (Emp_User_Name) VALUES ('Rajesh'); |
See the data
|
SELECT Emp_User_Name, Emp_Login_Time, Emp_Login_Time AT TIME ZONE 'India
Standard Time' as India_Standard_Time,
Emp_Login_Time AT TIME ZONE 'UTC' AS
UTC_LoginTime, Emp_Login_Time AT TIME ZONE 'Nepal
Standard Time' AS Nepal_Standard_Time FROM
EmpLoginAudit; |
We will get the
list of time zone in the “sys.time_zone_info” Table in SQL server.
|
SELECT * FROM sys.time_zone_info; |
See the below
example
|
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time' AS
[India Standard Time --> My Local Time] GO SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time' AS [GMT
Standard Time] GO SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'US Mountain Standard Time'
AS [US Mountain Standard Time] GO SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Central Asia Standard Time'
AS [Central Asia Standard Time] GO SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Central Standard Time' AS [AUS Central Standard Time]  |
The set dateformat statement defines the order of datepart (Month, day , Year) when interpreting string date value in SQL Server. This setting is crucial for ensuring that date strings are interpreted correctly especially when dealing with different regional date format.
Syntex
Set DATEFORMAT {format}
*Format can be mdy,dmy,ydm,myd or
dym
The US English default dateformat
is MDY.
Available Date format
|
Format |
Order |
Example(’04-12-2024’) |
|
MDY |
Month-Day_Year |
April
12 2024(04-12-2024) |
|
DMY |
Day-month-year |
12 April 2024 (12-04-2024) |
|
YMD |
Year-
month- day |
2024
April 12 (2024-04-12) |
|
YDM |
Year-day-Month |
2024 12 April (2024-12-04) |
|
MYD |
Month-year-day |
April
2024, 12 (04-2024-12) |
|
DYM |
Day-year-month |
12th 2024 April (12-2024-04) |
|
set DATEFORMAT MDY SELECT CAST('04/05/2025' AS DATE); -- Interpreted as April 5, 2025 SET DATEFORMAT DMY; SELECT CAST('04/05/2025' AS DATE); -- Now it's 4 May 2025 |
Data Types for Date and Time
We have the following SQL convert
date and Time data types in SQL Server.
|
Date
type |
Format |
|
Time |
hh:mm:ss[.nnnnnnn] |
|
Date |
YYYY-MM-DD |
|
SmallDateTime |
YYYY-MM-DD
hh:mm:ss |
|
DateTime |
YYYY-MM-DD hh:mm:ss[.nnn] |
|
DateTime2 |
YYYY-MM-DD
hh:mm:ss[.nnnnnnn] |
|
DateTimeOffset |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm |
Ø SYSDATETIME
(): The SYSDATETIME () function returns the current system date and time with
precision to fractions of a second as a datetime2 data type.
Ø SYSDATETIMEOFFSET
(): The SYSDATETIMEOFFSET () function returns the current date and time of the
system including the time zone offset from UTC. It’s useful when you need
date-time with time zone awareness.
Ø GETUTCDATE
(): The
GETUTCDATE () function returns the current date and time in Coordinated
Universal Time (UTC).
Ø GETDATE
(): In SQL Server, GETDATE () is a built-in function that returns the current
system date and time based on the server's local time zone.
Ø CURRENT_TIMESTAMP:
In SQL Server, CURRENT_TIMESTAMP is a built-in ANSI SQL-compliant function that
returns the current date and time just like GETDATE ()
See the example
|
select SYSDATETIME() As
'SYSDATETIME' select SYSDATETIMEOFFSET() As
'SYSDATETIMEOFFSET' select GETUTCDATE() As
'GETUTCDATE' select GETDATE() AS
'GETDATE' select CURRENT_TIMESTAMP As
'CURRENT_TIMESTAMP' |
|
Function |
Type |
Includes
Time Zone |
Precision |
|
GETDATE() |
datetime |
❌ No |
Milliseconds |
|
SYSDATETIME() |
datetime2(7) |
❌ No |
Milliseconds |
|
SYSUTCDATETIME() |
datetime2(7) |
❌ (UTC only) |
Milliseconds |
|
SYSDATETIMEOFFSET() |
datetimeoffset |
✅ Yes |
100 nanoseconds |
|
CURRENT_TIMESTAMP |
datetime |
❌ No |
Milliseconds |
