Limitations on ColumnStore Indexes

ColumnStore index is a new type of index available in SQL 2012 and has been enhanced in SQL 2014 version. ColumnStore index stores the data column wise instead of a regular row based B tree format and hence, it has better ability to compresses the data. When it was first released in SQL 2012, it has several limitations and some of those have been addressed in later versions of SQL(SQL 2014).

Quick pointers on limitations on ColumnStore Indexes in SQL 2012 and 2014:

• NonClustered columnstore does not require the table to have a clustered index.
• In SQL 2014, Clustered ColumnStore index is the only index that can exist on the table.
• In SQL 2014, Clustered ColumnStore Index is updateable. Data can be inserted,updated and deleted from the table.
• In SQL 2012/14 – NonClustered ColumnStore Index are NOT updateable. One solution is, to disable the columnstore Index and do the data load and then re enable the columnstore index.
• Varchar(max),nvarchar(max),xml, ntext,text,image etc are not suitable datatypes for Columnstore index.
• Foreign Key, Unique constraints cannot be defined.
• In SQL 2012, there is no clustered columnstore option only NonClustered ColumnStore index.
• In SQL 2014, we can create clustered columnstore or NonClustered ColumnStore index. However, if the table has clustered columnstore index, we cannot create any other index.

I will add more points as I come across them.

Advertisement

Deny vs Grant – which one supersedes?

There can be situations where a database user is part of multiple groups and if one of the group the user is part of has deny permissions and the other group has grant permissions on a object – which permissions would go through?

In common sense, you would think deny should supersede the grant permissions, because you want to have preventive protection and SQL Server works in the same way. Deny will override the Grant permissions.

Below sample code will demonstrate the same:
--create a test login
USE Master
GO
CREATE LOGIN wsxcde1 with password ='wsxcde1'
--create a test database to play with
CREATE DATABASE TESTING
GO
USE Testing
GO
--Create sample table
Create Table Test(sno int)
Create table Test2(sno int)

--Create user for the logins and assign data reader role for the users.
CREATE USER [wsxcde1] FOR LOGIN [wsxcde1]
GO
EXEC sp_addrolemember N'db_datareader', N'wsxcde1'
GO
--Create a custom role and add the user to this role and Grant Read permissions to the role.
Create Role myCustomRole
GO
EXEC sp_addrolemember N'myCustomRole', N'wsxcde1'
GO
EXEC sp_addrolemember N'db_datareader', N'myCustomRole'
GO

--Deny select permissions on table test2 to the custom role
Deny select on test2 to mycustomrole

At this point, database user wsxcde1 has read permissions on the database directly as the user itself and indirectly through the database role. However, the user was denied permissions on table dbo.test2 explicitly for the custom role.

--Now check if select will go through
Execute as user ='wsxcde1'
Select * from dbo.test2
Select suser_name()
Revert
--Clean up
USE Master
GO
Drop database testing
Drop login wsxcde1

TSQL to get Actual & Estimated Execution Plan


Create Table myTable(sno int identity(1,1),sname varchar(20),scity varchar(30))
Insert into myTable values('SomeName','SomeCity')
Create NonClustered Index NC_sname on myTable(sname)
GO
--GETS THE ESTIMATED EXECUTION PLAN AND THE QUERY IS NOT EXECUTED
Set SHOWPLAN_XML ON
GO
Select SName from myTable WHERE SName='SomeName'
GO
Set SHOWPLAN_XML OFF
GO
--GETS THE ACTUAL EXECUTION PLAN AND THE QUERY IS EXECUTED
Set Statistics XML ON
Select SName from myTable WHERE SName='SomeName'
Set Statistics XML OFF
GO
Drop Table myTable

Query to find when last full backup was taken

The below query returns last time a full backup was taken on the database.

select database_name as [Database],a.name [BackupName],user_name as [BackupTakenBy],
backup_start_date,backup_finish_date,backup_size as [BackupSize],is_copy_only,
has_backup_checksums,b.physical_device_name as [BackupLocation]
from msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
where backup_set_id in
(select max(backup_set_id) from msdb.dbo.backupset where type='D' and is_copy_only in (1,0)
group by database_name)


If you do not want to consider copy_only backups, replace “is_copy_only in (1,0)” with “is_copy_only in (0)”.

Maintenance Plan History

The below query returns the maintenance plan history along with the details on the tasks present in the plan. To log the actual command and task details,”log extended information” under Reporting and Logging option should be checked in the maintenance plan.
Select @@SERVERNAME [servername],
case when D.Succeeded=1 then 'Success' when D.succeeded=0 then 'Failed' End as Result,
A.name,B.subplan_name,D.line1,D.line2,D.line3,D.line4,
D.line5,D.start_time,D.end_time,D.command
From msdb.dbo.sysmaintplan_plans a inner join msdb.dbo.sysmaintplan_subplans b on a.id=b.plan_id
inner join msdb.dbo.sysmaintplan_log c on c.plan_id=b.plan_id and c.Subplan_id=b.subplan_id
inner join msdb.dbo.sysmaintplan_logdetail d on d.task_detail_id=c.task_detail_id
Order By D.start_time DESC