Tag Archives: T-SQL

Query to fetch table sizes and row counts quickly

Here’s a query that comes in handy when checking the size of your tables within a SQL Server Database:

SELECT st.name AS TableName,
	(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id) AS RowLength,
	si.rowcnt AS [RowCount],
	CAST(si.rowcnt*(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id)/1024.0/1024.0 AS INT) AS MaxTableSizeMB
FROM sys.tables as st
LEFT JOIN sysindexes si on st.object_id=si.id and si.indid < 2
ORDER BY st.name

SQL Server login with read access on every database

Here’s a quick script to add a new SQL server login with read access to every database on the server:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_adduser [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_datareader'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_denydatawriter'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

For newer versions of SQL Server, use this:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'CREATE USER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_datareader] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_denydatawriter] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

Be sure to also add to the “model” database. That way you will also have read access to newly created databases.

User defined function (T-SQL) for finding work days and holidays – Norwegian Edition

In my last blog post I published a handy stored procedure for populating a time dimension in SQL Server 2008. The stored procedure makes use of a UDF called dbo.fIsWorkDay(@Date). This function returns a boolean value saying if the date parameter is a work day or not. The following two functions are specific for Norwegian holidays:

T-SQL procedure for finding easter sunday (Found here: http://www.mssqltips.com/tip.asp?tip=1537):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
IF OBJECT_ID('fEasterByYear',N'FN') IS NOT NULL DROP FUNCTION fEasterByYear
 
GO
 
CREATE FUNCTION [dbo].[fEasterByYear] (@Year SMALLINT) RETURNS DATE AS
BEGIN 
	DECLARE
		@c INT,
		@n INT,
		@k INT, 
		@i INT, 
		@j INT, 
		@l INT, 
		@m INT, 
		@d INT, 
		@Easter DATE 
 
	SET @c = (@Year / 100) 
	SET @n = @Year - 19 * (@Year / 19) 
	SET @k = (@c - 17) / 25 
	SET @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15 
	SET @i = @i - 30 * ( @i / 30 ) 
	SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)) 
	SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4 
	SET @j = @j - 7 * (@j / 7) 
	SET @l = @i - @j 
	SET @m = 3 + (@l + 40) / 44 
	SET @d = @l + 28 - 31 * ( @m / 4 ) 
 
	SET @Easter = CAST((SELECT CONVERT(CHAR(4),@Year) + '-' + RIGHT('0' + CONVERT(VARCHAR(2),@m),2) + '-' + RIGHT('0' + CONVERT(VARCHAR(2),@d),2) + 'T00:00:00') AS DATE)
 
	RETURN @Easter 
END

Calculating work day:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
IF OBJECT_ID('fIsWorkday',N'FN') IS NOT NULL DROP FUNCTION fIsWorkDay
 
GO
 
CREATE FUNCTION [dbo].[fIsWorkDay] (@Date DATE) RETURNS BIT AS
BEGIN
	DECLARE @IsWorkDay BIT = 1
 
	SELECT @IsWorkDay = 
	CASE
		--Exclude Saturday and Sunday (Datefirst-independent)
		WHEN ((@@DATEFIRST + DATEPART(dw,@Date)) % 7) IN (0,1) THEN 0 
		--Exclude National Day
		WHEN MONTH(@Date)=5 AND DAY(@Date)=17 THEN 0
		--Exclude Workers' Day
		WHEN MONTH(@Date)=5 AND DAY(@Date)=1 THEN 0
		--Exclude Christmas
		WHEN MONTH(@Date)=12 AND DAY(@Date) IN (25,26) THEN 0
		--Exclude New Years
		WHEN MONTH(@Date)=1 AND DAY(@Date)=1 THEN 0
		--Exclude Easter, Pentecost and Ascension of Jesus
		WHEN @Date IN (
			DATEADD(DAY,-3,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,-2,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,1,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,39,dbo.fEasterByYear(YEAR(@Date))),
			DATEADD(DAY,50,dbo.fEasterByYear(YEAR(@Date)))
			) THEN 0
		ELSE 1
	END
 
	RETURN @IsWorkDay
END

Stored procedure for generating and populating a time dimension in SQL Server

There are many was to generate time dimensions (calendar) for use in a multi dimensional data warehouse. Kimball suggests writing the dates in an Excel workbook and load that into the data warehouse. I prefer to populate the time dimension using a stored procedure contained in the DWH.

Regarding primary key for the time dimension, I usually use an 4 byte integer with the format YYYYMMDD. I prefer this over using a 3 byte date column, as I then have the possibility to use negative primary keys with special meanings.

Creating the table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
IF OBJECT_ID('DimCalendar',N'U') IS NOT NULL DROP TABLE DimCalendar
 
CREATE TABLE [DimCalendar] (
	[DateKey] [INT] NOT NULL PRIMARY KEY,
	[Date] [DATE] NOT NULL,
	[Day] [TINYINT] NOT NULL,
	[Week] [TINYINT] NOT NULL,
	[Month] [TINYINT] NOT NULL,
	[Year] [SMALLINT] NOT NULL,
	[WeekYear] [SMALLINT] NOT NULL,
	[WeekdayName] [VARCHAR](10) NOT NULL,
	[WeekdayNameShort] [VARCHAR](3) NOT NULL,
	[MonthName] [VARCHAR](20) NOT NULL,
	[MonthNameShort] [VARCHAR](3) NOT NULL,
	[Quarter] [TINYINT] NOT NULL,
	[Workday] [BIT] NOT NULL,
	[WorkHours] [DECIMAL](6,2) NOT NULL
)

Populating the table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
IF OBJECT_ID('sPopulateDimCalendar',N'P') IS NOT NULL DROP PROCEDURE sPopulateDimCalendar
 
GO
 
CREATE PROCEDURE sPopulateDimCalendar
 
@FromDate DATE = '1990-01-01',
@ToDate DATE = '2020-12-31'
 
AS
 
BEGIN
 
--Setting language
SET LANGUAGE English
 
--Populating Dimension
WHILE @FromDate <= @ToDate
	BEGIN
		INSERT INTO DimCalendar(
			[DateKey], 
			[Date], 
			[Day], 
			[Week], 
			[Month], 
			[Year], 
			[WeekYear], 
			[WeekdayName], 
			[WeekdayNameShort], 
			[MonthName], 
			[MonthNameShort], 
			[Quarter],
			[WorkDay],
			[WorkHours]
            )
            SELECT 
                YEAR(@FromDate)*10000 + MONTH(@FromDate) * 100 + DAY(@FromDate) AS DateKey, 
		@FromDate AS [Date],
                DAY(@FromDate) AS [Day],
		DATEPART(ISO_WEEK, @FromDate) AS [Week],
                MONTH(@FromDate) AS [Month],
                YEAR(@FromDate) AS [Year],
		CASE 
			WHEN DATEPART(ISO_WEEK, @FromDate) = 53 AND MONTH(@FromDate) = 1 THEN YEAR(@FromDate) - 1 
			WHEN DATEPART(ISO_WEEK, @FromDate) = 1 AND MONTH(@FromDate) = 12 THEN YEAR(@FromDate) + 1
			ELSE YEAR(@FromDate) 
		END AS [WeekYear],
                DATENAME(WEEKDAY,@FromDate) AS WeekDayName,
                LEFT(DATENAME(WEEKDAY,@FromDate), 3) AS WeekDayNameShort,
                DATENAME(MONTH, @FromDate) AS MonthName,
                LEFT(DATENAME(MONTH, @FromDate), 3) AS MonthNameShort,
                DATENAME(QUARTER,@FromDate) AS [Quarter],
                dbo.fIsWorkDay(@FromDate) AS WorkDay,
                CASE WHEN dbo.fIsWorkDay(@FromDate) = 1 THEN 8 ELSE 0 END AS WorkHours
 
      SET @FromDate = DATEADD(DAY, 1, @FromDate)
END
 
END

The function dbo.fIsWorkDay(@Date) is obviously different depending on which country you are in.