Archive

Archive for the ‘SQL Tips’ Category

SQL Tip – How to get size of all Tables in a Database?

June 16, 2015 Leave a comment

 
You can use SQL Server system Stored Procedure sp_spaceused to get the details of a table like rows and size. Just pass the table name to this SP as a parameter:

USE [AdventureWorks2014]
GO

EXEC sp_spaceused N'Person.Person'
GO
name		rows	reserved	data		index_size	unused
Person.Person	19972   85840 KB	30504 KB	53192 KB	2144 KB

 

But if you want to get details of more than one or all tables of a Database then you can iterate this SP for all tables. To iterate for all tables there is an undocumented function in SQL Server i.e. sp_msForEachTable, and you can provide your sp_spaceused query as a parameter to this SP:

CREATE TABLE #TableSize (
	 name		NVARCHAR(128)
	,rows		CHAR(20)
	,reserved	VARCHAR(18)
	,data		VARCHAR(18)
	,index_size VARCHAR(18)
	,unused		VARCHAR(18)
)

sp_msForEachTable 'INSERT INTO #TableSize (name, rows, reserved, data, index_size, unused) EXEC sp_spaceused [?]'

SELECT * FROM #TableSize 

DROP TABLE #TableSize
GO

 

There is one more way to get the details by using system (sys) views, query below:

SELECT 
     t.name AS TableName 
    ,s.name AS SchemaName 
    ,p.rows AS RowCounts 
    ,SizeInKB = SUM(a.total_pages) * 8
    ,UsedSpaceInKB = SUM(a.used_pages) * 8
    ,UnUsedSpaceInKB = (SUM(a.total_pages) - SUM(a.used_pages)) * 8
    ,SizeInMB = (SUM(a.total_pages) * 8)/1024 
    ,SizeInGB = (SUM(a.total_pages) * 8)/(1024*1024) 
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 

INNER JOIN sys.schemas s 
ON t.schema_id = s.schema_id 

WHERE t.is_ms_shipped = 0 
AND i.object_id > 255 
--AND t.name IN ('tbl1', 'tbl2', 'tbl3', 'tbl4') 
--AND t.name LIKE 'pattern%'  
GROUP BY t.name, s.name, p.rows 
ORDER BY SizeInMB DESC

To get details of selective tables just apply the Table names above in the IN() clause or LIKE operator.


Categories: SQL Tips

T-SQL Query solution to ‎SSGAS2015‬ 2nd Question/Challenge – SQL Server Geeks

June 12, 2015 1 comment

The 2nd Challenge in SQL Server Geeks Facebook Page goes like this:

For the #SSGAS2015 attendees are leaving comments with their email ids. These comments gets saved into a table COMMENTS. You as an SQL Developer, need to extract all the email ids from the COMMENTS table.

–> Sample records in the table:

ID	Comment
1	Can I pay using PayUMoney. Please revert on ahmad.osama1984@gmail.com. 
        Also send details to manusqlgeek@gmail.com
2	I would like to get updates on SQLServerGeeks summit. Please send details 
        to myemailid@yahoo.com

–> Expected Answer:

ID	Emails
1	ahmad.osama1984@gmail.com, manusqlgeek@gmail.com
2	myemailid@yahoo.com

 

–> Initially this looked very tricky and tough question, but when you think by dividing the problem it looked very simple and here it goes:

1. First split the sentence into columns containing individual words by the whitespace separator.

2. Then Transpose all the columns as rows.

3. Then filter out the rows that contains email ID values.

4. Now for every ID column Transpost back the filtered rows into a single comma separated column value.
 

–> Here is the full solution:

-- Create the Table (DDL):
CREATE TABLE COMMENTS (
	ID INT, 
	Comment VARCHAR(1000)
)

-- Insert the 2 test rows:
INSERT INTO COMMENTS
SELECT 1, 'Can I pay using PayUMoney. Please revert on ahmad.osama1984@gmail.com. Also send details to manusqlgeek@gmail.com'
UNION ALL
SELECT 2, 'I would like to get updates on SQLServerGeeks summit. Please send details to myemailid@yahoo.com'

-- Check the rows:
select * from COMMENTS

-- Final solution as a single T-SQL Query:
;with CTE AS (
	SELECT A.ID, Split.a.value('.', 'VARCHAR(100)') AS Email
	FROM (SELECT ID, CAST ('<M>' + REPLACE(REPLACE(Comment,'. ', ' '), ' ', '</M><M>') + '</M>' AS XML) AS String
		FROM  COMMENTS) AS A
	CROSS APPLY String.nodes ('/M') AS Split(a)
)
, CTE2 AS (
	SELECT ID, Email
	FROM CTE
	WHERE Email like '%@%'
)
SELECT DISTINCT ID, (SELECT STUFF((SELECT ', ' + Email
 FROM CTE2
 WHERE ID  = t.ID
 FOR XML PATH('')),1,1,'')) AS Emails
FROM CTE2 t

-- Drop the table finally:
DROP TABLE COMMENTS
GO

Waiting for the coming challenges. Thanks!


GROUPING SETS equivalent | for SQL Server 2005 and below

March 23, 2015 4 comments

Sometime back I discussed about [GROUPING SETS] in my previous post and today one reader of this blog asked me how we can create the same result-set without using GROUPING SETS if we are on previous versions like SQL Server 2005 or 2000.
 

–> Let’s take the following SQL Query that uses GROUPING SETS operator and let’s see what Output we get:

-- GROUPING SETS
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class, section)
	,(class)
	,()
)
Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	a	NULL	230
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	b	NULL	230
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	a	NULL	190
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	b	NULL	210
Intermediate	NULL	NULL	400
NULL		NULL	NULL	860

You can refer to the DDL and population of this #tempTable in my previous post, [link].
 

–> And now let’s see how can we recreate the same result set without using GROUPING SETS if we are on lower versions of SQL Server (<= 2005):

-- ROLLUP and Grouping Sets Equivalent (pre SQL 2008)
SELECT class, section, rollno, marks as [sum]
FROM #tempTable

UNION ALL

SELECT class, section, null as rollnu, sum(marks) [sum]
FROM #tempTable
GROUP BY class, section

UNION ALL

SELECT class, null, null, sum(marks) [sum]
FROM #tempTable
GROUP BY class

UNION ALL

SELECT null, null, null, sum(marks) [sum]
FROM #tempTable

ORDER BY class DESC, section DESC, rollno DESC

 

Thanks, please let me know your thoughts and comments!!!


Categories: SQL Tips Tags: , , ,

SQL Tip – Disable or Enable all Indexes of a table at once

July 28, 2014 Leave a comment

 
There are times when you need to DISABLE all Indexes of a table, like when there is a need to INSERT/UPDATE huge records in a table. As you have to INSERT huge records in a table the whole INSERT process could take more time to maintain/process Indexes on that table. Thus before inserting records its good to DISABLE all Non-Clustered indexes and post processing Re-Enable them.

USE [AdventureWorks2014]
GO

-- Disable Index
-- Syntax: ALTER INDEX [idx_name] ON [table_name] DISABLE;
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] DISABLE;
GO

-- Enable Index
-- Syntax: ALTER INDEX [idx_name] ON [table_name] REBUILD;
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] REBUILD;
GO

Please note: to Enable you need to use REBUILD option, there is no ENABLE option just like DISABLE in above DDL statements.
 

–> Generate Queries of ALTER DDL scripts to:

– Disable all Indexes:

SELECT 
	o.name, 
	'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' 
		+ QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
		+ '.' + QUOTENAME(o.name) + ' DISABLE;'
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
AND o.name = 'Person'

 

– Enable all Indexes:

SELECT 
	o.name, 
	'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' 
		+ QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
		+ '.' + QUOTENAME(o.name) + ' REBUILD;'
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
AND o.name = 'Person'

Maintaining Uniqueness with Clustered ColumnStore Index | SQL Server 2014

July 24, 2014 5 comments

Column Store indexes were introduced in SQL Server 2012 with a flavor of Non-Clustered index i.e. “Non-Clustered ColumnStore” index. However there is a big limitation that the underlying table becomes read-only as soon as you create one.

In SQL Server 2014 this behavior is unchanged and addition to this you can also create ColumnStore index as a Clustered index. And the good thing is that the table having “Clustered ColumnStore” index can also be updated. However there is one more big limitation here that there is no Clustered Key with this type if index, thus risking the Uniqueness in the table.
 

–> Here we will see this limitation and a workaround which can be used in some scenarios:

USE tempdb
GO

-- Create a simple table with 3 columns having 1st column to contain Unique values:
CREATE TABLE dbo.TableWithCCI
(
	PKCol int NOT NULL,
	Foo int,
	Bar int
)
GO

-- Now create a "Clustered ColumnStore" index on this table:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TableWithCCI ON dbo.TableWithCCI
GO

Notice: While creating this index there is no provision to provided the “Clustering Key”, as this index includes all of the columns in the table, and stores the entire table by compressing the data and store by column.

On checking the metadata (by ALT+F1) of the table, you will see NULL under the index_keys column:

SQLServer2014_Unique_CCI
 

– Now let’s check this feature of absence of Uniquenes. We will enter 2 records with same value:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33
GO

SELECT * FROM dbo.TableWithCCI
GO

You will see 2 records with same duplicate value.
 

– Now, let’s create another Unique index to enforce this constraint:

CREATE UNIQUE INDEX UX_TableWithCCI ON dbo.TableWithCCI(PKCol)
GO

We get an error that you cannot create more indexes if you have a Clustered ColumnStore index:

Msg 35303, Level 16, State 1, Line 25
CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.

 

–> Workaround: As a workaround we can create an Indexed/Materialized View on top this table, with Clustering Key as the PK (1st column of the table/view):

CREATE VIEW dbo.vwTableWithCCI 
	WITH SCHEMABINDING
AS 
	SELECT PKCol, Foo, Bar
	FROM dbo.TableWithCCI
GO

-- Delete duplicate records entered previously:
DELETE FROM dbo.TableWithCCI
GO

-- Create a Unique Clustered Index on top of the View to Materialize it:
CREATE UNIQUE CLUSTERED INDEX IDX_vwTableWithCCI ON dbo.vwTableWithCCI(PKCol)
GO

– Now let’s try to enter duplicate records again and see if these can be entered or not:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33
GO

– As expected we get an error after we inserted 1st records and tried to insert the 2nd duplicate record:

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 48
Cannot insert duplicate key row in object ‘dbo.vwTableWithCCI’ with unique index ‘IDX_vwTableWithCCI’. The duplicate key value is (1).
The statement has been terminated.

–> Not sure why Microsoft has put this limitation of not maintaining the Uniqueness with these indexes. While using this workaround you need to consider this approach if possible. Like in some scenarios where the table is very big and there are frequent updates (INSERT/UPDATE/DELETES) this approach of maintaining another Indexed-View would be expensive. So this approach should be evaluated before implementing.
 

-- Final Cleanup:
DROP VIEW dbo.vwTableWithCCI
GO
DROP TABLE dbo.TableWithCCI
GO

 

I look forward in new versions of SQL Server to address this limitation.
You can also refer to MSDN BOL [here] for checking all limitations with ColumnStore Indexes.
 

[Update as of May-2015] with SQL Server 2016 you can make unique Clustered ColumnStore Index indirectly by creating Primary/Unique Key Constraint on a heap with a Non-Clustered Index, [check here].
 

Update: Know on ColumnStore Indexes as of SQL Server 2016:


 

Check & Like my FB Page