Cannot use the %ls granularity hint on the table “%.*ls” because locking at the specified granularity is inhibited.

This error occurs when the lock cannot be applied on a specific table at a specified granularity.

It occurs for two granularity levels:
ROW
PAGE

For more information on ROW granularity locking error follow this link:
Cannot use the ROW granularity hint on the table because locking at the specified granularity is inhibited.

For more information on PAGE granularity locking error follow this link:
Cannot use the PAGE granularity hint on the table because locking at the specified granularity is inhibited.

Cannot use the PAGE granularity hint on the table because locking at the specified granularity is inhibited.

Error:

Msg 651, Level 16, State 1, Line 1
Cannot use the PAGE granularity hint on the table [table name] because locking at the specified granularity is inhibited.

This error could occur due to creation of indexes on the table with ALLOW_PAGE_LOCKS = OFF

Check the indexes on the table to see if there is any index with ALLOW_PAGE_LOCKS off.

Query:
[sql]
SELECT * FROM sys.indexes
WHERE ALLOW_PAGE_LOCKS = 0 and object_name(object_id) = [table name]
[/sql]

Replace the [table name] with the table name in the error and check if there are any indexes with ALLOW_PAGE_LOCKS disabled.

Fix:
1. Disable the index or change the index to enable page locks.
2. Use row locks or table locks instead.

Cannot use the ROW granularity hint on the table because locking at the specified granularity is inhibited.

Error:

Msg 651, Level 16, State 1, Line 1
Cannot use the ROW granularity hint on the table because locking at the specified granularity is inhibited.

This error could occur due to creation of indexes on the table with ALLOW_ROW_LOCKS = OFF

Check the indexes on the table to see if there is any index with ALLOW_ROW_LOCKS off.

Query:
[sql]
SELECT * FROM sys.indexes
WHERE allow_row_locks = 0 and object_name(object_id) = [table name]
[/sql]

Replace the [table name] with the table name in the error and check if there are any indexes with allow_row_locks disabled.

Fix:
1. Disable the index or change the index to enable row locks.
2. Use page locks or table locks instead.

truncate all tables sql server 2008

How to truncate all tables sql server?

Following script truncates all the tables in SQL Server:

[sql]

DECLARE @tablename AS VARCHAR (1000)

DECLARE @sql AS VARCHAR (1000)

IF OBJECT_ID(‘tempdb.dbo.#tables’) IS NOT NULL
DROP TABLE #tables

SELECT *
INTO #tables
FROM sys.tables

WHILE EXISTS (SELECT *
FROM #tables)
BEGIN
SELECT @tablename = name
FROM #tables
SELECT @sql = ‘truncate table ‘ + @tablename;
PRINT @sql
EXECUTE (@sql)
DELETE #tables
WHERE name = @tablename;
END
[/sql]

Works if the tables do not have foriegn key constrains or schema binding relations with other tables/objects in the database.

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Error:

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Reason:
The select statements in the clause do not have the same number of columns.

Fix:
Make sure that all the starements using UNION, INTERSECT or EXCEPT operator have same number of expressions(columns).
Use column names instead of using * in the select list.

Sample example code

Before:
[sql]
SELECT 1
UNION
SELECT 2,3
[/sql]

After:
[sql]
SELECT 1,NULL
UNION
SELECT 2,3
[/sql]

sql server union order by clause

using union/union all with order by clause in sql server.

In order to order the results of a union statement add order by clause with the column names corresponding to first select statement.
If you are using alias names use the alias column names used in the first select statement

Below example shows how to use union with order by clause in sql server:
[sql]
select 1 AS Column1,’c’ AS Column2
union
select 2,’b’
union
select 3,’a’
ORDER BY Column2
[/sql]
result:
Column1 Column2
———– ——-
3 a
2 b
1 c

(3 row(s) affected)

with out order by claues:
[sql]
select 1 AS Column1,’c’ AS Column2
union
select 2,’b’
union
select 3,’a’
[/sql]
result:
Column1 Column2
———– ——-
1 c
2 b
3 a

(3 row(s) affected)

Applicable to: SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 r2, SQL Server 2012

sql server union with example

UNION : UNION Statement in SQL Server combines all rows in the results of two queries.

EXAMPLE’s Given below:

[sql]
SELECT 1
UNION
SELECT 2
[/sql]

Result:

———–
1
2

(2 row(s) affected)

Result is the combining the results of two select queries.

[sql]
SELECT ‘Apple’
UNION
SELECT ‘Mango’
UNION
SELECT ‘Grape’
[/sql]

Result:

—–
Apple
Grape
Mango

(3 row(s) affected)

Result is the combining the results of three select queries.

[sql]
SELECT ‘Apple’
UNION
SELECT ‘Apple’
UNION
SELECT ‘Grape’
[/sql]

result:
—–
Apple
Grape

(2 row(s) affected)

Result is the combining the results of three select queries, it can be observed that the duplicate rows in the select queries are ignored.

[sql]
SET NOCOUNT ON
DECLARE @table1 TABLE (
id INT)

DECLARE @table2 TABLE (
id INT)

DECLARE @i AS INT

SET @i = 1

WHILE @i <= 10
BEGIN
INSERT INTO @table1
SELECT @i
SET @i = @i + 1
END

SET @i = 5

WHILE @i <= 15
BEGIN
INSERT INTO @table2
SELECT @i
SET @i = @i + 1
END

SELECT *
FROM @table1

SELECT *
FROM @table2

SELECT *
FROM @table1
UNION
SELECT *
FROM @table2
[/sql]
Result:

id
———–
1
2
3
4
5
6
7
8
9
10

id
———–
5
6
7
8
9
10
11
12
13
14
15

id
———–
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

@table1 contains numbers 1 to 10, @table2 contains numbers 5 to 15
union of @table1 and @table2 contains numbers 1 to 15 (with out dublicate values)

So union statement works as follows:
If two queries are unioned and if the query 1 results in x rows and query 2 in y rows of which z rows are dupicate with respect to query1,
then union gives x+y-z rows.

Note:
Make sure that the select statements fetch the same number of columns, if not you would get the error

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists

Sample Code:
[sql]
SELECT ‘Apple’
UNION
SELECT ‘Apple’,’Grape’
[/sql]

Make sure that the column datatypes are same in each select statements, if not you would get the error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘two’ to data type int.

Sample Code:
[sql]
SELECT ‘Apple’,1
UNION
SELECT ‘Grape’,’two’
[/sql]

The column names in the first select statement are used in the output of union statement. Use alias names if you wanted to change the column names.
If you are specifng values directly in select queries then use alias names for columns.
Sample queries:
[sql]
SELECT * INTO #a FROM (
SELECT ‘Apple’ ,1
UNION
SELECT ‘Grape’,’2′) a
[/sql]
Output:

Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 1 of ‘a’.
Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 2 of ‘a’.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.

Fixed code:
[sql]
SELECT * INTO #a FROM (
SELECT ‘Apple’ a1 ,1 a2
UNION
SELECT ‘Grape’,’2′) a
[/sql]

Applicable to SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 r2, SQL Server 2012

sql server business intelligence interview questions and answers

Following are the links where you can get material regarding sql server business intelligence (bi) interview questions and answers:

Data Warehousing Interview Questions and Answers Complete List Download

SQL Server Reporting Services FAQ

Analysis Services interview questions

Data Warehousing Interview Questions

Data Warehouse Concepts Interview Questions

Business Intelligence interview questions

SSRS Interview questions and answers

SSIS interview questions and answers

Download SSRS Interview Questions and Answers PDF

SQL Server Reporting Services Interview Questions with Answers

Business Intelligence interview questions

SSIS Interview Questions

SSIS Interview Questions – Part 1

SQL Server BI DEV – DBA Q and A

Data warehousing interview questions and answers

bi faqs

MS SQL Server interview questions

Download pdf file using the below link:
SQL Server 2008 Interview Questions Answers pdf

sql server convert string to date mm dd yyyy

Below script converts string(varchar) to date. Date value in string can be in the format mm/dd/yy (or) mm-dd-yyyy
[sql]
–mm/dd/yyyy
SELECT CAST(’12/30/2000′ AS DATE)
–mm-dd-yyyy using cast
SELECT CAST(’12-30-2000′ AS DATE)

–mm/dd/yyyy using cast
SELECT CONVERT(date,’12/30/2000′)
–mm-dd-yyyy using cast
SELECT CONVERT(date,’12-30-2000′)
[/sql]

sql server convert string to date yyyymmdd

You can use cast or convert to convert string which is in yyyy/mm/dd (or) yyyy-mm-dd format to date.

Below is the script:
[sql]
–yyyy/mm/dd using cast
SELECT CAST(‘2000/12/30’ AS DATE)
–yyyy-mm-dd using cast
SELECT CAST(‘2000-12-30′ AS DATE)

–yyyy/mm/dd using cast
SELECT CONVERT(date,’2000/12/30′)
–yyyy-mm-dd using cast
SELECT CONVERT(date,’2000-12-30’)
[/sql]