How to reset IDENTITY in Memory-Optimized Tables

Even though SEQUENCE object is available with much more flexibility, we still use IDENTITY property for adding sequence values to tables, specifically when we need to introduce a surrogate key. However, if you need the same with Memory-Optimized tables, you need to know certain things.

Can I add the IDENTITY property to Memory-Optimized tables?

Yes, it is possible but it should be always IDENTITY(1,1). You cannot use a different values for seed and increment, they should be always set as 1.

CREATE TABLE dbo.MemoryOptimizedTable
(
 Id int IDENTITY(1,1) NOT NULL primary key nonclustered,
 CurrentDate datetime NULL default (getdate())
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO 5

Can I reset the IDENTITY seed using DBCC CHECKIDENT?

No, this is not supported. If you need to reset the IDENTITY seed, only way is inserting a new value explicitly by turning SET IDENTITY_INSERT on. As shown in the example, once the record with value 100 is inserted, the next value of the seed is set to 100+1.

SET IDENTITY_INSERT dbo.MemoryOptimizedTable ON
GO
INSERT INTO dbo.MemoryOptimizedTable (Id, CurrentDate) VALUES (100, DEFAULT);
GO
SET IDENTITY_INSERT dbo.MemoryOptimizedTable OFF
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO

SELECT * FROM dbo.MemoryOptimizedTable;

What if insert a lower value explicitly?

It is possible as long as it does not violate any rules. Look at the code below. It inserts a record with value 50 explicitly. But it does not mean that the seed is getting reset to 50+1. This does not happen because the last generated value is greater than 50+1. Therefore, the value of next record is 102, not 51.

SET IDENTITY_INSERT dbo.MemoryOptimizedTable ON
GO
INSERT INTO dbo.MemoryOptimizedTable (Id, CurrentDate) VALUES (50, DEFAULT);
GO
SET IDENTITY_INSERT dbo.MemoryOptimizedTable OFF
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO

SELECT * FROM dbo.MemoryOptimizedTable;

Naming columns in the View

How do you name columns returned from your created view? Generally, you do not need to explicitly name them as the names of view-columns are derived from base-tables. But, if there is any ambiguity in the name for a column or calculated column, then it must be named explicitly.

What are the ways of naming columns?

There are two ways of naming columns in the view. One way is, mentioning names along with CREATE VIEW statement. This requires names for all columns, you cannot just name set of columns in the view. Here is an example of it;

Other way is, adding aliases to columns. Most of us prefer this way because we do not need to name all columns explicitly. In most cases, we need to name columns that has ambiguity in the name or it is a calculated column. Remember if above technique is used for naming columns, aliases added will be overwritten.

Creating Horizontal Views and Vertical Views

Everyone has either used or created views for getting or creating a dynamic result using one or multiple tables. And everyone knows the usage of views and purpose of creating them. But many do not how they have been categorized; such as Horizontal and Vertical. Here is a simple explanation on them.

Horizontal Views

If you think about the standard definition of a view, it is a virtual relation (table) that is result of one or more relational operations on one or more relations (tables). In simple terms, is a logical table defined with a SELECT statement based on one or more tables. Now, what is a Horizontal View? It is a view that limits the number of records to be produced. We create Horizontal Views by adding WHERE clause.

USE AdventureWorks2014;
GO

CREATE VIEW dbo.Sales_2012
AS
SELECT * FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2012;
GO

Vertical Views

This view results set of selected columns from one or more tables. We implement this by selecting required columns, optionally adding the WHERE clause.

USE AdventureWorks2014;
GO

CREATE VIEW dbo.Sales_2012_Summary
AS
SELECT SalesOrderNumber, OrderDate, SubTotal 
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2012;
GO

In addition to that, there is another type called Grouped and joined views that is actually either a Horizontal View or Vertical View with aggregations and multiple joins, hence I do not think that it has to be taken as a major type.

Converting Attributes to Columns - SQL Server Brain Basher of the Week #065

Let's talk about something common for all database management systems without talking something specific to SQL Server. This is the interview question of the week and let me start it with a conversation I had with a interviewee.

image taken from: https://www.pinterest.com/thepotentmix/recruitment-cartoons-and-other-entertainments/

Me: Let's assume that you have been given a document that contains identified attributes for a particular entity, for an example, Customer Entity. Business Analyst has mentioned that it needs an attribute called Customer Name. How do you take this entity and design your database table?

Interviewee: All I have to do is, understand given attributes and create a table call Customer with relevant columns. We need to make sure that the right data type is selected for each every attribute or the column.

Me: How do you decide the data type? Let's talk about this specific attribute: Customer Name.

Interviewee: Yes, I will be setting varchar for this attribute, probably with the size as 200. Another thing, if the solution is a multilingual application, have to use nvarchar instead of varchar.

Me: Good, anything else to be considered on that?

Interviewee: Regarding Customer Name, I think that is all I have to consider. We might have to change the size of it but 200 is reasonable.

Okay, what do you think? He is not a senior person, that is what I started with basic but I expected something additional, something extra.

First thing you need to understand is, BA does not know or does not think about database design. It is all about the business. It is our duty to covert the business identified to logical and physical database design. So, even though BA has identified Customer Name as an attribute, or as a single attribute, it does not mean that we need to stick into it. This is where we apply normalization rules. Not only that, we need to think and see;

• How this attribute is going to be filled
• How this attributed is going to be read
• How often the entity is searched based on this attribute.

For an example, Customer Entity (or the table) might be searched by Customer Last Name. Have we addressed this requirement. Do not say that BA has not mentioned it, if not mentioned, it is always good to get it clarified but it is something we need to consider.

If we consider other common things whether BA has mentioned it or not. Customer Name is not going to be a single column. You will surely have at least two columns called First Name and Last Name. This makes sure that your table is designed properly and it is ready for standard or most common analysis as well as holding data efficiently. Remember, breaking an attribute to multiple columns is not only for known attributes such as Employee Name, Location. We might break attributes like Product Code, Invoice Number as well. For example, Product Code might be forming using two elements such as Product Type Code and Unique Number. In that case, it is better to have two columns for Product Type Code and Unique Number, and another Computed Column for Product Code (if required).

Make sense? Although this is a simple thing, many miss it, hence DO NOT FORGET SIMPLE THINGS AND FUNDAMENTAL RELATED TO DATABASE DESIGN :).

Maintaining Sinhala (Unicode) characters with SQL Server Database

This is one of the questions I received last week on Unicode characters. I was asked the way of maintaining Sinhala characters in one of the tables because they have been trying with nvarchar data type but it has not worked as expected.

Let's try to understand the way of maintaining different characters such as Sinhala and Tamil. If we need to maintain such characters, usually with char or varchar, we should instruct to SQL Server to use specific code page and corresponding characters. The instructions can be passed using Collation. Not only it detects the code page and set relevant characters, it decides the sorting rules, case and ascent sensitivity of our data.

The COLLATE clause can be specified at Server Level, Database Level, Column Level or in an Expression (example, with ORDER BY). SQL Server supports Windows Collations, Binary Collations, and SQL Server Collations.

Note that not all collations related your language are available with SQL Server Collation set. If the required one is not available, then you can use Windows Collation set. However, some of Windows Collations cannot be set at Server Level or Database Level.

Collations for languages like Sinhala and Tamil are not exist with SQL Server Collation Set. Therefore, we have to use Windows Collation and no way of setting it at Database Level.

To see all Windows Collation, here is the page: https://msdn.microsoft.com/en-us/library/ms188046.aspx

As per the above page, we have to use Indic_General_100_ for Sinhala characters. We can find out all collations related to it using below query;

SELECT * FROM sys.fn_helpcollations();

Let's apply this to a table and see. The following code creates a table with a nvarchar data type for holding Sinhala characters. Note the the collation set for the column. When insert records, we need to make sure that N is used for specifying the the value is Unicode.

DROP TABLE dbo.Test;

CREATE TABLE dbo.Test
(
 Id int identity (1,1) primary key
 , Message nvarchar(4000)  collate Indic_General_100_BIN not null
);

INSERT INTO dbo.Test (Message) VALUES (N'Hello World!');
INSERT INTO dbo.Test (Message) VALUES (N'සුභ උදෑසනක්!');

Here is the result if you query the table now.

What are the advantages of a SQL Server database with multiple files located on one physical drive ?

This is a common question I always get from my classes/workshops. Do we really get some benefits out of a database with multiple data files on one physical drive? Obviously, no performance benefits, but in certain situation, it gives you some benefits;

1. Taking partial backups, file backups, file group backups
   Although the files have not been distributed on multiple physical drives, still partial backups, files backups and file-groups backups are possible. Assume that your database is very large and some tables are infrequently updated, then you can separate tables into two file groups, using multiple data files, one group for tables that are frequently updated and other for tables that are infrequently updated (or read-only tables). This allows you to have a time-saving and efficient backup strategy, setting different backup plans on two file groups.
   
2. Restoration into smaller multiple physical drives
   Assume that you maintain a database with single 100GB size data file. What if the exiting drive crashes and you get two 50GB physical drives for restoring the database from one of the backups? As you know, it is not possible, but, if the database is maintained with multiple data files even with a single physical drive, scenario like this can be easily addressed.
   
3. Moving files into another drive
   For various reasons, either the entire database or part of the database is required to be moved to a different physical drives. Some of the reasons could be, moving from test environment to production environment, moving some file-groups to optimized physical drives, moving entire database to a different location. If the database is constructed with multiple files, moving files for above scenario can be easily done with less effort.