NOT IN and NULL ( a dangerous combination)

Since NULL represents an unknown value, looking for values in the outer table that are not unknown (i.e. employees’ ids that are not in a list that contains NULL values) would return 0 rows.

ALTER USER DEFINED DATATYPE

We cannot alter the User-defined types after creation.

PROCEDURE

CIS Benchmark : Center for internet security

1 Installation, Updates and Patches
 Ensure Latest SQL Server Service Packs and Hotfixes are Installed

SELECT SERVERPROPERTY('ProductLevel') as SP_installed, SERVERPROPERTY('ProductVersion') as Version;

2. Ensure Single-Function Member Servers are Used

Ensure that no other roles are enabled for the underlying operating system and that no excess tooling is installed, per enterprise policy.

3.Surface Area Reduction 

Ensure 'Ad Hoc Distributed Queries' Server Configuration Option is set to '0'

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Ad Hoc Distributed Queries';

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE; EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
 GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

4 Ensure 'CLR Enabled' Server Configuration Option is set to '0'

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'clr enabled';

 EXECUTE sp_configure 'clr enabled', 0; RECONFIGURE;

Move Database

(A) Move USER database :3 methods

1 Backup and restore on new location

2. attach and detach database

3. Alter Command

First check current location

sp_helpdb 'database name'
or
SELECT name,physical_name FROM AdventureWorks.sys.database_files

ALTER DATABASE AdventureWorks SET OFFLINE

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Data, FILENAME ='D:\MSSQL\Data\databasename.mdf');
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME ='D:\MSSQL\Log\databasename_Log.ldf');

ALTER DATABASE AdventureWorks SET ONLINE

(B) Move master Database to another drive

(C) Move Temp Database to another drive  

SQL Query Performance

common issues with performance such as:

• deadlocks
• blocking
• missing and unused indexes
• I/O bottlenecks
• poor query plans
• statistics
• wait stats
• fragmentation

Tools to collect performance related data.

• Dynamic Management Views (DMVs) and System Catalog Views
• Profiler and Server Side Traces
• Windows Performance Monitor
• Built in performance reports in SSMS
• Query Plans
• Database Tuning Advisor