Query to change the column Type to another Type

DECLARE @SourceColumn NVARCHAR(MAX),
@DestinationColumn NVARCHAR(MAX)

SET @SourceColumn = 'VARCHAR'
SET @DestinationColumn = 'NVARCHAR'

SELECT
'Alter Table ' + OBJECT_NAME(c.OBJECT_ID) + ' Alter Column ' +
c.name + @DestinationColumn + '(' + CONVERT(NVARCHAR(MAX),c.max_length) + ')'
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE OBJECT_ID = OBJECT_ID('tb_test')
AND t.NAME = @SourceColumn

Attach Database without log file and with multiple files

Attach Database without log file and with multiple files

CREATE DATABASE [InitialLoad] ON
( FILENAME = N'E:\SQLDATA\InitialLoad.mdf'),
(FILENAME = N'E:\SQLDATA\InitialLoad.adf'),
(FILENAME = N'E:\SQLDATA\InitialLoad.idf' )
FOR ATTACH_REBUILD_LOG

Shrink Database Files

ALTER DATABASE --DatabaseName--
SET RECOVERY SIMPLE;

SELECT name FROM sys.database_files

DBCC SHRINKFILE (--name--, 3);

ALTER DATABASE --DatabaseName--
SET RECOVERY FULL;

SQL Server 2008 Query Hints

SQL Server 2008 query hints override any execution plan the query optimizer might select for a
query. Because the query optimizer typically selects the best execution plan for a query, you
should use the query hints only as a last resort by experienced developers and database
administrators.
In this article, you can find some tips to use SQL Server 2008 query hints.

If you want to set any hints for your query, do not remember to test the query
with hint and without the hint and compare results.
Because SQL Server 2008 query optimizer usually proposes the best execution plan, you should test
the query with hint and use this hint only when it provides the best result. If the query hint
provides the better execution plan not stable, avoid using this hint and rely on the SQL Server
query optimizer.

Use the FAST n query hint if you need to quickly return only n rows.
You can quickly return n rows and can work with them, when the query continues execution and
produces its full result set.
This is the example to quickly return 5 rows from the titles and titleauthor tables:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (FAST 5)

Consider using the RECOMPILE query hint to discard the plan generated for the query
after it executes.
When the RECOMPILE hint is used the query plan is generated the next time the same query is
executed. Microsoft recommends using this hint for queries with variable values that vary widely
each time they are compiled and executed.

Use the HASH GROUP hint if you need that aggregation described in the GROUP BY,
DISTINCT, or COMPUTE clause of the query should use hashing.
This example shows how you can use the HASH GROUP hint:
SELECT title_id, count(*) FROM titleauthor
GROUP BY title_id OPTION (HASH GROUP)

If you need that all UNION operations are performed by concatenation UNION sets,
use the CONCAT UNION hint.
Keep in mind, that only the last query involving a UNION operator can have the OPTION clause.
This example shows how you can use the CONCAT UNION hint:
SELECT 'warnings' as 'Severity', COUNT(*) FROM sys.messages WHERE severity < 11
UNION
SELECT 'user errors' as 'Severity', COUNT(*) FROM sys.messages WHERE severity BETWEEN 11 AND 16
UNION
SELECT 'software/hardware errors' as 'Severity', COUNT(*)
FROM sys.messages WHERE severity BETWEEN 17 AND 25 OPTION (CONCAT UNION)

Use the HASH UNION hint if you need that all UNION operations will be performed
by hashing UNION sets.
Keep in mind, that only the last query involving a UNION operator can have the OPTION clause.
This example shows how you can use the HASH UNION hint:
SELECT 'warnings' as 'Severity', COUNT(*) FROM sys.messages WHERE severity < 11
UNION
SELECT 'user errors' as 'Severity', COUNT(*) FROM sys.messages WHERE severity BETWEEN 11 AND 16
UNION
SELECT 'software/hardware errors' as 'Severity', COUNT(*)
FROM sys.messages WHERE severity BETWEEN 17 AND 25 OPTION (HASH UNION)

Consider using the PARAMETERIZATION query hint to specify the parameterization
rules that the query optimizer applies to the query when it is compiled.
The PARAMETERIZATION hint has two parameters SIMPLE and FORCED. When the SIMPLE parameter is
used with the PARAMETERIZATION hint, the query optimizer attempts to use simple parameterization.
When the FORCED parameter is used, the query optimizer attempts to use forced parameterization.
The PARAMETERIZATION query hint is used to override the current setting of the PARAMETERIZATION
database option.
Note. The PARAMETERIZATION query hint cannot be specified directly within a query, it can
only be specified inside a plan guide.

Use the MAXDOP n query hint if you want to override the 'max degree of parallelism'
configuration option for this query.
The 'max degree of parallelism' option limits the number of processors to use in parallel plan
execution. This option by default is 0, which causes to use the actual number of available CPUs.
This example sets the 'max degree of parallelism' option to 0, so all CPUs will be used to run
the query:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (MAXDOP 0)

You can use the KEEP PLAN query hint if you want to reduce the estimated recompile
threshold for a query.
For example, using this hint reduces the number of recompiles when multiple table updates occur.
This example shows how you can use the KEEP PLAN hint:
SELECT * FROM authors OPTION (KEEP PLAN)

Use the ORDER GROUP hint if you need that aggregation described in the GROUP BY,
DISTINCT, or COMPUTE clause of the query should use ordering.
This example shows how you can use the ORDER GROUP hint:
SELECT au_id, count(*) FROM titleauthor
GROUP BY au_id OPTION (ORDER GROUP)

If you want to specify that all join operations should be performed by merge join,
you can use the MERGE JOIN hint.
If you specify several join hints, SQL Server query optimizer selects the least expensive join
strategy.
This example shows how you can use the MERGE JOIN hint:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (MERGE JOIN)

Use the KEEPFIXED PLAN query hint if you want to ensure that a query will be
recompiled only if the schema of the underlying tables is changed or sp_recompile
is executed against the underlying tables.
If you use this hint, the query will not be recompiled due to changes in statistics or to the
indexed columns.
This example shows how you can use the KEEPFIXED PLAN hint:
SELECT * FROM authors OPTION (KEEPFIXED PLAN)

If you want to specify that all join operations should be performed by hash join,
you can use the HASH JOIN hint.
If you specify several join hints, SQL Server query optimizer selects the least expensive join
strategy.
This example shows how you can use the HASH JOIN hint:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (HASH JOIN)

Use the ROBUST PLAN query hint if you want to force the query optimizer to
attempt an execution plan that works for the maximum potential row size.
By default, SQL Server 2008 query optimizer may use the intermediate tables to execute the query
and the rows may be so wide that the particular operator cannot process the row. In this case,
you can encounter an error message about exceeding the row size limit. To avoid such errors,
consider using the ROBUST PLAN query hint.
Note. Because the ROBUST PLAN query hint may hurt performance, use this hint only if you have
received an error message about exceeding the row size limit.
This example shows how you can use the ROBUST PLAN hint:
SELECT a.notes FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id JOIN authors c
ON b.au_id = c.au_id
WHERE au_fname = 'Johnson'
OPTION (ROBUST PLAN)

If you want to specify that all join operations should be performed by nested loop join,
you can use the LOOP JOIN hint.
If you specify several join hints, SQL Server query optimizer selects the least expensive join
strategy.
This example shows how you can use the LOOP JOIN hint:
SELECT * FROM titles a JOIN titleauthor b
ON a.title_id = b.title_id OPTION (LOOP JOIN)

Use the MERGE UNION hint if you need that all UNION operations will be performed
by merging UNION sets.
Keep in mind, that only the last query involving a UNION operator can have the OPTION clause.
This example shows how you can use the MERGE UNION hint:
SELECT 'warnings' as 'Severity', COUNT(*) FROM sys.messages WHERE severity < 11
UNION
SELECT 'user errors' as 'Severity', COUNT(*) FROM sys.messages WHERE severity BETWEEN 11 AND 16
UNION
SELECT 'software/hardware errors' as 'Severity', COUNT(*)
FROM sys.messages WHERE severity BETWEEN 17 AND 25 OPTION (MERGE UNION)

Consider using the OPTIMIZE FOR query hint to use a particular value for a
local variable when the query is compiled and optimized.
The OPTIMIZE FOR hint instructs the query optimizer to use a particular value for a local
variable only during query optimization, and not during query execution.

If your query is very slow, try the following before using query hints:
- rebuild indexes used in the query (or defragment them using DBCC INDEXDEFRAG),
- update statistics on the relevant tables,
- consider creating new indexes for this query,
- rewrite your query to provide better execution plan.
Use the query hints only when the actions above do not provide good performance.

SQL Server 2008 Table Hints

Sometimes your query executes very slowly. One of the ways to increase the queries performance
is using the SQL Server 2008 table hints. The table hints specify a locking method to be used by
the query optimizer with this table, specify the locking granularity for a table, or one or more
indexes to be used by the query optimizer when querying this table. If you want to use the table
optimizer hints, you should specify the WITH keyword with the hints list after the table name in
the FROM clause. You can specify several table hints and separate them by a comma.

If you want to set any table hints, do not remember to test the query with hint
and without the hint and compare results.
Because SQL Server 2008 query optimizer usually proposes the best execution plan, you should
test the query with table hint and use this hint only when it provides the best result. If the
table hint provides the better execution plan not stable, avoid using this hint and rely on the
SQL Server query optimizer.

Use the NOLOCK or READUNCOMMITTED table hint if you need the highest concurrency.
These hints specify that dirty reads can be allowed. When these hints are used, no shared locks
are issued and no exclusive locks are honored.
This example shows how you can use the NOLOCK table hint:
SELECT * FROM titles WITH (NOLOCK)

Consider using the READCOMMITTED table hint to specify that statements cannot
read data that has been modified but not committed by other transactions.
The READCOMMITTED table hint specifies that read operations comply with the rules for the
READ COMMITTED isolation level by using either locking or row versioning. When the database
option READ_COMITTED_SNAPSHOT is OFF (by default), the shared locks are used, when the
READ_COMITTED_SNAPSHOT is ON, the row versions are used instead of shared locks.
This example shows how you can use the READCOMMITTED table hint:
SELECT * FROM authors WITH (READCOMMITTED)

Use the REPEATABLEREAD table hint if you need more consistency, than default
(READCOMMITTED) and higher concurrency, than with SERIALIZABLE.
By default, SQL Server uses READ COMMITTED isolation level. With this isolation level SQL Server
uses shared locks while reading data. It ensures that not committed data will not be read, but it
not ensures that the data will not be changed before the end of the transaction. If you need the
current data will not be changed before the end of the transaction, you can use the REPEATABLEREAD
table hint.
This example shows how you can use the REPEATABLEREAD table hint:
SELECT * FROM authors WITH (REPEATABLEREAD)

Consider using the NOWAIT table hint if you need to return a message when a lock is
encountered on the table.
When the NOWAIT table hint is used, SQL Server 2008 returns a message as soon as a lock
is encountered on the table. Using this hint is equivalent to use SET LOCK_TIMEOUT 0 for
a specific table.
In this example, SQL Server 2008 will return a message when a lock is encountered on the
authors table:
SELECT * FROM authors WITH (NOWAIT)

Use the INDEX hint if you need to specify the name or ID of the indexes to be
used by SQL Server 2008 when processing the statement.
This example shows how you can use the INDEX hint:
SELECT au_fname FROM authors WITH (INDEX(0))
If a clustered index exists, INDEX (0) forces clustered index scan, if no clustered index
exists, INDEX (0) forces a table scan.
Note. You can use only one index hint per table, but multiple indexes can be used in the
single hint list.

Consider using the XLOCK table hint to take and hold exclusive locks until
the transaction completes.
When you use the XLOCK table hint with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks
apply to the appropriate level of granularity.
This example shows how you can use the XLOCK table hint with the PAGLOCK hint:
SELECT * FROM titles WITH (XLOCK, PAGLOCK)

Use the READPAST table hint if you need to return only the pasted rows.
When the READPAST table hint is used, the locked rows will be skipped.
This is the example to return only the pasted rows from the titles and titleauthor tables:
SELECT * FROM titles a JOIN titleauthor b
WITH (READPAST) ON a.title_id = b.title_id

Consider using the ROWLOCK table hint.
This hint specifies that a shared row lock will be taken instead of shared page or table lock.
The ROWLOCK provides the higher concurrency, but at the cost of lower performance because
SQL Server 2008 should hold and release locks at the row level.
This example shows how you can use the ROWLOCK table hint:
SELECT * FROM titles WITH (ROWLOCK)

Use the HOLDLOCK or SERIALIZABLE table hint if you need the highest consistency.
These hints specify shared locks will be holding until the completion of a transaction.
By default, the shared locks are released as soon as the required table or data page is
no longer needed. This results in higher consistency, but at the cost of lower concurrency.
This example shows how you can use the HOLDLOCK table hint:
SELECT * FROM titles WITH (HOLDLOCK)

Consider using the PAGLOCK table hint.
The PAGLOCK hint specifies that a shared page lock will be taken instead of table lock.
This example shows how you can use the PAGLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b
WITH (PAGLOCK) ON a.title_id = b.title_id

Consider using the UPDLOCK table hint.
The UPDLOCK hint specifies that update locks are taken while reading the table and that
they are held until the end of the Transact-SQL statement or end of the transaction.
This example shows how you can use the UPDLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b
WITH (UPDLOCK) ON a.title_id = b.title_id

Use the FASTFIRSTROW table hint if you need to quickly return first row.
You can quickly return first row and work with it, when the query continues execution and
produces its full result set. The FASTFIRSTROW table hint has the same effect as specifying
the FAST 1 query hint. Keep in mind, that when you use the FASTFIRSTROW hint the rest of
the rows are often returned more slowly that they would have been if the hint had not
been specified.
This is the example to quickly return first row from the titles and titleauthor tables:
SELECT * FROM titles a JOIN titleauthor b
WITH (FASTFIRSTROW) ON a.title_id = b.title_id

Consider using the TABLOCK table hint.
The TABLOCK hint specifies that a shared lock will be taken on the table until the end of
the Transact-SQL statement.
This example shows how you can use the TABLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b
WITH (TABLOCK) ON a.title_id = b.title_id

Consider using the TABLOCKX table hint.
The TABLOCKX hint specifies that an exclusive lock will be taken on the table until the end
of the Transact-SQL statement or the end of the transaction. The TABLOCKX provides the worst
concurrency, but, in this case, SQL Server 2008 will make minimum work to hold and release locks.
This example shows how you can use the TABLOCKX table hint:
SELECT * FROM authors WITH (TABLOCKX)

If your query is very slow, try the following before using table hints:
- rebuild indexes used in the query (or defragment them using the DBCC INDEXDEFRAG command),
- update statistics on the relevant tables,
- consider creating new indexes for this query,
- rewrite your query to provide better execution plan.
Use the table hints only when the actions above do not provide good performance.

Hide Database details from the SQL Server Management Studio

Hide Database details from the SQL Server Management Studio

Hiding User Database in Object Explorer in SQL Server Management Studio

1.Create a user account (testuser)(make sure its not mapped to any Database)
2.Right Click on the upper section of the SQL (SQLSERVER Name)>Properties>Permissions>Click on the user account, and select Deny to view databases.
3.Right Click on the newly created DB, Properties,Files, and change the Owner to the newly created account.

Run the below query

USE TestDB

ALTER AUTHORIZATION ON DATABASE::TestDB to testuser


USE MASTER

DENY VIEW ANY DATABASE TO TestDB

Hiding System Objects in Object Explorer in SQL Server Management Studio

1.In SQL Server Management Studio, under Tools menu, click Options
2. In the Options dialog box, expand Environment and then select the General tab Select Hide system objects in Object Explorer and then click OK
3. In the Microsoft SQL Server Management Studio dialog box, click OK to acknowledge that the changes will come into effect once you restart SQL Server Management Studio
4.Close SQL Server Management Studio and reopen it again you will not see the System Objects in Object Explorer in SQL Server Management Studio

Indexed View Limitations

There are several requirements that you must take into consideration when using Indexed views.
1. View definition must always return the same results from the same underlying data.
2. Views cannot use non-deterministic functions.
3. The first index on a View must be a clustered, UNIQUE index.
4. If you use Group By, you must include the new COUNT_BIG(*) in the select list.
5. View definition cannot contain the following
a. TOP
b. Text, ntext or image columns
c. DISTINCT
d. MIN, MAX, COUNT, STDEV, VARIANCE, AVG
e. SUM on a nullable expression
f. A derived table
g. Rowset function
h. Another view
i. UNION
j. Subqueries, outer joins, self joins
k. Full-text predicates like CONTAIN or FREETEXT
l. COMPUTE or COMPUTE BY
m. Cannot include order by in view definition

Find all SQL Objects with QUOTED_IDENTIFIER set to OFF

--Find all SQL Objects with QUOTED_IDENTIFIER set to OFF

DECLARE @Type NVARCHAR(500),
@StrSql NVARCHAR(MAX)
SET @StrSql = ''

SET @Type = '''P'', ''TR'', ''V'', ''IF'', ''FN'', ''TF'''

SET @StrSql ='SELECT
SCHEMA_NAME(schema_id) AS [Schema], Name
FROM
sys.objects
WHERE
type IN (' + @Type + ') AND
OBJECTPROPERTY(object_id, ''ExecIsQuotedIdentOn'') = 0
ORDER BY SCHEMA_NAME(schema_id),Name'

EXEC sp_executesql @StrSql