SQL

How to get the Previous and Next Row Data

Posted on Updated on

If you’ve ever wrote reports that requires comparing numbers from previous weeks, months, quarter, and year SQL 2012 and later versions will solve your issue.

In this example, I have a table call HBClaimsRemits_DL_NW. Everyweek I bring in new data into this table. However, I would like to see if the data grew or shrank.

To do this, we use LAG and LEAD. Both of these functions requires that we use PARTITION so that we’re getting the LAG and LEAD data for the specific group of data.

LAG gets the data from the previous record. If no previous record exists, a NULL value will return.

LEAD gets the next row data. If no future record exits, a NULL value will return

Here is the script to show Previous (LAG) and Future (LEAD) records.

SELECT
Name,
RecordCount AS Current_Record_Count,
ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Previous_Record_Count,
ISNULL(LEAD(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) AS Next_Record_Count,
CreateDate
FROM SSIS_Log.dl.event

I like to simplify the results to something more of an Indicator (KPI).

Here is the script with an Indicator field. There’s more scripting involved but the end results is easier to read.

SELECT
Name,
RecordCount,
CASE
WHEN ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0) = 0 THEN ‘No Changes’
WHEN RecordCount ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0)
THEN ‘Up by ‘ + CAST(FORMAT(RecordCount – ISNULL(LAG(RecordCount) OVER (PARTITION BY Name ORDER BY CreateDate), 0), ‘##,###’) AS VARCHAR(20))
END AS Indicator,
CreateDate
FROM SSIS_Log.dl.event

Truncate Transaction Log

Image Posted on Updated on

Hopefully, you’re not visiting this page in a panic because you or your users are not able to use the Database because it’s out of space. Keep your sanity and strategically plan out the database maintenance plan to prevent issues like this from surfacing.

Before you truncate the transaction log, please remember that if you want to recovery any data before the truncation, that data will no longer exists. Before you truncate the log files, you’ll need to know the exact file name. Here’s how you do that.

Go to the database properties

Database Properties
Database Properties

Under Files, you’ll see the file log name.

File Log Name
File Log Name

SQL 2005

USE MyDatabaseName
GO
BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY

— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (MyDatabaseName_Log, 1);
GO

SQL 2008, 2012 R2, 2014

Steer away from WITH TRUNCATE_ONLY in these versions because it no longer exists.

If your database is already set in SIMPLE recovery mode, then every time you do a backup, the transaction log will be automatically truncated for you.

If your database is in FULL recovery mode, then here are a couple of steps

USE MyDatabaseName
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (MyDatabaseName_Log, 1);
GO

If your database is already part of a maintenance place that backups the data then use this.

BACKUP LOG MyDatabaseName_Log TO DISK = N’C:\Backup\MyDatabaseNameDB.bak’
GO

Have a cup of BEETS juice and good luck!

Table Size

Posted on Updated on

There are times when you need to migrate data or scale out your database. Here is a script to get you how much space you’re actually using per Table.

Here are 3 methods but not limited.

1. Get a specific table size

EXEC sp_spaceused N’dbo.MyTable’

2. Get all table size

CREATE TABLE #TableSize(Name VARCHAR(100), Rows VARCHAR(50), Reserved VARCHAR(50), Data VARCHAR(50), IndexSize VARCHAR(50), Unused VARCHAR(50))
INSERT INTO #TableSize
EXEC sp_msforeachtable ‘EXEC sp_spaceused [?]’

SELECT * FROM #TableSize ORDER BY Name ASC

3. Custom Code

SELECT
TableName = t.Name
,SchemaName = s.Name
,[RowCount] = p.Rows
,TotalSpaceKB = SUM(a.Total_Pages) * 8
,UsedSpaceKB = SUM(a.Used_Pages) * 8
,UnusedSpaceKB = (SUM(a.Total_Pages) – SUM(a.Used_Pages)) * 8
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.OBJECT_ID > 255
GROUP BY t.Name
,s.Name
,p.[Rows]
ORDER BY t.Name

Isolation Level and NOLOCK

Posted on Updated on

In short,

  • SET TRANSACTION ISOLATION LEVEL XXXXX is for the entire script
  • WITH (NOLOCK) is for that specific table

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM MyTable WITH (NOLOCK)

READ COMMITTED

Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting innonrepeatable reads or phantom data. This option is the SQL Server default.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.

SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Populate States Table

Posted on

I always find myself recreating a States table for web applications. Here it is.

CREATE TABLE [States](

[StatesID] [int] IDENTITY(1,1) NOT NULL,
[Abbreviation] [nchar](2) NOT NULL,
[Name] [nvarchar](128) NOT NULL,

CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
( [StatesID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]

GO

INSERT INTO [States] VALUES
(‘AL’, ‘Alabama’),
(‘AK’, ‘Alaska’),
(‘AZ’, ‘Arizona’),
(‘AR’, ‘Arkansas’),
(‘CA’, ‘California’),
(‘CO’, ‘Colorado’),
(‘CT’, ‘Connecticut’),
(‘DE’, ‘Delaware’),
(‘DC’, ‘District of Columbia’),
(‘FL’, ‘Florida’),
(‘GA’, ‘Georgia’),
(‘HI’, ‘Hawaii’),
(‘ID’, ‘Idaho’),
(‘IL’, ‘Illinois’),
(‘IN’, ‘Indiana’),
(‘IA’, ‘Iowa’),
(‘KS’, ‘Kansas’),
(‘KY’, ‘Kentucky’),
(‘LA’, ‘Louisiana’),
(‘ME’, ‘Maine’),
(‘MD’, ‘Maryland’),
(‘MA’, ‘Massachusetts’),
(‘MI’, ‘Michigan’),
(‘MN’, ‘Minnesota’),
(‘MS’, ‘Mississippi’),
(‘MO’, ‘Missouri’),
(‘MT’, ‘Montana’),
(‘NE’, ‘Nebraska’),
(‘NV’, ‘Nevada’),
(‘NH’, ‘New Hampshire’),
(‘NJ’, ‘New Jersey’),
(‘NM’, ‘New Mexico’),
(‘NY’, ‘New York’),
(‘NC’, ‘North Carolina’),
(‘ND’, ‘North Dakota’),
(‘OH’, ‘Ohio’),
(‘OK’, ‘Oklahoma’),
(‘OR’, ‘Oregon’),
(‘PA’, ‘Pennsylvania’),
(‘PR’, ‘Puerto Rico’),
(‘RI’, ‘Rhode Island’),
(‘SC’, ‘South Carolina’),
(‘SD’, ‘South Dakota’),
(‘TN’, ‘Tennessee’),
(‘TX’, ‘Texas’),
(‘UT’, ‘Utah’),
(‘VT’, ‘Vermont’),
(‘VA’, ‘Virginia’),
(‘WA’, ‘Washington’),
(‘WV’, ‘West Virginia’),
(‘WI’, ‘Wisconsin’),
(‘WY’, ‘Wyoming’);

Show all current running Stored Procedures

Posted on Updated on

There are times when you need to figure out what Stored Procedures are currently running and if they are blocking any if all other processes.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.start_time,
req.blocking_session_id,
req.wait_type,
req.wait_time,
req.last_wait_type,
req.reads,
req.writes,
req.logical_reads,
req.row_count
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE DB_NAME(database_id) = ‘MyDatabaseName

Concatenate rows into CSV

Posted on Updated on

If you have the need to take multiple rows into 1 comma separated row then this should do the trick. One of the biggest usages for this is when you’re using SSRS with multiple parameters.

DECLARE @COLUMNS VARCHAR(MAX)

SELECT @COLUMNS =
COALESCE (@COLUMNS + ‘,’ + CAST(FirstName AS NVARCHAR) + ”,” + CAST(FirstName AS NVARCHAR) + ”)
FROM MyCustomers

SELECT @COLUMNS