Collation levels and default collation

Collation levels and default collation:

Collation can be set at 4 levels in SQL Server:
1.       Server
2.       Database
3.       Columns
4.       Expression

But collations are compared either at columns level or in expressions. So I am starting from lower to upper levels. The collations of upper two levels (server and database) are only helpful in providing default collation to columns and expressions.
Expression collation: All literals, variables and parameters and functions without any input parameters, by default get the collation of database.

Column collation: When we create a table we can specify a collation explicitly for all character data type (char, nchar, varchar, nvarchar, text, ntext) columns. If not specified then collation of database would be the default collation of a column. The collation of a column can be changed by using an ALTER TABLE statement similar to the following:

ALTER TABLE TestTab ALTER COLUMN CharCol CHAR(10) COLLATE Greek_CS_AI

Database collation: When we create a new database we can specify collation. If not specify then collation of model database is assigned as default collation. To know the collation of database use below statement:

SELECT DATABASEPROPERTYEX('testDB', 'Collation') SQLCollation

Database collation can be changed using the ALTER DATABASE statement as below.:

ALTER DATABASE myDB COLLATE Greek_CS_AI

When collation of database is changed it does not automatically change the collation of all columns of all tables. But the new collation would be the default collation for new columns created hence forth and for expressions.

Server collation: This is set during SQL Server installation. It’s the default collation for system databases. Because this is the collation of model database so this would be the default collation for all new databases that would be created on this server. The server collation can not be changed. To query the server collation use the below statement:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'))

Executing a SQL statement from command prompt:

Executing a SQL statement from command prompt:

Sometimes we need to execute some –T-SQL script from command prompt. This is a common requirement when some t-sql scripts are needed to be scheduled by windows scheduler.

Here I will explain how we can execute T-SQL script from command prompt. Below I created an executable file (.bat) to create a backup of AdventureWorks database so that I can schedule this executable using Windows scheduler.

 Create a text file for example c:\sql.txt and write below backup script in it:

USE master

GO

BACKUP DATABASE AdventureWorks

TO DISK = 'D:\SQL_Backup\AdventureWorks.bak' WITH INIT

Create another batch file for example Backup.bat and write the below statement inside it:

sqlcmd -S ServerName -U yourLogin -P yourPassowrd -i c:\sql.txt

Now we can execute this Backup.bat file from command prompt or can schedule using windows scheduler. Thus we have scheduled a task that is not dependent on SQL server Agent service.

Find who dropped, created or altered the table, SP or any object

Find who dropped, created or altered the table, SP or any object

If any database object is dropped, created or altered accidentally, sometimes you may need information about:

Who dropped, created or altered the object? When culprit not accept the mistake.

When the object was dropped, created or altered? Especially drop time of table is required for point in time recovery from database backups.

These changes are not recorded in SQL Server Error Log but are recorded in default trace. If you have not disabled the default trace and started looking into the issue soon after change occurred you could get this information. The path of trace file is “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\”. The folder MSSQL10.SQLEXPRESS may be different according to your SQL Server instance name. Here you would find 5 profiler trace files named as “log_1”, “Log_2”, etc. The file number is increased by time (in following example I used “log_16.trc”). Find the file that is most recently modified and use the below t-sql query to open that file:

SELECT ObjectName, DatabaseName, StartTime, NTDomainName, HostName, NTUserName, LoginName

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\log_16.trc', default)

WHERE objectname is not null  

You can start a trace as default in two ways:

1. create a stored procedure that starts a trace# and then set that SP as startup as following:

CREATE PROC sp_StartTrace
AS
DBCC TRACEON (1204) 
GO

sp_procoption sp_StartTrace , 'STARTUP', 'ON'

2. Start SQL Server at Run window using NET START command with -T switch as below:
net start SQLSERVER -T 1204