Category : SQL Server


Add constraint without checking existing data

In this post “Add constraint without checking existing data” we are going to learn how we can add a constraint on a column which already has invalid data. We create constraints to automatically enforce the integrity of the database. Using constraints we can define the rules for valid set of values for a given column. Let’s learn how we can instruct SQL Server to ignore check on the existing data during constraint creation on a column.

We have an employee table with columns EmpId, EmpName and ContactNumber. Column ContactNumber holds few invalid values, as we don’t had a constraint on this column. Now, we want to accept only valid values in the contact number column being validated with the custom business logic, but we don’t want to remove or modify the already existing data in the column. We need to put a constraint for coming data and not for existing … More


Index on computed column 2

Today, we are going to learn how we can create index on a computed column which improves the performance of SELECT queries. To create a computed column, we will use an user defined function instead of inline code. Also to know about computed columns, refer my previous blog post “What is Computed Column in sql server“.

Create index on computed column

Let’s create a function, “dbo.fn_GetTotalCourseFee“, which accepts @CourseFee and @TaxPercentage as input parameters and returns the computed @TotalFee amount. To highlight some important prerequisites related to index creation on computed column, I have used a function, otherwise we can also use the inline code in column definition.

Below is the function being used to create a computed column:

CREATE FUNCTION dbo.fn_GetTotalCourseFee
(
 @CourseFee NUMERIC(9, 2),
 @TaxPercentage NUMERIC(9, 2)
)
RETURNS NUMERIC(9, 2)
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @TotalFee NUMERIC(9, 2) = 0.0
 SET @TotalFee = 
More

Querying temporal table data

We have discussed what is a temporal table, how we can create it, and what are the limitations of a temporal table. We have also discussed how we can convert an existing table to a temporal table in our previous posts.

In this post “querying temporal table data” we are going to learn how to query the time varying data from a temporal table.

In SQL Server 2016, we have a new clause FOR SYSTEM_TIME, which has four new sub clauses to query temporal table data:

  1. AS OF <datetime>
  2. FROM <start_datetime> TO <end_datetime>
  3. BETWEEN <start_datetime> AND <end_datetime>
  4. CONTAINED IN (start_datetime, end_datetime)

Let’s create a temporal table with dummy data to query with FOR SYSTEM_TIME clause and its sub clauses with examples. Below script is used to create a temporal table and to insert few dummy rows in the table with SQL Server default datetime value (1900-01-01) … More


Convert existing table to temporal table 3

In the previous post, we have discussed the temporal table, which is introduced in SQL Server 2016 to store the time varying data (current data and the data change history along with the time period). There, we have also learnt how we can create a temporal table from scratch.

In this post “Convert existing table to temporal table“, we are going to learn how we can convert an existing table to a temporal table. For the demo purpose we are using a dummy employee table, created with a very small subset of the data taken from HumanResources.Employee table of adventure works database.

Below is the dummy table which needs to be converted in a temporal table:

Existing employee master table

Existing employee master table

We can use below script to create the above sample table with the data for demo purpose:

--Create demo employee master table
CREATE TABLE dbo.EmployeeMaster
(
BusinessEntityID 
More

Temporal Table in SQL Server 1

In this post we are going to discuss “Temporal Table”, a new feature introduced in SQL Server 2016. Lets start this post “Temporal Table in SQL Server” with the definition of temporal table.

Temporal Table facilitates inbuilt support for data change tracing along with the time period in a table. It holds the current as well as historical data to determine the values of a record at any given period of time. Temporal table uses a pair of tables; current table and an associated history table, to store current and historical data respectively. It uses two system columns of data type datetime2 to record start time and end time in both tables (current and history) to enable record versioning.

We can easily manage the data change history in a table using temporal tables which was a bit cumbersome and manual task in the previous versions of Microsoft SQL Server. Temporal … More


Get hierarchical data in order 2

In this post, we are going to learn how we can get hierarchical data in order. We are dealing with a user defined hierarchical data which can have “n” number of siblings and their siblings can also have “n” number of siblings in turn.

For demo purpose, we have a Product_Master table, in which a product can be marked as a parent of another product, and that child product can also be marked as a parent of another product, such kind of hierarchy is known as user defined hierarchy. As any product can be marked as another product’s parent, we can have any number of nested products in the hierarchy. We have to retrieve all these products with their associated parent in ordered way.

Ordered hierarchical data from above user defined hierarchy, can be achieved with a recursive common table expression (CTE) or using a loop. We … More


Generate serial number for each consecutive set of numbers 2

In this post, we are going to generate serial number for each consecutive set of numbers in a given column. We have a table tbl_Donation with columns EmpId and DonationYear. For each consecutive set of years of each employee, we need to generate serial numbers. If employee “X” donated in 2006, 2007 and 2008 years consecutively and then in 2010 and 2011, we have to assign 1 for 2005, 2 for 2006, 3 for 2007 followed by 1 for 2010 and 2 for 2011. We need to generate incremental series for each consecutive set of years but as and when a gap occurs between donation years, series has to be restarted with 1.

PARTITION BY clause in a ROW_NUMBER() window function could be able to generate the required output, if we had same number along with donation years for each consecutive set of years for all employees. Initially, we … More


CHAR, NCHAR, VARCHAR and NVARCHAR default length

What is the default length of CHAR, NCHAR, VARCHAR and NVARCHAR when we don’t specify their length in their length argument? If we omit the size of CHAR, NCHAR, VARCHAR and NVARCHAR, how they behave? In this post “CHAR, NCHAR, VARCHAR and NVARCHAR default length”, we are going to discuss the default length of CHAR, NCHAR, VARCHAR and NVARCHAR data types, if length is not specified in their argument.

I would strongly suggest to define the length of CHAR, NCHAR, VARCHAR and NVARCHAR data types as per the need. In this post, I just explain the behavior of CHAR, NCHAR, VARCHAR and NVARCHAR data types, if length is omitted, and not encouraging to omit the size of these data types.

We can define these data types as below:

char [ ( n ) ]
nchar [ ( n ) ]
varchar [ ( n | max ) ]
nvarchar [ More


Insert into table with one identity column only 2

One of my friend asked me a question that “How we can insert values in a table with only one column which is identity, without using IDENTITY_INSERT ON?”. In this post “Insert into a table with one identity column only”, I am going to share a nice method to achieve this.

We know that using “SET IDENTITY_INSERT TableName ON” command, we can easily insert rows in an identity column. As per MSDN, we need to keep few things in mind related to “IDENTITY_INSERT ON”:

  1. Only one table can have IDENTITY_INSERT as ON in one session at a time. We must have to use “SET IDENTITY_INSERT TableName OFF” to use IDENTITY_INSERT on other tables.
  2. To execute IDENTITY_INSERT on a table, a user must own the table or has ALTER permission on the table. Only below types of user can execute IDENTITY_INSERT on a table:
    1. A member of sysadmin fixed server role
More

Delete all rows from all tables 2

Have you ever come across the problem to delete all rows from all tables available in a database? One of my colleague asked me this question that how can we delete all rows from all tables of a database. So, in this post, we are going to discuss, how we can delete all rows from all tables of a SQL Server database.

To delete all rows from a table named ProductCategory, we can simply use “DELETE FROM ProductCategory” command. But in case the table is being referred by a foreign key constraints from some tables and / or fires a trigger to insert few rows in an another table in the database, we must have to delete all related rows from child tables before we start deleting parent table rows. We also need to delete records from those table which are getting inserted rows as a result of a … More