October | 2015 | SQL with Manoj

SQL Tips – Check count of records in Temporary Tables from other sessions

October 13, 2015 Leave a comment

Recently one Stored Procedure was giving issues on our Production system. The SP had some temporary (#) tables that were getting populated in sequence, but the final result was not as expected. As Temp-Tables cannot be accessed across other sessions you cannot see what’s going on. Also as its a PROD system I could not dissect or change the SP and see what’s happening inside.

Debugging a SP on a DEV machine is simple. I can add some extra checks after temp-table population code to get the record count by using “SELECT @@rowcount”, and was able to see the issues.

But this cannot be done on PROD, as you cannot alter the SPs there. So, to see which temp table is being populated you can use below query. This will also show the records count if any temp table is in mid of population.

SELECT 
	T.NAME AS TABLE_NAME,
	S.ROW_COUNT
FROM TEMPDB.sys.dm_db_partition_stats AS S 
INNER JOIN TEMPDB.sys.tables AS T 
ON S.OBJECT_ID = T.OBJECT_ID 
WHERE S.INDEX_ID < 2
and T.NAME like '%#TempTable%'; -- Give #Table name here

Categories: SQL Tips Tags: Temporary Tables

SQL Error – The database principal owns a schema in the database, and cannot be dropped

October 10, 2015 1 comment

Have you ever faced this error while dropping a USER from a Database? Today while dropping a USER I faced this error and for a while I was not able to fix it. After checking here & there and searching online I found some clues and got the reason for this error.

–> Let’s check the reason behind it and how to fix it:

USE [AdventureWorks2014]
GO

DROP USER [hruser]
GO

You get following error:

Msg 15138, Level 16, State 1, Line 5
The database principal owns a schema in the database, and cannot be dropped.

The above error explains very well why the USER cannot be deleted. It’s because the USER is associated with a SCHEMA and has its ownership. Thus until and unless you assign another USER as the SCHEMA owner you cannot DROP this USER.

–> To check which SCHEMA this USER owns you can use either of these 2 methods:

Method #1: By simple Query:

SELECT *
FROM sys.schemas
WHERE principal_id = USER_ID ('hruser')
GO

name	schema_id	principal_id
Sales	9		5

Method #2: By GUI in SSMS: Right click on the user name under Database -> Security -> Users

–> So, all you need to do is change the ownership of the Schema from the USER that you want to delete to some other user like dbo, as done below:.

USE [AdventureWorks2014]
GO

ALTER AUTHORIZATION ON SCHEMA::[Sales] TO [dbo]
GO

If there are more SCHEMAs then issue ALTER AUTHORIZATION ON SCHEMA statement on those schemas also to change their owners.

–> Ok, now just re-run the DROP USER statement, it will drop the User.

USE [AdventureWorks2014]
GO

DROP USER [hruser]
GO

Categories: SQL Errors Tags: ALTER AUTHORIZATION ON SCHEMA, DROP USER

SQL Error – Cannot create index on view ‘vw_SomeView’ because the view is not schema bound.

October 5, 2015 Leave a comment

This error occurs when you try to create an Index on top of a View which is not created by using “WITH SCHEMABINDING” option.

When you create Index on top of a View they are called “Indexed View” or “Materialized View”.

–> Let’s check below:

use [AdventureWorks2014]
go

DROP VIEW IF EXISTS vw_Person
GO

CREATE VIEW vw_Person
AS
SELECT 
	[BusinessEntityID], 
	[FirstName], [MiddleName], [LastName], 
	[Suffix], 
	[EmailPromotion], 
	[ModifiedDate]
FROM [Person].[Person]
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vw_Person ON [vw_Person]([BusinessEntityID])
GO

Msg 1939, Level 16, State 1, Line 18
Cannot create index on view ‘vw_Person’ because the view is not schema bound.

–> So to fix this issue you can ALTER the view definition by adding “WITH SCHEMABINDING” option with ALTER/CREATE VIEW statement as shown below.

Thus, creating an Index on a Schema bound view will not throw this error.

ALTER VIEW vw_Person
WITH SCHEMABINDING --<< here <<
AS
SELECT 
	[BusinessEntityID], 
	[FirstName], [MiddleName], [LastName], 
	[Suffix], 
	[EmailPromotion], 
	[ModifiedDate]
FROM [Person].[Person]
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vw_Person ON [vw_Person]([BusinessEntityID])
GO

–> By adding this rule or restriction SQL Server just wants to make sure if an Index is created on a View, nobody can directly change the definition of Table(s) underneath the View. And if it needs a change then first the View need to be dropped and re-created after altering the tables (error shown below).

ALTER [Person].[Person]
DROP COLUMN [Suffix]

Msg 5074, Level 16, State 1, Line 32
The object ‘vw_Person’ is dependent on column ‘suffix’.
Msg 4922, Level 16, State 9, Line 32
ALTER TABLE DROP COLUMN suffix failed because one or more objects access this column.

Categories: Indexes, SQL Errors Tags: Indexed View, Materialized View, SQL Views

SQL Server 2016 CTP 2.4 update is here | download now

October 1, 2015 Leave a comment

As pert of Microsoft’s rapid release model for SQL Server, Microsoft today announced the CTP 2.4 update of the recently and initially released Community Technology Preview (CTP) 2.2 version of SQL Server 2016 and the CTP 2.3, link.

[Register and Download the CTP 2.4 Evaluation version (180 days) here]

–> Direct download link (~2.3 GB):

– Box file SQLServer2016-x64-ENU.box
– EXE file SQLServer2016-x64-ENU.exe

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2016 (CTP2.4) – 13.0.600.65 (X64) Sep 20 2015 01:45:59 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 10240: )

–> Enhancements and Issues fixed in this release:

1. Operational Analytics and In-Memory Data Warehouse improvements

– Existing Non-Clustered ColumnStore Indexes (NCCI) are updateable without requiring index rebuild.

– Parallel Index build of Non-Clustered ColumnStore Index (NCCI), leveraging available cores/logical processors available on the server for significant performance improvement.

– INSERT operations performance in delta Row Group. (Prior to CTP2.4, the delta rowgroup data was PAGE compressed, which added overhead for Insert operations. With CTP2.4, the data in delta rowgroup is no longer PAGE compressed.)

– Better Query performance with Non-Clustered ColumnStore Index (NCCI), with:
a. String predicate pushdown.
b. Improved query path when processing deleted rows by bypassing delete buffer when possible.

2. SQL Server Smart Maintenance Plans for effective database maintenance

– Consistency-checking strategy

– Backup strategy

– Index and statistics maintenance strategy

3. Tempdb scalability improvement

– Now provides a separate tab for tempdb files configuration.