General

How to Get the Week Number from a Date in SQL Server?
To get the week number from a date in SQL Server, you can use DATENAME Built-in Function:
SELECT DATENAME(ww, GETDATE())

How to Get the Day of the Week from a Date in SQL Server?
To get the date of the week from a date in SQL Server, you can use DATENAME Built-in Function:
SELECT DATENAME(dw, GETDATE())

How to Determine a Leap Year or not in SQL Server by using User Defined Function (UDF)?

A leap year is a year containing one additional day and lasts 366 days instead of the usual 365.
Rule to determine if a year is a leap year:
– leap year is any year that is divisible by 400 (2000, 2400 are leap years…) or by 4 (2004, 2008 are leap years…) but it must not be divisible by 100 (2100, 2200 are NOT leap years…)

We will create a User Defined Function (UDF) that accepts a DATETIME value and checks if a year is a leap year:

CREATE FUNCTION [dbo].[Udf_Rajendra_LeapYearCheck] (@MyDate DATETIME)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnString varchar(50)
SET @ReturnString = CASE WHEN (YEAR(@MyDate) % 4 = 0 AND YEAR(@MyDate) % 100 != 0)
OR YEAR(@MyDate) % 400 = 0
THEN 'The year is a leap year'
ELSE 'The year is NOT a leap year'
END
RETURN @ReturnString
END
GO

To use this user-defined function:
SELECT dbo.[Udf_Rajendra_LeapYearCheck] ('2015-06-30')

How to select only date or time part from DateTime in SQL Server

When you run following command:

SELECT GETDATE()

you will get a result similar to this:

2012-05-25 20:24:17.590

with time included along with date.

To select only date part from DateTime in SQL Server run one of these commands, depending of the output format needed (for date example May 25th 2012):

To select only time part from DateTime in SQL Server run one of these commands, depending of the output format needed:

Query to find the users and their permissions in SQL Server Database ?

SELECT u.name as user_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS u
ON p.grantee_principal_id = u.principal_id

How to give Grant permissions to users to create jobs in SQL Server ?

There are roles in MSDB database that help database administrators to have better granular control over job creation, execution, and browsing:
SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.

To grant users permission to create and schedule their own jobs, use this script:

USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [LoginName]
GO
USE [msdb]
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'UserName'
GO

To grant a domain group permission to create and schedule their own jobs, use this script:

USE [msdb]
exec sp_addrolemember 'SQLAgentUserRole', 'DomainName\GroupName'

Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.

How to list all database files in SQL Server ?

SELECT name, physical_name AS current_file_location,*
FROM master.sys.master_files
                       (OR)
SELECT DB_NAME([dbid])AS [Database Name], fileid, [filename]
FROM sys.sysaltfiles
WHERE [dbid] > 0 AND [dbid] <> 32767
OR [dbid] = 2;

How to Disable and Enable Index in SQL Server

To disable an index in SQL Server use the following command:
USE Database_Name
GO
ALTER INDEX Index_Name ON Table_Name DISABLE;
GO

To enable a disabled index, use the following command (REBUILD enables a disabled index):
USE Database_Name
GO
ALTER INDEX Index_Name ON Table_Name REBUILD;
GO

How to list all Disabled indexes in a SQL Server database

USE Rajendra --Database Name
GO
SELECT i.name AS Index_Name, i.index_id, i.type_desc, s.name AS 'Schema_Name', o.name AS Table_Name
FROM sys.indexes i
JOIN sys.objects o on o.object_id = i.object_id
JOIN sys.schemas s on s.schema_id = o.schema_id
WHERE i.is_disabled = 1
ORDER BY
i.name
GO

How to enable, disable and check if Service Broker is enabled on a database?

SELECT name, is_broker_enabled FROM sys.databases

ALTER DATABASE test SET ENABLE_BROKER;

ALTER DATABASE test SET DISABLE_BROKER;

How to Check Failed Jobs in SQL Server?

Using Below SQL query to get failed job history in SQL Server

SELECT  'Job: ' + Job.[name] as failedjobs, Hst.[sql_message_id], Hst.[message] , Hst.[run_date], Hst.[run_time],'Hist', hst.*
FROM [msdb].dbo.sysjobhistory Hst
INNER JOIN [msdb].dbo.sysjobs Job ON Hst.[job_id] = Job.[job_id]
where hst.run_status = '0'   -- 0 = FAILED
and  convert(varchar(8), GETDATE(),112) = Hst.[run_date]
ORDER BY Job.[name],Hst.[run_date] DESC, Hst.[run_time] DESC

Important SQL Queries

1) The following SQL query will show the HostName , SQL Instance Name , SQL edition ,SQL Product Level, Standalone or Clustered type and SQL version info
SELECT
SERVERPROPERTY('MachineName') as HostName,
SERVERPROPERTY('InstanceName') as InstanceName,
SERVERPROPERTY('Edition') as Edition,
SERVERPROPERTY('ProductLevel') as ProductLevel
Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
'STANDALONE' end as ServerType,
@@VERSION as SQLVersionDetails
2  The below SQL  query will show all of the information related to Server level configuration and sql performance related information .
SELECT * from sys.configurations order by NAME
3  Below SQL query will show the info of sql security .
SELECT sl.name, sl.denylogin, sl.isntname,sl.isntgroup, sl.isntuser
  FROM master.dbo.syslogins sl
WHERE sl.sysadmin = 1 OR sl.securityadmin = 1
4  Below SQL query will show the information of database name , compatibility level ,database recovery model and database state , is it online or offline .
SELECT name,compatibility_level,recovery_model_desc,state_desc FROM sys.databases
5. This sql query will provides the logical name and the physical location of the data/log files of all the databases available in the current SQL Server instance
SELECT db_name(database_id) as DatabaseName,name,type_desc,physical_name FROM sys.master_files
6) The database may contain filegroups other than the primary file group. The following sql query  gets executed in each database on the server and displays the file groups related results
EXEC master.dbo.sp_MSforeachdb @command1 = 'USE [?] SELECT * FROM sys.filegroups'
7) The following SQL query lists all of the databases in the server and the last day the backup happened. This will help the database administrators to check the backup jobs and also to make sure backups are happening for all the databases
SELECT db.name, case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100), MAX(b.backup_finish_date)) end AS last_backup_finish_date FROM sys.databases db LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D' WHERE db.database_id NOT IN (2) GROUP BY db.name ORDER BY 2 DESC
8)The below sql query gets all the information related to the current backup location from the msdb database.
SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily
9) SQL Server database administrators used sp_who and sp_who2 to check the current users, process and session information. These statements also provided information related to cpu, memory and blocking information related to the sessions
sp_who
Sp_who2
10)  Using below query to find out the all databases file location on sql instance.
SELECT DB_NAME([dbid])AS [Database Name], fileid, [filename]
FROM sys.sysaltfiles
WHERE [dbid] > 0 AND [dbid] <> 32767
OR [dbid] = 2;