SQL SERVER – Order of Statement Execution

The order in which a query is written is not the order in which it is evaluated by SQL Server.
The query is evaluated by SQL server in following order:

5: SELECT   {select list}
1: FROM     {table source}
2: WHERE    {search condition}
3: GROUP BY {group by list}
4: HAVING   {search condition}
6: ORDER BY {order by list}

SQL SERVER - Search text inside SQL

We can find a text or a string inside SQL Server stored procedures / triggers / views / functions using the below function:

CREATE FUNCTION [dbo].[SearchObject]
(
    @str VARCHAR(100)
)
RETURNS TABLE 
RETURN
    SELECT 
        DISTINCT 
        QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) AS ObjectFullName,
        s.name AS SchemaName,
        o.name AS ObjectName, 
        o.type_desc AS ObjectType
    FROM sys.objects o
    INNER JOIN syscomments c ON c.Id = o.object_id
    INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE 
    is_ms_shipped = 0 AND c.text like '%' + @str + '%'

SELECT * FROM [dbo].[SearchObject]('split')

SQL SERVER - SEQUENCE in SQL SERVER 2012

SEQUENCE is user-defined object that has start value, increment value and end value defined in them and this sequence can be added to a column whenever required rather than defining an identity column individually for tables. It generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences are not associated with specific tables like identity columns. The relationship between sequences and tables is controlled by the application.
A sequence can be defined as any integer type. Also the following types are allowed: tinyint, smallint, int, bigint, decimal and numeric with a scale of 0. Any user-defined data type (alias type) that is based on one of the allowed types.
If no data type is provided, the bigint data type is used as the default.

There are different ways to create SEQUENCE, like:

1. SQL Server Management Studio
2. TSQL statement

Let us take a quick look to create a SEQUENCE using TSQL Statements. It can be created using CREATE SEQUENCE Syntax

---- Create a SEQUENCE object on schema "dbo" by the name of TEST_Sequence
CREATE SEQUENCE SEQ_MyTestSequence  
AS INT  
START WITH 1  
INCREMENT BY 1  
MINVALUE 0  
NO MAXVALUE  

---- Create table Customer
CREATE TABLE Customer
(
CustomerID  INT NOT NULL,
CustomerName VARCHAR(100)
)
GO

Now, Populate Customer table using SEQ_MyTestSequence to generate the CustomerId column:

INSERT INTO Customer(CustomerID, CustomerName)
VALUES
(NEXT VALUE FOR SEQ_MyTestSequence, 'JOHN'),
(NEXT VALUE FOR SEQ_MyTestSequence, 'ANTHONY'),
(NEXT VALUE FOR SEQ_MyTestSequence, 'JUANA')

Lets us look at results, which we inserted using SEQUENCE

SELECT * FROM Customer

We should note that, sequence are global to a database and not dependent on tables. Let us create a new table named Account and use the same sequence which we used for Cutomer table in past to understand.

--- Create a new table called Account

CREATE TABLE Account(
[AccountID] INT NOT NULL,
[AccountNumber] [varchar](6) NULL
) 
GO

INSERT INTO Account(AccountID,AccountNumber)
VALUES
(NEXT VALUE FOR SEQ_MyTestSequence, '000001'),
(NEXT VALUE FOR SEQ_MyTestSequence, '000002')
GO

SELECT * FROM Account

In above result, NEXT value of SEQUENCE has given a next number of SEQUENCE. Last number was 3 that was used in Customer)

Every time when we use NEXT VALUE FOR SEQ_MyTestSequence, it will give a new value. for example selecting or printing NEXT value of SEQUENCE Every time we will get a New Value

SELECT NEXT VALUE FOR SEQ_MyTestSequence 

Let us see how to check the current value of the SEQUENCE:

SELECT current_value FROM sys.sequences WHERE name = 'SEQ_MyTestSequence'

With the help of following statement we can alter a SEQUENCE:

ALTER SEQUENCE SEQ_MyTestSequence
RESTART WITH 101

In above code, we have altered a SEQUENCE and started with 101, let us check it by running the following code

SELECT current_value FROM sys.sequences WHERE name = 'SEQ_MyTestSequence'

We can change the MAX and MIN value of a SEQUENCE:

ALTER SEQUENCE SEQ_MyTestSequence
MINVALUE 101
MAXVALUE 1011
CYCLE

Above code will alter SEQUENCE and specify start as 101 and MAX as 1011, once the MAX Value is reached, SEQUENCE numbers will be restarted from MINVALUE.

Now, verify MAX value and CYCLE Clause

SELECT *,NEXT VALUE FOR SEQ_MyTestSequence as NextId
FROM Account 

We can drop a SEQUENCE with the help of following statement:

DROP SEQUENCE SEQ_MyTestSequence 

Reference: http://msdn.microsoft.com/en-us/library/ff878091.aspx

SQL SERVER - Different ways to know when was SQL Server restarted

Following are the ways to get the timestamp when SQL Server started:

Method 1: In SQL Server 2008 and above, with the DMV sys.dm_os_sys_info returns a column called sqlserver_start_time. This column contains the date and time SQL Server was last started:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info 

Method 2:

SELECT start_time FROM sys.dm_exec_requests WHERE (session_id = 1) 

Method 3:

SELECT MIN(login_time) FROM sys.sysprocesses 

Method 4: TempDb is created every time SQL Server is started, so another workaround is to look at the creation date of the TempDb database:

SELECT create_date FROM sys.databases WHERE name = 'tempdb' 

OR

SELECT crdate FROM sysdatabases WHERE name='tempdb' 

Method 5:

SELECT start_time FROM sys.traces WHERE is_default = 1 

Method 6: Dashboard Reports
We can use the SQL Server Dashboard Report in SQL 2008 and above. Right click on the server name in SSMS and select Reports -> Server Reports -> Server Dashboard and you will get a report in which we can find Server Start Time.

Method 7: Windows Event Viewer
Windows Event Viewer is also an option to look for the start up time. If we open Windows Event Viewer and filter Event Sources for the SQL Server instance you can find all of the start up times that are still in the Event Viewer log.

Method 8: SQL Server Error Log
If you open the current SQL Server error log in SSMS under Management > SQL Server Logs and scroll to the bottom of the error log you can see when the server was started. Note the text "SQL Server is starting". You will want to look for this to make sure the error log was not cycled and give you a false impression of the start time. The error log method can be used for any version of SQL Server.

SP_ReadErrorLog 0,1,'Copyright (c)' 

SQL SERVER - Dynamic sorting using CASE statement

Let us look at the different examples to sort a field dynamically based on parameter supplied by the user in SQL Server. We can implement this by using dynamic SQL where we create a query on the fly and execute it, but dynamic SQL has many issues and very few advantages. If we are willing to limit ourselves to a fixed number of columns to sort then it is possible to achieve without dynamic SQL otherwise completely dynamic sorting by using several CASE statements will be the solution. We can write these types of queries using CASE statement with ORDER BY clause.

Following examples will demonstrate to use several CASE statements in ORDER BY to sort:

-- -- First, create a new database and a table to test
GO
CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO
CREATE TABLE dbo.Employees (
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
SELECT 'Barry','IT', 80000 UNION
SELECT 'Jones','IT', 40000 UNION
SELECT 'Billy','IT', 50000 UNION
SELECT 'Ralph','IT', 50000 UNION
SELECT 'Aaron','Finance', 40000 UNION
SELECT 'Barney','Finance', 25000 UNION
SELECT 'Callum','Finance', 25000 UNION
SELECT 'Oliver','Finance', 15000 UNION
SELECT 'Anderson','HR', 25000 UNION
SELECT 'Martin','HR', 15000 UNION
SELECT 'Garcia','HR', 15000 UNION
SELECT 'Caine','HR', 10000

Now create stored procedures to get sorted records from Employees table based on given field.

-- 1 -- Decide field to sort but without dynamic sort order.
CREATE PROCEDURE dbo.GetAllEmployees_1
(
@SortField VARCHAR(50) = 'EmployeeName'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE
WHEN @SortField = 'EmployeeName' THEN EmployeeName
ELSE @SortField
END
ASC

END
GO

-- Execute the stored procedure created above:

EXEC dbo.GetAllEmployees_1 'EmployeeName'

-- 2 -- User option to sort on any field and sort in ascending or descending order method 1.
CREATE PROCEDURE dbo.GetAllEmployees_2
(
@SortField VARCHAR(50) = 'EmployeeName',
@SortDir VARCHAR(4) = 'ASC'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'ASC' THEN EmployeeName END
ASC,
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'DESC' THEN EmployeeName END
DESC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'ASC' THEN Department END ASC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'DESC' THEN Department END
DESC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'ASC' THEN Salary END ASC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'DESC' THEN Salary END DESC

END
GO

-- Execution of the following stored procedures will show the records sorted based on given field and based on given sort order:

EXEC dbo.GetAllEmployees_2 'EmployeeName', 'ASC'
EXEC dbo.GetAllEmployees_2 'EmployeeName', 'DESC'

-- 3 -- User option to sort on any field and sort in ascending or descending order method 2
CREATE PROCEDURE dbo.GetAllEmployees_3
(
@SortField VARCHAR(50) = 'EmployeeName',
@SortDir VARCHAR(4) = 'ASC'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'ASC' THEN EmployeeName END
ASC,
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'DESC' THEN EmployeeName END
DESC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'ASC' THEN Department END ASC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'DESC' THEN Department END
DESC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'ASC' THEN Salary END ASC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'DESC' THEN Salary END DESC

END
GO

-- Execute the following code to test the above created stored procedure:

EXEC dbo.GetAllEmployees_3 'EmployeeName', 'ASC'
EXEC dbo.GetAllEmployees_3 'EmployeeName', 'DESC'

Sometimes want to display records based on priority that also could done by dynamic sorting using CASE statement. In the following example we will sort the field Department based on priority where department “IT” should be first then “Finance” and then “HR”.

-- 4 -- Order by priority.
CREATE PROCEDURE dbo.GetAllEmployees_4
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE
WHEN Department = 'IT' THEN 1
WHEN Department = 'Finance' THEN 2
WHEN Department = 'HR' THEN 3
END ASC

END
GO

-- Execute the following code to test:
EXEC dbo.GetAllEmployees_4

In above example, the data type must be the same for sort field. In this example the data type is of integer. This can be helpful where in some situations we want display data based on priority.

SQL SERVER - Single DROP statement to drop multiple tables

We can drop multiple tables using a single DROP statement. For example, we have three tables Table1, Table2, Table3, we want to drop all these tables, and we can do it by following statement:

DROP TABLE Table1, Table2, Table3

Following is an example to drop multiple tables using single DROP statement:

CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO

CREATE TABLE [dbo].[Table1](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] [varchar](6) NULL
) 
GO

CREATE TABLE [dbo].[Table2](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column2] [varchar](6) NULL
) 
GO

CREATE TABLE [dbo].[Table3](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column3] [varchar](6) NULL
) 
GO

We have created three tables above in database MyTestDB, now we can drop all three table by:

DROP TABLE Table1, Table2, Table3

-- Clean up 
USE MASTER
GO
DROP DATABASE MyTestDB

SQL SERVER - View Query Editor Window In Full Screen Mode

We can view query window in full screen mode by enabling Full Screen mode.It will hide all tool windows and view only document windows. Click anywhere in the Query Editor window to activate the window.

To enable Full Screen mode:

Press ALT + SHIFT + ENTER to toggle Full Screen mode.
OR
Go to View menu >> Select Full Screen Option

To disable Full Screen mode:
Press "Full Screen" button located at top in the menu bar.

By this way we can view SQL Server Management Studio in full screen mode.