SQL SERVER - Display Line Numbers In Query Editor Window

We can display line numbers in query editor window . Line numbers are useful for navigating code in SQL Server Management Studio

Go to menu Tools >> select Options >> Click Text Editor >> Click All Languages >> Click General. Select Line numbers in Display section.

To view line numbers in only some programming languages, select Line Numbers in the appropriate folder.

It should be noted that, document will not be printed with line numbers by turning on line numbering. For line numbers to print, we must select the Include Line Numbers check box in the Print command on the File menu.

Reference: Enabling Full Screen Mode

SQL SERVER - Add New Column In Existing Table With Default Value

Many times, we need to add new column in our existing database table. Many of us prefer to use a table designer to add new column, I would like to use SQL query to do this operation. If we add new column through designer it will not update the default value for that column and we need to update that column. With the help of following query, we can add new column to our existing table with default value we want to assign to that column without executing update statement.

ALTER TABLE [dbo].[Account]
ADD Active BIT DEFAULT 1 WITH VALUES;

Following is the complete example to test the above SQL statement.

USE [Master]
GO
CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO
CREATE TABLE [dbo].[Account](
 [AccountID] INT IDENTITY(1,1) PRIMARY KEY,
 [AccountNumber] [varchar](6) NULL,
 [BranchName] [varchar](6) NULL,
 [Balance] [numeric](8, 2) NULL
) 
GO

INSERT INTO dbo.Account(AccountNumber,BranchName,Balance)
SELECT '000001','Br001',1202.00
UNION SELECT '000002','Br002',904.00

SELECT * FROM dbo.Account

ALTER TABLE [dbo].[Account]
ADD Active BIT DEFAULT 1 WITH VALUES;

SELECT * FROM dbo.Account

DROP TABLE [dbo].[Account]

USE MASTER
GO
DROP DATABASE MyTestDB

In above example, it will add a new column ‘Active’ with default value 1 for all the rows in table ‘[dbo].[Account]'. The above example shows that we can add new column in existing table with default value in SQL SERVER using ALTER TABLE statement.

SQL SERVER - View full content of a text or varchar(MAX) column

Recently I came across with a problem where I had to debug the dynamic SQL stored in large dynamic SQL varchar datatype variable and could not select in the result grid or print the whole SQL script because of SQL server limitation of displaying maximum number of text when dealing with large dynamic SQL variables.

Therefore, I started to search the web and found one tricky solution. Here I want to share that solution for such kind of problem.

DECLARE @str AS VARCHAR(MAX)
SELECT @str = REPLICATE(CAST('A' AS VARCHAR(MAX)), 65535)

SELECT [processing-instruction(X)] = @str 
FOR XML PATH(''),TYPE

This will convert the dynamic SQL to XML, instead of printing the dynamic SQL to the message tab. XML
The column name [processing-instruction(x)] sends special XML instruction allowing the text to be converted, along with any special characters. XML also keeps the formatting and can store up to 2 GB of data. It should be noted that whatever value you put in parenthesis would be incorporated in the XML tags, in this case “X”. We can strip of the XML tags from the top and bottom of the query text and go ahead with debugging.

Reference: Allow large text to be displayed in as a link

SQL SERVER - Find Currently Running Queries

Following is the SQL script to list out which queries are currently running on SQL server database.

SELECT 
 OBJECT_NAME(t.ObjectID) as ObjectName,
 SUBSTRING(t.text,(statement_start_offset/2)+1,
  (( CASE statement_end_offset
   WHEN -1 THEN DATALENGTH(t.text)
   ELSE statement_end_offset
  END - statement_start_offset)/2) + 1
 ) AS StatementText,
 db_name(database_id) as DatabaseName,
 req.blocking_session_id as blocking_session_id,
 req.session_id,
 req.start_time,
 req.[status],
 req.command,
 req.open_transaction_count,
 req.percent_complete,
 req.cpu_time,
 req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) AS t

GO

The above query helps to identify queries that are running for long time so that an appropriate action can be taken.

more about sys.dm_exec_requests
more about sys.dm_exec_sql_text

SQL Server - Convert Comma Separated String to Table Column

In previous post I mentioned different ways to get comma separated values (CSV) from a table column.

Following are the different ways to convert comma separated string to column:

-- -- Using while loop: 

DECLARE @strCSVString VARCHAR(4000)

SELECT @strCSVString = '1,2,3,4,5'

DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

DECLARE @str VARCHAR(20)

DECLARE @idx Int

IF ISNULL(@strCSVString,'') <> ''

BEGIN

    SET @idx = CHARINDEX(',' , @strCSVString)

    WHILE @idx > 0

    BEGIN

          SET @str = SUBSTRING(@strCSVString, 1, @idx - 1)

          SET @strCSVString = SUBSTRING(@strCSVString, @idx + 1, LEN(@strCSVString) - @idx)

          INSERT INTO @Result values (@str)

          SET @idx = CHARINDEX(',' , @strCSVString)

    END

    SET @str = @strCSVString

    INSERT INTO @Result values (@str)

END

SELECT * FROM @Result 

GO

-- -- Using Common Table Expression (CTE): 

DECLARE @strCSVString VARCHAR(4000)

SELECT @strCSVString = '1,2,3,4,5'

DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

; WITH CTE(Start, [Stop]) AS

(

  SELECT  1, CHARINDEX(',' , @strCSVString )

  UNION ALL

  SELECT  [Stop] + 1, CHARINDEX(',' ,@strCSVString  , [Stop] + 1)

  FROM CTE

  WHERE [Stop] > 0

)

INSERT INTO @Result

SELECT  SUBSTRING(@strCSVString , Start, CASE WHEN stop > 0 THEN [Stop]-Start ELSE 4000 END) AS stringValue

FROM CTE

        

SELECT * FROM @Result 

GO

-- -- Using XML: 

DECLARE @strCSVString VARCHAR(4000)

SELECT @strCSVString = '1,2,3,4,5'

DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

DECLARE @xmlData XML 

SELECT @xmlData = CAST('<Col>'+ REPLACE(@strCSVString,',','</Col><Col>')+ '</Col>' AS XML)

INSERT INTO @Result            

SELECT d.value('.', 'int') AS Val

FROM @xmlData.nodes('/Col') AS x(d)

SELECT * FROM @Result 

GO

We can use the above code by creating a function and can change the delimiter string from comma to any valid character  by having a parameter in function.

For example, let's create a funtion, dbo.Split() that converts comma delimited string in to a table column.

CREATE FUNCTION [dbo].[Split]

(

@strCSVString nvarchar(2000),

@Delimiter nvarchar(5)

)  

RETURNS @Result TABLE (ColumnValue NVARCHAR(100)) 

AS  

BEGIN

  ;WITH CTE(Start, [Stop]) AS

(

 SELECT  1, CHARINDEX(@Delimiter , @strCSVString )

 UNION ALL

 SELECT  [Stop] + 1, CHARINDEX(@Delimiter, @strCSVString, [Stop] + 1)

 FROM CTE

 WHERE [Stop] > 0

)

INSERT INTO @Result

SELECT  SUBSTRING(@strCSVString , Start, CASE WHEN stop > 0 THEN [Stop] - Start ELSE 4000 END) AS StringValue

FROM CTE

 RETURN

END

GO

-----------------

SELECT * FROM  dbo.[Split]('1,2,3,4,5' , ',')

SELECT * FROM  dbo.[Split]('1|2|3|4|5' , '|')

-----------------

Output:

The all above queries will give the same result as:

SQL SERVER - Questions with answers - 1

Question:

What is PRIMARY KEY?

Answer:

The PRIMARY KEY is a field or column that uniquely identifies each record of the table, each table can have only one key declared as a primary key. We can have PRIMARY KEY constraint on more than one column, so duplicate values allowed in one of the columns, but the combination of values from all the columns in the primary key must be unique. By default, primary key creates clustered index.

Question:

What is FOREIGN KEY?

Answer:

A FOREIGN KEY is a column or combination of columns used to establish and enforce a link between the data in two tables. It ensures that the values that can be entered in a particular column (foreign key) exist in a specified table (primary key). A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table, this column becomes a foreign key in the second table. Therefore, we can say that a FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Question:

What are COMPOSITE KEY, CANDIDATE KEY and ALTERNATE KEY?

Answer:

A key formed by combining at least two or more columns is called a composite key. When multiple fields used as a primary key, they are called a COMPOSITE KEY. One primary key contains more than one column.

A CANDIDATE KEY is one that can identify each row of a table uniquely. A candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called ALTERNATE KEYs.

Question:

What is the difference between a PRIMARY KEY and a UNIQUE key?

Answer:

Primary keys do not allow NULL values, but unique keys allow one NULL value.

A primary key creates a clustered index on the column and unique keys create a non-clustered index, by default.

We can define only one primary key per table.

We can have more than one UNIQUE constraint per table.

Primary and unique keys enforce uniqueness of the column on which they are defined.

You can use primary key constraints to enforce uniqueness as well as referential integrity.

Primary key defined on a table helps to relate that table to other tables. These relationships help to normalize a database.

Question:

What is NORMALIZATION?

Answer:

Normalization is the process of systematically organizing the data in a database. Normalization process consists of eliminating redundant data by storing the same data in more than one table, and ensuring data dependencies by only storing related data in a table.

Normalization helps to reduce redundancy of data and to make sure that all tables are properly dependent.

The characteristic of a normalized database is the number of small tables join together and produce the required details.

To normalizing the logical database design using formal methods to split the data into multiple and number of related tables.

Reasonable normalization also improves the performance query. When useful indexes are available, and efficient joins between tables are used boost up performance.

Normalization is part of successful database design, without normalization, database systems can be inaccurate, inefficient, and slow they might not produce the data you expect.

Read More...

Question:

What is DENORMALIZATION?

Answer:

Denormalization is the process of deliberately adding redundant data or grouping data to optimize the read performance of a database. Some times more normalization in a database also reduce the performance of  the process, such as in OLTP applications.  In some cases, denormalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance. It is the opposite of normalization process.

Denormalization helps to speed up database access by moving from higher to lower form of normalization.

Question:

What is ACID Properties?

Answer:

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability.

ACID property ensures the quality of the transaction.

Atomicity: If the transaction is completed its execution then at the end, either all of the transaction is successful, or all of the transaction fails. If a partial data is written to disk, the atomic property is violated. It is an all-or-none proposition.

Consistency: This property ensures that a transaction never leaves the database in a partially finished state.

Isolation: This isolation level must implies that the transaction must either visible to the data in its previous state or the transaction must wait until the changes from other transactions are committed. It keeps transactions separated from each other until they are finished.

Durability: This property ensures that once the user has been notified of success, the transaction will persist, and not be undone. The database will keep track of pending changes in such a way that the server can recover from an abnormal termination. This means it will survive system failure, and the database system has checked the integrity constraints and will not need to abort the transaction. In other words, when a client application receives notification that a transaction has completed its work successfully, it is guaranteed that the data is changed permanently.

Question:

What is the difference between DBMS and RDBMS

Answer:

DBMS (Database Management System) has to be persistent, i.e. it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored.

DBMS is used for easier business applications

RDBMS (Relational Data Base Management) adds the additional condition that the system provides a tabular structure for the data, with enforced relationships between the tables in the database. This excludes the databases that do not provide a tabular structure or don't enforce relationships between tables.

RDBMS is used for complex applications.

RDBMS that enforces the rules of foreign key concept

Question:

What are the types of constraints?

Answer:

-> PRIMARY KEY

A primary key defines the column or combination of columns that allow a row to be uniquely  identified. Each table can have only one key declared as a primary key.

Example:

You implement a primary key on the CustomerID column of the Customers table as follows:

CREATE TABLE Customers (

                CustomerID                        INT IDENTITY(1,1) PRIMARY KEY,

                CustomerName                 VARCHAR(50) NOT NULL,

                CreatedDate                         DATETIME DEFAULT (GETDATE()),

)

GO

-> FOREIGN KEY

Foreign key constraints can be used to implement a concept called referential integrity. It ensures that the values that can be entered in a particular column exist in a specified table (i.e. PRIMARY KEY table). Values cannot be inserted in this column that do not exist in the specified table. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Example:

Create Orders table and add foreign key constraint to Customers table CustomerID column.

CREATE TABLE Orders(

                OrderID                INT IDENTITY(1,1) PRIMARY KEY,

                CustomerID        INT NOT NULL REFERENCES Customers(CustomerID),

                OrderDate          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

)

-> CHECK CONSTRAINTS

A CHECK constraint defines a condition for one or more columns in a table at the time of INSERT and UPDATE operations.

Condition can be defined by any expression that returns TRUE or FALSE value. If the condition returns value TRUE then

The operation continues, but if the condition returns FALSE value, the operation is rolled back.

Example:

CREATE TABLE Employees (

                EmployeeID                       INT IDENTITY(1,1) NOT NULL,

                EmployeeName               VARCHAR(50) NOT NULL,

                ZipCode                               VARCHAR(5) NOT NULL CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]')

)

GO

Values rejected by the CHECK constraints that evaluate to FALSE. Because NULL values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose there is a constraint on an INT column "Unit" specifying that "Unit" can contain only the value 20 (Unit=20). If we try to insert the value NULL into "Unit" then the database engine inserts NULL and does not return an error for this.

-> DEFAULT CONSTRAINT

DEFAULT condition can be defined for columns to avoid repetitive data entry. If a column has a DEFAULT

Constraint then the default value will be supplied if you don't provide a specific value for the column in the INSERT statement.  We can provide DEFAULT definition as any expression that evaluates to a single scalar value with a data type that is compatible with the data type of the column in which the DEFAULT definition is defined.

This constraint is applied only in INSERT operations.

This expression can be a constant value, any scalar function in the system, the result of a scalar user defined function, any scalar expression created from any combination constant value or system scalar function or user defined function, including mathematical expressions.

Example:

CREATE TABLE Customers (

                CustomerID                        INT IDENTITY(1,1) NOT NULL,

                CustomerName                VARCHAR(50) NOT NULL,

                CreatedDate      DATETIME DEFAULT (GETDATE()),

)

GO

-> RULES

Rules provide the same functionality as check constraints, except that rules are created as a separate object. Because rules are not associated with a specific table or column when you create them, they cannot reference columns or tables in their definition. Instead, you use variables as placeholders. We define check constraints within the table definition and cannot reuse them. Rules provide the same features and complex comparisons via AND, OR, and NOT as check constraints and allow pattern matching. The following examples show the previous two check constraints implemented as rules:

CREATE RULE EmailValidatorRule

AS

@EmailValue like ‘%@%.[a-z][a-z][a-z]‘ OR @EmailValue like ‘%@%.[a-z][a-z].[a-z][a-z]‘;

After defining a rule, you then bind it to columns or user-defined data types by using the sp_bindrule system stored procedure as EXEC sp_bindrule 'EmailValidatorRule', 'dbo.mytesttable.Email'.

-> UNIQUE CONSTRAINT

We can create a PRIMARY KEY to enforce uniqueness in a field or group of fields, but we can have only one PRIMARY KEY per table.

Therefore, if we require enforcing uniqueness in other columns, you can create a UNIQUE constraint. Unique keys allow one NULL value.

Example:

CREATE TABLE Employees (

                EmployeeID                     INT NOT NULL UNIQUE NONCLUSTERED,

                EmployeeName               VARCHAR(50) NOT NULL

)

GO

Question:

What is a Subquery?

Answer:

A subquery or Inner query or Nested query is a nested select statement where the inner select statement is evaluated first. It is a query in a query. We can use the results of the inner select statement as the input for another select statement. For example, you can use the results of a subquery as a search condition that uses the IN ( ) function or EXISTS operator. A subquery is usually added in the WHERE Clause of the sql statement.

Example:

USE NORTHWIND

GO

SELECT * FROM Orders

WHERE OrderID IN(SELECT OrderID FROM [Order Details] WHERE OrderId = 90425)

Question:

What is a Correlated Subquery?

Answer:

It is a subquery in which the inner query is evaluated once for every value returned by the outer query. Generally a query which has a subquery will execute the subquery once and replace the resulting value or values into the WHERE clause of the outer query.

Correlated subquery (also known as a synchronized subquery or a repeating subquery) is a subquery that uses values from the outer query in its WHERE clause. The subquery is evaluated once for each row processed by the outer query. In queries that include a correlated

subquery, the subquery depends on the outer query for its values.

For example, using the Northwind database, if you want to find out the price of all the books ordered, you can use a correlated subquery. Price information about each book is in the [Order Details] table, and the

Orders table has all the books ordered.

Example:

USE NORTHWIND

GO

SELECT

                o.OrderID,

                o.OrderDate,

                (SELECT MAX (d.UnitPrice) FROM [Order Details] AS d WHERE d.OrderID = o.OrderID) AS MaximumUnitPrice

FROM Orders AS o

Question:

What is Stored Procedures?

Answer:

A stored procedure is a set of named Transact-SQL statement that is stored in the database in compiled form so that it can be shared by a number of programs. The main difference between a stored procedure and a set of statements is that a stored procedure can be reused just by calling its name. Therefore, if you want to re-run the code, you do not have to execute the whole set of statements that compose the stored procedure one by one.

Question:

What are advantages of Stored Procedures?

Answer:

- Stored Procedures allow modular programming

- Stored Procedures are precompiled statements

- Stored Procedures provide faster code execution and optimize network traffic.

- Stored Procedures provide security mechanism by needing to execute any of the statements directly.

- Stored Procedures can be set to execute automatically when SQL Server starts

- Stored Procedures can use parameters

Question:

Can we call a function and stored procedure from a select statement?

Answer:

It is possible to call a function from a select statement. It is not possible to call a stored procedure from a select statement.

Question:

What is INDEX in SQL Server?

Answer:

Indexes are created on columns in tables or views. The index provides a fast way to access data based on the values within those columns. Indexes are constructed as B-tree, which enables SQL Server to search very large volumes of data without affecting the performance from one query to the next. Indexes are useful only if it can help find data quickly regardless of the volume of data stored.

Question:

What are the benefits of Indexes?

Answer:

The database engine can use indexes to boost performance in a number of different queries

-- Searching For Records: When searching for rows with specific key values. These are queries with a

WHERE clause to restrict the query to specific values for every key column. (Exact-match queries)

-- Sorting Records: to produce an ordered output when using the ORDER BY clause.

-- Range queries: when solving queries that search for a range of values in a column.

-- Filtering for values in the foreign key to solve a join operation: When using a JOIN predicate to search for rows in a table based on keys from a second table.

-- Hash and merge join operations: In some cases, having an index can speed up the execution of a

JOIN algorithm, because the data is exactly in the order that the JOIN algorithm uses.

-- Covering a query: To avoid a full-table scan, when a narrow index contains all the required data.

-- Avoiding duplicates: To check for the existence of suitable indexes in an INSERT or UPDATE

operation in an attempt to avoid duplicates.

Question:

Is it possible to create Non-Clustered index on a primary key column?

Answer:

Yes. It is possible to create a non-clustered Index on a primary key column. We can use syntax given below to create a Non-Clustered Index on primary key column while creating table itself.

CREATE TABLE Customers

(

          CustomerID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED

)

Question:

Is it possible to create index on more than one column?

Answer:

Yes, it is possible to create index on more than one column. We can include maximum 16 columns as key columns while creating index. Sum of bytes should not be more than 900 bytes.

Question:

How many non-clustered indexes can be created in SQL Server?

Answer:

There can be only 249 non-clustered indexes per table possible in SQL Server 2005 and 999 Non Clustered Indexes per table in SQL Server 2008

Question:

Is it possible to force a query to use a specific Index?

Answer:

Yes, it is possible to force a query to use a specific index by using index hints.

Example:

SELECT FristName, LastName

FROM Employees WITH(INDEX (indx_firstname, indx_lastname))

Question:

What is the difference between clustered and non-clustered INDEX?

Answer:

Clustered INDEX is unique for any given table and the leaf level of a clustered index is the actual data, and the data is re-sorted in case of clustered index. We can have only one clustered index per table.

In case of non-clustered INDEX the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the database.

Consider the Telephone Directory as an example of Clustered Index, in which data and index are at the same page, whereas index in the backside of the book is a good example of non-clustered index.

Only one clustered index is possible per table and non-clustered index may be more than one per table (There can be only 249 non-clustered indexes per table possible in SQL Server 2005 and 999 Non Clustered Indexes per table in SQL Server 2008).

SQL SERVER - DROP,DELETE,TRUNCATE,SELECT all the tables in the Database

Using sp_MSforeachtable stored procedure we can empty all table or delete all data from all the tables in the database. We can do this with a single line of statement to do these entire action.
   
Let's first create a test database and table to test.


USE master
GO
CREATE DATABASE TestDataBase
GO

USE TestDataBase
GO
CREATE TABLE [dbo].[Account]
( [account_number] [varchar](6) NULL,
[branch_name] [varchar](6) NULL,
[balance] [numeric](8, 2) NULL
) ON [PRIMARY]

USE TestDataBase
GO
INSERT INTO [dbo].[Account]([account_number],[branch_name],[balance])
VALUES('ACC001','B00001',123.45)
GO


--To select all records from all tables in the database:

EXEC sp_MSforeachtable @command1 = 'SELECT * FROM ?'


--To delete all records in all tables in the database:

EXEC sp_MSforeachtable @command1 = 'DELETE FROM ?'

--To Truncate all tables in the database:

EXEC sp_MSforeachtable @command1 = 'TRUNCATE TABLE ?'

--To drop all the tables:
EXEC sp_MSforeachtable @command1 = 'DROP TABLE ?'