Find SQL Services running on a Instance

dm_server_services is a useful system view which provides detail about SQL Services running on a instance and other good feature is that it shows even the cluster node on which SQL Server services are running.

Query
SELECT servicename, 
  startup_type_desc, 
  status_desc, 
  last_startup_time, 
  service_account, 
  is_clustered, cluster_nodename
FROM sys.dm_server_services

Output 
servicename,startup_type_desc,status_desc,last_startup_time,service_account,is_clustered,cluster_nodename
SQL Server (MSSQLSERVER),Automatic,Running,2013-05-31 23:31:38.7849183 -07:00,domain\user,N,NULL
SQL Server Agent (MSSQLSERVER),Automatic,Running,2013-05-31 23:31:46.0190158 -07:00,domain\user,N,NULL

Find Number of Days in a Given Month

How many lines of code do you need to write to get number of days in a given month , there is no handy function available in SQL Server which does the job.

Say 12 If statements or combine Month which has 30 and 31 with just IF Statements .. And how about February which may have 28 or 29 depending on leap year or not.

Below mentioned code returns data with single statement when a date is provided as input.

SET NOCOUNT ON

DECLARE @dt DATETIME
SELECT  @dt = '2012-07-01'
SELECT DAY(DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt))-1) AS DaysInGivenMonth


/*
--Section below explains logic step of step how this is calculated...

SELECT DAY(@dt) 'Day from the date specified'
SELECT 1-DAY(@dt) 'One day before the date'
SELECT DATEADD(DAY,1-DAY(@dt),@dt) 'Find the start of month'
SELECT DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt)) 'Find prev month'
SELECT DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt))-1 'Subtract one day'

SET NOCOUNT OFF
*/

In SQL 2012

DECLARE @dt DATETIME
SELECT  @dt = '2012-07-01'
SELECT DATEPAT(dd,EOMONTH (@dt))

What is Collation and is it that Important ?

Collation is defined as set of rules which determines how data comparison and data sorting would take place. Character data is sorted using correct character sequence and with options for specifying case and accent sensitivity.

Example:
For case sensitive rule, character A and a are different as it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97.

For accent sensitive rule, character "ä” and "a" are different as it uses ASCII code to differentiate the input. The ASCII value of a is 97 and ä is 225.

Levels:
First Level , specified at instance level during SQL Installation.

Second level , when we create a database, one can specify the default collation used for the database. If you don’t specify a collation, database is created with the default collation of the instance.

Third level, when we define a character column, you can specify its collation. If you do not specify a collation, the column is created with the default collation of the database.

We cannot specify collation for character variables and are always created with the default collation of the database.

General Recommendation:
System Databases collation cannot be changed directly and would require rebuild or reinstall of SQL Server system databases which would need downtime.

Database or column Collation could be changed at any point of time without downtime , but best practice is to keep user database in same collation as System Databases.

Name (required)

Email Address (required, will not be published)

Website Address

<%= commentBody %>