Tuesday, February 28, 2017

CHECK constraints accepts values that evaluate to UNKNOWN

Few days back, I wrote a post titled as SQL Server Default and Rule objects - Should not I use them now? that discussed two objects that are deprecated that can be used for enforcing the data integrity. I received a question based on it, related CHECK Constraint.

CHECK Constraint limits the values for columns based on the condition added. It can be set with a column, or it can be set for the entire record by adding it to the table. If you are adding CHECK Constraint for enforcing data integrity, you need to remember how it works.

CHECK Constraint works with any Boolean Expression that can return True, False or Unknown. If the value is False, it will be rejected and if the value if True, it will be accepted. However, if the value is Unknown, then it accepts it without rejecting. Therefore, you need to be very careful with the condition you write because, if the condition returns NULL, then it will be treated as True.

You can understand it by looking at the following code;

USE tempdb;

CREATE TABLE dbo.Student
 StudentId int primary key
 , Name varchar(100) NOT NULL
 , Marks int NULL
 , Credit int NOT NULL
 , Constraint ck_Student_Marks_Credit CHECK (Marks + Credit > 100)

-- This record can be inserted
INSERT INTO dbo.Student 
 VALUES (1, 'Dinesh', 60, 55);

-- This record cannot be inserted
INSERT INTO dbo.Student 
 VALUES (2, 'Yeshan', 40, 40);

-- This record CAN BE INSERTED
INSERT INTO dbo.Student 
 VALUES (3, 'Priyankara', null, 60);

Monday, February 27, 2017

NULL values consume storage in SQL Server Tables?

While we were discussing on data types to be used and null-able columns, a question was raised, asking whether the space is used for NULLs as SQL Server uses for other data types. My answer was Yes and No because it depends on the data type we have used.

NULL is not exactly a value. It indicates that the value is unknown hence it requires some bits/bytes to maintain it. However, if I set one of my columns value as NULL, can I assume that it will not use the space that data type suppose to use? For example, if I have a column with data type int that uses 4 bytes per value and I inserted a record with NULL for the column, will SQL Server still uses 4 bytes or few bits for the NULL?

It is always better to write some codes for testing and come to a conclusion. Therefore, let's test this with four tables. The below code creates;
  1. Customer_Without_NullValues_FixedWidthType table
  2. Customer_With_NullValues_FixedWidthType table
  3. Customer_Without_NullValues_VaryWidthType table
  4. Customer_With_NullValues_VaryWidthType table
The last three columns of First and Second tables are set with date data type and all are null-able. And last three columns of Third and Forth tables are set with varchar(4000) data type and all are null-able.

USE tempdb;

CREATE TABLE dbo.Customer_Without_NullValues_FixedWidthType
 Id int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , DateOfBirth date NULL
 , RegisteredDate date NULL
 , LastPurchasedDate date NULL

CREATE TABLE dbo.Customer_With_NullValues_FixedWidthType
 Id int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , DateOfBirth date NULL
 , RegisteredDate date NULL
 , LastPurchasedDate date NULL

CREATE TABLE dbo.Customer_Without_NullValues_VaryWidthType
 Id int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , Details1 varchar(4000) NULL
 , Details2 varchar(4000) NULL
 , Details3 varchar(4000)  NULL

CREATE TABLE dbo.Customer_With_NullValues_VaryWidthType
 Id int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , Details1 varchar(4000) NULL
 , Details2 varchar(4000) NULL
 , Details3 varchar(4000)  NULL

Next code insert 100,000 records for each table. However, last three columns of Second and Forth tables are filled with NULLs instead of known values.

INSERT INTO dbo.Customer_Without_NullValues_FixedWidthType
 (Name, DateOfBirth, RegisteredDate, LastPurchasedDate)
 ('a', getdate(), getdate(), getdate());

INSERT INTO dbo.Customer_With_NullValues_FixedWidthType
 (Name, DateOfBirth, RegisteredDate, LastPurchasedDate)
 ('a', null, null, null);

INSERT INTO dbo.Customer_Without_NullValues_VaryWidthType
 (Name, Details1, Details2, Details3)
 ('a', REPLICATE('a', 4000), REPLICATE('a', 4000), REPLICATE('a', 4000));

INSERT INTO dbo.Customer_With_NullValues_VaryWidthType
 (Name, Details1, Details2, Details3)
 ('a', null, null, null);

GO 100000

In order to see the space usage, easiest way is, check number of pages read for data retrieval.

As you see, space usage of First and Second table is same regardless of the value stored. I means, Fixed data types need the defined space whether the value is null or not. However, Third and Forth clearly shows that it is not the same with data type with vary length. When the data type is vary in length, it does not need the space defined with the type.

Sunday, February 26, 2017

Azure SQL Database or SQL Server on Azure Virtual Machine - SQL Server Brain Basher of the Week #066

Here is a modern interview question on SQL Server. In addition to general questions on administration and maintenance, Azure based questions have become common now because many maintain databases either in a VM hosted in cloud or as a Cloud-Managed-Database. Therefore, let's talk about an interview question related to Azure.

Here is the question. What are the factors you can use for determining whether the database has to be set up with a Virtual Machine hosted on Azure or set up as an Azure SQL Database?.

There is a very common answer for this question. If we want to get the entire database managed by Azure, such as Disaster Recovery, Backup, or Performance Tuning, then Azure SQL Database is the best. If we need to manage the instance by ourselves, then we need to go ahead with SQL Server on Azure Virtual Machine.

There is nothing wrong with the answer but it always better to know few more factors that can be used for deciding the way. Let's talk about some important factors.

Azure SQL Database is a relational Database-as-a-Service (DBaaS) (that falls under industry categories of Software-as-a-Service - SaaS and Platform-as-a-Service - PaaS) and SQL Server on Azure Virtual Machine is a Infrastructure-as-a-Services - IaaSAzure SQL Database is completely managed by Microsoft, you do not need to worry about the Operating System, configuring hardware, service packs, patches. But if it is SQL Server on Azure Virtual Machine, then we need to manage everything by us. It is good if you have a team or dedicated DBA for managing the database and you need to be the administrator of it, not Microsoft.

Azure SQL Database Server is logical server though it can hold multiple databases. It is not considered as an Instance that you configure in SQL Server on Azure Virtual Machine. You know that, you can have multiple unrestricted administrators in an instance which is possible with SQL Server on Azure Virtual Machine but not possible with Azure SQL Database.

Another key thing you need to remember is, Microsoft makes most of new features available initially with Azure SQL Database before making them available with other versions. That is why you see features such as Treat Detection, Temporal Tables with Retention Policies only available with Azure SQL Database. In addition to that, this has an Intelligence Database Service that recommends possible optimizations for performance tuning.

Do not forget, Azure SQL Database is limited to 1 TB whereas instance of SQL Server on Azure Virtual Machine can consume 64 TB with multiple databases.

We do not need to worry on High Availability with DBaaS as it is provided with the service. With IaaS needs a manual set up with multiple VMs using clustering + Availability Group as High Availability is only for the VM, not for the SQL Server instance.

Azure SQL Database has no hardware and administrative cost where as SQL Server on Azure Virtual Machine has administrative cost. When it comes to License cost, Azure SQL Database is sold as a service based on multiple tiers and SQL Server on Azure Virtual Machine comes with a license but you can use your own license if you need.

If you need to use other components such as Integration Services, Analysis Services or Reporting Services, then SQL Server on Azure Virtual Machine is the only option though some services such as Analysis Services available as a managed service.

There are few more tiny reasons for picking one over other. But as an Interviewee, the mentioned ones should be known.

Saturday, February 25, 2017

SQL Server Default and Rule objects - Should not I use them now?

In order to make sure that the database contains high quality data, we ensure data integrity with our data that refers to the consistency and accuracy of data stored. There are different types of data integrity that can be enforced at different levels of solutions. Among these types, we have three types called Domain, Entity and Referential Integrity that are specific to database level for enforcing data integrity.

For enforcing Domain Integrity, SQL Server has given two types of objects called Default and Rule. We have been using these objects for handling Domain Integrity but now it is not recommended to use these for enforcing Domain Integrity.

Let's try to understand what these objects first and see the usage. Default object can be used for creating an object that holds a default value and it can be bound to a column of the table. Rule is same as Default and it creates an object for maintaining rules for columns. See below code as an example.

USE tempdb;

-- creating default object
CREATE DEFAULT CreditLimitDefault AS 10000;

-- creating a sample table
CREATE TABLE dbo.Customer
 CustomerId int PRIMARY KEY
 , LastName varchar(50) NOT NULL
 , CreditLimit decimal(16,4) NOT NULL

-- Binding the default to a column
-- The object can be bound to many tables
EXEC sp_bindefault 'CreditLimitDefault', 'dbo.Customer.CreditLimit';

-- creating rule object
CREATE RULE CreditLimitRule AS @CreditLimit > 9000;

-- Binding the rule to a column
-- The object can be bound to many tables
EXEC sp_bindrule 'CreditLimitRule', 'dbo.Customer.CreditLimit';

As you see, above code creates two objects, CreditLimitDefault and CreditLimitRule that are Default and Rule objects. These objects can be assigned to any column in any table.

As I mentioned above, it is not recommended to use them now as they are deprecated. It is recommended to use Default and Check constraints instead.

Read more on CREATE DEFAULT at: https://msdn.microsoft.com/en-us/library/ms173565.aspx

Friday, February 24, 2017

How to hide SysStartTime and SysEndEtime columns in Temporal Tables

Temporal table was introduced with SQL Server 2016 and it is designed to capture and store changes of data in tables. In other words, similar to Change Data Capture (CDC), Change Tracking (CT), Temporal table maintains the history with changed details.

Temporal table needs two additional columns called SysStartTime and SysEndTime. Once they are added, they can be seen with the table just like other columns and will be appeared with SELECT * statement. Although it is not recommended to write SELECT * type of query against tables, unfortunately it can still be seen with many application and the database I had to analyze today had similar codes in almost all areas in the application. I had to make two tables as Temporal Tables and I had to make sure that it does not break the existing application.

Fortunately, SQL Server has given a solution for handling it. I was able to alter the table and make it as a Temporal Table without making changes to any statement written in the application while making sure that SELECT * does not return newly added SysStartTime and SysEndTime columns.

If you use, HIDDEN keyword when creating the Temporal Table, it makes sure that these two columns are not appeared when SELECT * is performed. However, columns can be explicitly mentioned in the SELECT if required.

-- changing existing table by adding columns
ALTER TABLE dbo.Customer  
  DEFAULT CONVERT(datetime2 (0), '2017-02-24 00:00:00')
  DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59')
 , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);  

-- turning versioning on
ALTER TABLE dbo.Customer

-- Checking records  
SELECT * FROM dbo.Customer;  
SELECT *, ValidFrom, ValidTo FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;  

Thursday, February 23, 2017

SQL Server Date, Datetime and Datetime2 - What should I use when I get the value as a string?

When we have to store a datatime value (or date), in most cases, application accepts the value as a datetime and send the value to SQL Server as a datetime value. However, if the value is sent as a string (Example, CSV upload), then what should be the best way of formatting the value and how we can convert it to datatime without making any mistake?

It is always recommended to use ISO 8601 standard when exchanging datatime values. The standard describes the way of passing a datetime value, generally it is YYYY-MM-DDTHH:MM:SS.sss. You can read more on this my post: Best way to pass datetime values to SQL Server – SS SLUG Dec 2013 – Brain Bashers - Demo III.

With SQL Server 2016, the default string format for dates is YYYY-MM-DD. If you pass the value with this format, regardless of the Current Language set, SQL Server will accurately read the value. However, this does not work with all datetime data types as expected. Have a look on the following code;


DECLARE @Date date
DECLARE @Datetime datetime
DECLARE @Datetime2 datetime2

SET @Date = '2017-05-06';
SET @Datetime = '2017-05-06';
SET @Datetime2 = '2017-05-06';

SELECT DATENAME(mm, @Date) As WithEnglish;
SELECT DATENAME(mm, @Datetime) As WithEnglish;
SELECT DATENAME(mm, @Datetime2)As WithEnglish;


DECLARE @Date date
DECLARE @Datetime datetime
DECLARE @Datetime2 datetime2

SET @Date = '2017-05-06';
SET @Datetime = '2017-05-06';
SET @Datetime2 = '2017-05-06';

SELECT DATENAME(mm, @Date) As WithGerman;
SELECT DATENAME(mm, @Datetime) As WithGerman;
SELECT DATENAME(mm, @Datetime2) As WithGerman;

As you see, Datetime data type convert happens based on the language set but Date and Datetime2 data types are accurately interpreted regardless of the language set. This is something you have to remember. If you expect datetime values as string and settings related to the session can be changed, then it is always better to use either Date or Datetime2.

If you need to make sure that date is properly getting interpreted regardless of the settings (language), then stick into ISO 8601. If you change the values of variable as below, you will get the same month: May for both English and German.

SET @Date = '2017-05-06T00:00:00';
SET @Datetime = '2017-05-06T00:00:00';
SET @Datetime2 = '2017-05-06T00:00:00';

Note that ISO 8601 accepts a value like 24:00:00 for time for midnight but SQL Server does not support it.

Wednesday, February 22, 2017

How to change the default Fill Factor value of SQL Server instance

Fill Factor is a setting that uses with Indexes for determining how much free space remains on each leaf-level page for future operations (Insert or Update). If you do not specify the value for Fill Factor when creating the index, the default value is set with it, which is 0.

Is it possible to change the default value? Yes, it is possible. The current value set can be seen using sp_configure;


Here is the output of it.

If you create a table like below in tempdb database;

USE tempdb;

CREATE TABLE dbo.Customer
 CustomerId int PRIMARY KEY
 , LastName varchar(20) NOT NULL 

and check the Properties of the index created, you will see that Fill Factor of it is 0.

You can change the default value using sp_configure. Once it is change, you need a restart of the service in order to get the value applied.

SP_CONFIGURE 'fill factor (%)', 80;

If you recreate the table again, you will see the Fill Factor as 80% now.

Tuesday, February 21, 2017

SQL Server Sequence suddenly starts with -2147483648

Have you faced this? Assume that you have created a Sequence object for generating values sequentially for tables which data type is set as int, and suddenly it shows the next number as -2147483648.

See below code;

-- creating sequence
CREATE SEQUENCE [dbo].[OrderSequence]
AS int

-- requesting numbers three times
SELECT (NEXT VALUE FOR   dbo.OrderSequence) AS OrderSequence
GO 3

What could be the reason? If you analyze the code written above, you can easily find the issue. I have not used MINVALUE property when creating the Sequence, hence it takes the lowest value of the data type set for the Sequence, which is -2147483648 for int data type. You may experience the same, if so, check and see whether the MINVALUE has been set or not.

Monday, February 20, 2017

Can we access the SQL Server temporary table created in different database?

Temporary tables are nothing new and we have been using this for long time. There are two types of temporary tables; Local that starts with single pound sign (#) and Global that starts with double pound signs (##). Local Temporary Tables are limited to the connection created and will be discarded automatically when the connection is disconnected. Global Temporary Tables are global to the instance and it can be accessed by the anyone connected the instance. It will be dropped automatically when the last referenced connection is dropped.

Now the question is, when a Local Temporary Table is created, can I access it in another database?

Answer is yes and no. See the code below.

USE Sales;

 Id int

-- This works without any issue
SELECT * FROM #TempTable;

The created table can be access without any issue because access is done in the same database using the same connection. If we try the SELECT in another database with different window (different connection);

USE Sales;

-- This will throw "Invalid object name '#TempTable'." error.
SELECT * FROM #TempTable;

You will see an error as above. However if I try to access the table from the same connection but different database;

--USE Sales;

-- Id int

--SELECT * FROM #TempTable;

-- Same first connection but different database
USE AdventureWorks2014;

-- This will work
SELECT * FROM #TempTable;

As you see, it is possible. Remember, Local Temporary Tables are limited to the connection, not to the database created, hence the created table can be accessed within any database as long as the connection is same.

Sunday, February 19, 2017

Changing Schema of SQL Server objects

I had a requirement today to change the schema of set of tables to new schema but I did not find a direct method to change the schema of all objects using a single statement. The ALTER SCHEMA supports transferring one object from one schema to another but it cannot be executed against multiple tables.

USE AdventureWorks2014;

-- transferring Person table from Person Schema to Sales
-- Once executed, tables becomes Sales.Person

Therefore I wrote a simple code for transferring multiple tables (of course code can be changed for addressing any object type) and thought to share it because you may look for something similar if you have the same need.

USE AdventureWorks2014;

DECLARE @TableNames TABLE (Id int identity(1,1) PRIMARY KEY, Name varchar(500));
DECLARE @Messages TABLE (Id int identity(1,1) PRIMARY KEY, Message varchar(1000));

DECLARE @TableName varchar(500);
DECLARE @TableId int = 1
DECLARE @NewSchema varchar(20) = 'Sales';
DECLARE @OldSchema varchar(20) = 'Production';
DECLARE @Statement varchar(500)

-- table all table names needed
SELECT s.name + '.' + t.name TableName
FROM sys.tables t
 INNER JOIN sys.schemas s
  ON t.schema_id = s.schema_id
WHERE s.name = @OldSchema
 AND t.type = 'U';

-- making the ALTER SCHEMA statement for all tables
-- and execute them using EXEC
 SELECT @TableName = Name FROM @TableNames WHERE Id = @TableId;
 SET @Statement = 'ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName;

  EXEC (@Statement);
  -- record success message
  INSERT INTO @Messages (Message) VALUES ('Successfully transfered ' + @TableName + ' to ' + @NewSchema);
  -- record the error
  INSERT INTO @Messages (Message) VALUES ('Transfer unsuccessful: ' + @TableName + ' [' + ERROR_MESSAGE());

 SET @TableId += 1;

-- checking the output
SELECT * FROM @Messages;

Thursday, February 16, 2017

Creating multiple administrative accounts in Azure SQL Database Server

You know that we can create multiple administrator accounts in SQL Server if it is a on-premises instance or an instance configured in a VM (hosted in cloud or on-premises). What we generally do is, create a login and add the login to sysadmin fixed server role. Can we do the same in Azure SQL Database (Server)?

There is only one unrestricted administrative account that can be be created in Azure SQL Database Server. It is created when creating the server. In addition to that, you can add either one Azure Active Directory Account or Security Group Account (that has multiple accounts as members). If you open the Properties of your server, you can see your administrative accounts;

However, if you need to add multiple administrative accounts (not unrestricted administrative accounts), then there is a way of adding them. There are two server-level administrative roles that are available in the master database which user accounts can be added for granting permissions for creating and managing databases and logins.

The first role is dbmanager. This role has permission to create new databases, hence members of this role can create databases. This role exist in the master database. Therefore, only users in the master database can become members of this role.

-- In master database
-- creating a user

-- adding the user to dbmanager role
ALTER ROLE dbmanager ADD MEMBER Dinesh2;

The other role is loginmanager. This role has permissions for creating logins in the master database. Again, this role is master database, hence only users in master database can become members of it.

-- In master database
-- creating a user

-- adding the user to loginmanager role
ALTER ROLE loginmanager ADD MEMBER Dinesh2;

Wednesday, February 15, 2017

Should I create Azure SQL Logins or Azure SQL Users?

Unless it is a contained database, we must create a login for connecting with SQL Server in order to access a database. Databases are accessed using Users and Users are created using Logins. Is it same with Azure SQL Databases? Should I create a login before creating a user?

With Azure SQL Database, it is not a must as Azure SQL database is a contained database (portable). We can create a user account with a password for accessing the database without creating a login. However, it allows us to create a login in the master and then create a user in our database using the login created in the master.

What if I created a user in my database using a login?
The main advantage is maintenance. If the login needs to be dropped or disabled, it can be simply done in the master database. Not only that, since multiple user accounts can be created with multiple databases using the same login, one login can be used for connecting the multiple databases.
The biggest disadvantage with this is, database becomes non-portable. If databases needs to be copied, moved or replicated, login must be created with new servers.

What if I created a user in my database without creating a login?
The main benefit we get is a portable database. Since all users with passwords are maintained inside the database, it can be copied, moved, replicated without any issue.
However, if the environment has multiple databases and users need to access more than one database, then multiple user accounts have to be created.

Monday, February 13, 2017

The Integration between Azure Data Lake Store and Azure SQL Data Warehouse

As a part of my presentation, I had to show the integration between Azure Data Lake Store (ADL) and Azure SQL Data Warehouse (Azure DW), hence to created the image below;

Generally ADL is used for storing unlimited data in any format (structured and unstructured) and processing them using various tools given with Massively Parallel Processing (MPP). We use Azure DW for storing a large volume of structured data, again using MPP. In most cases, Azure SQL DW is the source for the Business Intelligence. Earlier, Azure DW could not ingest data from ADL but now it is possible with PolyBase, hence, best practice is, load data into ADL, process them and then transfer to Azure SQL DW for reporting.

Sunday, February 12, 2017

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.

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 :).

Saturday, February 11, 2017

Azure Feature Pack for Integration Services (SSIS) - New Version Available

Microsoft has made the latest version of Azure Feature Pack for Integration Services (SSIS) and it is available for downloading. This was released on 28-Dec-2016 and there are few addition components added when comparing with the previous version.

Why we need this? Simply to work with Big Data as part of our ETL solution with the support of Azure Big Data platform which is HDInsight and supportive projects.

I wrote few posts on previous version of this, you can read more on them at;

The above posts explain how to download and install the feature pack, configure tasks for accessing HDInsight and process some unstructured data using tasks given with SSIS.

You can get the latest version of Azure Feature Pack fromhttps://msdn.microsoft.com/en-us/library/mt146770.aspx

I noticed one Control Flow new Task; Azure SQL DW Upload Task and two Data Flow items; Azure Data Lake Store Source and Azure Data Lake Store Destination that were not available with the previous version.

Previous Version:

Latest Version:

Need to test these new items, must be really interesting, will surely share them via blog posts.

Friday, February 10, 2017

SQL Server Data Tools (Visual Studio) - The length of the full path for the solution, project or item you are creating exceeds the maximum path length allowed

While trying to create a new SSIS project with default values for Project Name, Location and Solution Name, I experienced the following limitation;

The length of the full path for the solution, project or item you are creating exceeds the maximum path length allowed by the system. You must reduce the length of the name or the location.

This is not something new but I did not experienced this for a long time. What is the reason for this?

This is because of the limitation with the OS. The limitation is set for 260 characters for the path, hence when exceeds, Visual Studio shows this message without letting us to create the project. There are two ways I tried to overcome this issue;
  1. Of course, the first way is, select a path that can be referred with less number of characters.
  2. Second one is, creating the project without creating the solution. For that, we need to un-check the Create directory for solution Checkbox and then create the project. Once created, we can open it and add it a solution.
This checking is based on following calculation;

Path length + 1 (separator) + 
Solution name length + 1 (separator) + 
Project name length + 1 (separator) + 
Project name length + 
80 (Reserved space)

Thursday, February 9, 2017

Passing .Net data table as a User Defined Table to a Stored Procedure

When there is an updated data set that needs to be sent to a Stored Procedure for updating all records in the data set, what most do is, iterate the data set, access records one by one and call the stored procedure for each an every record. This is not a best practice and it affects to the performance of the application. Time it needs may not be noticed if the data set contains few records but it is a noticable when there are many records to be updated. What if we can send the entire data set with a single call and stored procedure can update all records with a single execution?

This is not something new but still do not use. SQL Server 2008 introduced User Defined Data Table that can be used for creating a new type similar to a Structure in programming languages. It can be used for accepting a data set to a stored procedure via a parameter and, since it is a table, data in the table can be accessed just like a normal table for manipulation.

Let's see how we can do it. Assume that I have a data set that contains updated recordset and need to pass it for a stored procedure. For that, first of all, we need to create a table type;

CREATE TYPE [dbo].[NoteSectionTagTable] AS TABLE
 NoteSectionId int not null
 , TagId int not null

Next is the stored procedure. See how it can be set. Note the Read Only keyword which is a must.

CREATE PROCEDURE [dbo].[UpdateNoteSectionTags]
 @NoteSectionTagTable NoteSectionTagTable READONLY

 DELETE dbo.NoteSectionTags
 WHERE NoteSectionId IN (SELECT NoteSectionId FROM @NoteSectionTagTable);

 INSERT INTO dbo.NoteSectionTags
  (NoteSectionId, TagId)
 SELECT NoteSectionId, TagId
 FROM @NoteSectionTagTable;


Everything is ready in database level. Here is the way of creating a table table and passing it to the stored procedure using C#.Net.

// creating the table
System.Data.DataTable noteSectionTags = new System.Data.DataTable();

// adding columns
System.Data.DataColumn noteSectionIdColumn = new System.Data.DataColumn("NoteSectionId", typeof(int));

System.Data.DataColumn tagIdColumn = new System.Data.DataColumn("TagId", typeof(int));

System.Data.DataRow noteSectionTag;

// adding rows
noteSectionTag = noteSectionTags.NewRow();
noteSectionTag["TagId"] = Convert.ToInt32(item.Value);
noteSectionTag["NoteSectionId"] = Convert.ToInt32(ViewState["NoteSectionId"]);

// we can add multiple rows like above

//creating sql command for calling the stored procedure
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("dbo.UpdateNoteSectionTags");
command.CommandType = CommandType.StoredProcedure;

//note the parameter data type
System.Data.SqlClient.SqlParameter parameterNoteSectionTagTable = new System.Data.SqlClient.SqlParameter("NoteSectionTagTable", SqlDbType.Structured);
parameterNoteSectionTagTable.Value = noteSectionTags;

// getting the connection for the config
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))

    command.Connection = connection;

Wednesday, February 8, 2017

How do I set the size of SqlParameter of SqlCommand for varchar(max)?

If you need to execute a Stored Procedure that implemented with varchar(max) parameter, using .Net Application, how do you set the size of the parameter?

Since the constructor of SqlParameter does not accept string value for size property, we have no way of passing max as the size;

// This will not compile
SqlParameter parameterNote = new SqlParameter("Note", SqlDbType.NVarChar, max);

But we can pass -1 as the size that indicates it is max;

SqlParameter parameterNote = new SqlParameter("Note", SqlDbType.NVarChar, -1);

Tuesday, February 7, 2017

Splitting values in a string variable and inserting values as rows - II - STRING_SPLIT function

Yesterday I wrong a post on splitting string values using an Extended Function. The biggest issue with it was, unavailability in Azure SQL Database. However, we have a new function to achieve it and it works in both SQL Server 2016 and Azure SQL Database.

This is how it works;

DECLARE @String nvarchar(4000) = 'SQL,Business Intelligence,Azure';

FROM STRING_SPLIT(@String, ',');

No more complex queries for splitting values :).

Monday, February 6, 2017

Splitting values in a string variable and inserting values as rows - I

Challenges are interesting and finding various ways to solve is a thrilling adventure.

One of the codes I had to write today was, splitting a string value received from an ASP.Net application and inserting them into a table. There are many ways of splitting a string values (or sometime, converting columns into rows) but this was bit different. The values I receive from the application are something like;

"SQL Business_Intelligence Azure"
"Personal Fun_time Crazy_Stuff"

Now how can I convert them into individual values (as rows) and insert them into a table?

There is a useful extended stored procedure that allows us to split values in a string considering space as the separator. It is xp_sscanf. It has some limitations but it can be useful in some scenario.

Here is the function I wrote for splitting values;

USE tempdb;

CREATE OR ALTER FUNCTION dbo.SplitString (@String nvarchar (4000))
 Tag nvarchar(200)

 DECLARE @Tag1 nvarchar(200)
   , @Tag2 nvarchar(200)
   , @Tag3 nvarchar(200)
   , @Tag4 nvarchar(200)
   , @Tag5 nvarchar(200)
   , @Tag6 nvarchar(200)
   , @Tag7 nvarchar(200)
   , @Tag8 nvarchar(200)
   , @Tag9 nvarchar(200)
   , @Tag10 nvarchar(200)

 EXEC xp_sscanf @String, '%s %s %s %s %s %s %s %s %s %s',   
  @Tag1 OUTPUT, @Tag2 OUTPUT, @Tag3 OUTPUT, @Tag4 OUTPUT, @Tag5 OUTPUT
  , @Tag6 OUTPUT, @Tag7 OUTPUT, @Tag8 OUTPUT, @Tag9 OUTPUT, @Tag10 OUTPUT;  

  SELECT REPLACE(@Tag1, '_', ' ') AS NewTag
  SELECT REPLACE(@Tag2, '_', ' ')
  SELECT REPLACE(@Tag3, '_', ' ')
  SELECT REPLACE(@Tag4, '_', ' ')
  SELECT REPLACE(@Tag5, '_', ' ')
  SELECT REPLACE(@Tag6, '_', ' ')
  SELECT REPLACE(@Tag7, '_', ' ')
  SELECT REPLACE(@Tag8, '_', ' ')
  SELECT REPLACE(@Tag9, '_', ' ')
  SELECT REPLACE(@Tag10, '_', ' ')) AS T


And this is how I can use it;

USE tempdb;

 DROP TABLE dbo.Tags;

 TagId int identity(1,1) primary key
 , Tag nvarchar(200)

DECLARE @String nvarchar(4000) = 'SQL Business_Intelligence Azure';

FROM dbo.SplitString (@String);

SELECT * FROM dbo.Tags;

As you see with the second script, I can simply pass the string received, get them split and insert to the required table.

Is this working in Azure SQL Database?
Unfortunately, it does not work in Azure SQL Database as Azure SQL does not support Extended Stored Procedure. However, good news is, SQL Server 2016 has a new function that can be used with both SQL Server 2016 and Azure SQL Database. Here is a sample code for it.

Sunday, February 5, 2017

On-Premises or Cloud - SQL Server Brain Basher of the Week #064

Here is the Brain Basher of this week.

During interviews, questions on deciding the hosting place for a planned database is very common. Sometime we ask the best place for a database by giving the certain criteria but sometime we ask that how you can decide considering general factors.

If you were asked the below question, what would be your answer?

"You have to create a database for one of the business processes related to the company. Hosting environment for the database is still undecided. We can either create it as an Azure SQL Database or can use the existing On-Premises SQL Server for hosting this database. What do you think?"

This is the Interview Question of the week.

Since there is no specific requirement, particularly on business need, most of the time, interviewee gets stuck with certain areas. However, as per my experience, it would be great if interviewee can explains the factors related for making such decisions along with pros and cons. Let's list out possible factors that influence the decision;
  • Stable Connection - How this database will be used? Frequently, with heavy transactions, or infrequently with less transactions? This is very important because if the database is an OLTP database with heavy transactions and business is depend on it, you need a stable, high speed internet connection. If the company has no such facility that cannot satisfy the speed and the bandwidth required, it is better not to move into cloud. However, getting high speed internet connection is a plan that will be executed soon, hosting in cloud should be considered.
  • Sensitive data - Some companies, specially some financial companies have policies on sensitive data. If the policy says that data should be maintained only in local servers, then hosting the database in cloud is not going to work. Not only the policies established by the company, there are some regulations enforced by the government, hence it has to be considered too. We may go for a hybrid version, that hosts part of the database in cloud and the rest in on-premises server for making sure that sensitive data is not moved out. This is possible with Microsoft SQL Server (SQL Server On-Premise Database files maintained in Azure).
  • Accessibility - How our database is going to be accessed? What we need to make sure is, it can be accessed by the audience who need it. Users may access it via a web application, using reports, or using tools like Microsoft Excel or Power BI by connecting directly. If we hosted, can all access data as they want? Does it require special software to be installed? Does it require special permissions? Do we have to purchase more licenses? These are things we have to consider on this. If we have to spend more which is not a part of the budget or it makes the usage complex, we might have to consider on-premise. However, certain scenario make on-premises hosting complex and costly as well.
  • Size - Is it going to be in megabytes, gigabytes or terabytes? If you expect that database will grow in terabytes within shorter time period, then it should be hosted in a local machine as 1 TB is one of the limitations with Azure SQL Database.
  • Security - Most common factor that pushes the implementation away from cloud. Many think that having the database hosted with a local server is more secured than placing the database in cloud. However, it is not true. If you go for a good provider like Microsoft, Amazon, there have been no security breaches recorded and they guarantee the security. Securing the local environment is very difficult, have to consider security implementations electronically as well as physically and someone has to continuously watch it, patch it, upgrade it when required and monitor it. Therefore, experts recommend hosting in cloud than in-house when security is considered.
  • High Availability and Disaster Recovery - If this is required and you have already implemented this using methods like Mirroring, Clustering or even with a backup/restore strategy, and most importantly it addresses required RPT and RPO (read more at http://dinesql.blogspot.com/2016/10/determine-backup-strategy-sql-server-interview-question.html), then you can consider local implementation. However, if you have not implemented anything, then it is always better to move into cloud as almost all providers support HA and DR.
  • Cost - No doubt, initial cost is always very high when it comes to hosting in local server. You may spend on purchasing servers, software licenses and some extra hardware for speeding up the processes. But the initial cost is very low when hosting in cloud. You may calculate for multiple years and compare, but in most cases, hosting in cloud is always cheaper than hosting in on-premises server.
  • Maintenance - If it is local, you need someone to do this. Installing service packs required, checking space required, performance tuning, monitoring,... many things. If it is cloud, most of these are addressed by the provider and only few need our attention. Example, Azure SQL Database suggests enhancements on indexing and statistics and we can use these recommendations for performance tuning that saves time and resources. So, if you do not have a dedicated person for performing these, go for cloud.
  • Features and Functionalities - You may need to implement some processes that needs certain set of functionalities. For example, if you need to send an email using SQL Server Database, you need to use Database Mail feature. If you need to execute a business login on a scheduled time, you need to use SQL Server Agent. You know that these features are not available with Azure SQL Database. Not only that, it does not support all TSQL commands, standard partitioning among multiple volumes (with Azure SQL Database, partitioning is possible but volumes cannot be decided. If need, federation can be considered) is not available. Therefore, we need to think about features and functionalities that will be used with the database and then decide whether we can host in a local server or cloud.
There are more but interviewee should talk about at least mentioned factors. That makes sure that the interviewee is smart and knowledgeable on the subject, not just a person who implement anything when asked.

Saturday, February 4, 2017

What if I create an Azure SQL Database using Management Studio (SSMS)?

Will there be differences if I create my Azure SQL Database using Management Studio instead of Azure Portal?

Yes, there are. One major difference I noticed is, Snapshot Isolation. By default it is on in an Azure SQL Database along with Is Read Committed Snapshot On;

[Database created using the portal]

However, if you create a database using SSMS, it does not set as expected;

[Database created using SSMS]

As you see, even though Snapshot Isolation is on, Is Read Committed Snapshot On is set as false. Not sure how it is happening, have already asked from experts, will update this with reasons once I received a reply.

Friday, February 3, 2017

Is it possible to read data while they are being modified in Azure SQL Database with default settings?

Somewhere in 2011, I wrote an article titled Isolation Level in SQL Server that explains different Isolation Levels in SQL Server. It is based on an older version of SQL Server, but it is still valid for new versions as well.

Since now we have SQL Server as a SaaS, we slowly move to Azure SQL Database. As you know, Azure SQL Database is not same as On-Premises SQL Database, it has some differences; one is related to Isolation Level.

Before talking about the difference, let's write a sample code and see how it works in both Azure SQL Server and On-Premises SQL Server databases. For this, I have created a database called Sales in both environments.

Once databases are availble, the blow code that adds a table and inserts records, needs to be run in both local and Azure SQL Databases;

CREATE TABLE dbo.Products
 ProductId int identity(1,1) Primary Key
 , Name varchar(100) not null
 , Color varchar(20) not null

INSERT INTO dbo.Products
 (Name, Color)
 ('Product 01', 'Black')
 , ('Product 02', 'Red')
 , ('Product 03', 'Blue');

Now we have the same table in both databases. Let's first test with local database.

Open a new connection and execute the following code. It creates a transaction and updates one of the records. Note that, we do not either rollback or commit.

-- First connection

 UPDATE dbo.Products
  SET Color = 'Green'
 WHERE ProductId = 2;

Open another connection and execute the code given below. This connection tries to retrieve the record that is being modified by first connection.

-- Second connection

SELECT * FROM dbo.Products
WHERE ProductId = 2;

The result of the second connection is shown in the below image. As you see, we cannot immediately see the record as the record is being modified.

Let's go back to the first connection and rollback the transaction.

-- First connection


Once it is done, second connection will be able to see records.

This is the standard/default behavior of SQL Server. Generally, we cannot access records that are being modified because default Isolation Level set is Read Committed. Now let's do the same in Azure SQL Database.

Open a new connection for Azure Sales Database and execute the same code that starts the transaction.

Open another connection for Azure Sales Database and execute the same code for retrieving records. Here is the result of it.

As you see, there is no issue with Azure Sales database. Second connection can retrieve records even though the record is being modified by another connection.

This is one of the differences between Azure SQL Database and Local SQL Database. The reason for this is, by default Snapshot Isolation Level is enabled in Azure SQL Database. Because of that, a version of the old record is created before the modification and it will be used for other users if request.

Can we turn this behavior off in Azure SQL Server? No, it is not possible, if you try to change it via SSMA, you will experience the below error;

However, you can change Is Read Committed Snapshot On that changes the default behavior.

If you set this to false, then second connection will not be able to see records while first connection modifying records but second connection can set the Isolation Level manually and see records.

Thursday, February 2, 2017

Is Azure SQL Database a PaaS, DaaS or SaaS?

I was confused with this too, but added the most common one to my slides when presenting my session; Getting Ready for Azure SQL Database. Is Azure SQL Database a Platform-as-a-Service, Database-as-a-Service or Software-as-a-Service?

First of all, let's try to understand these models. In addition to the ones mentioned, there is another model called IaaS - Infrastructure as a Service. Here is simple and shortened explanation on these models;
  • IaaS - Infrastructure as a Service
    When it comes to Cloud Computing, getting required Servers, Storage, Network as an on-demand service and maintenance of them is somone's else (like Azure, Amazon) responsibility, is called as IaaS. It is something like we have outsourced the entire IS service including physical components. Microsoft Virtual Machines in Azure is one of the examples for this.
  • PaaS - Platform as a Service
    This model sits on IaaS, offering a complete development and deployment environment. In addition to the services you get with IaaS, this offers services on Programming Languages, Database Services, etc. for making solutions. Microsoft Azure is a good example for this.
  • SaaS - Software as a Service
    This is the top layer of the Cloud Computing and it sits on PaaS, means on top of both IaaS and PaaS. This offers software required as a service on an on-demand way or as a subscription. Example, Gmail from Google that allows us to use email service just using the browser.
  • DaaS - Database as a Service
    This is not something much popular or not something always getting referred because it is an extension or an optimized SaaS model. This provides all RDMS functionalities with cloud computing and sits on PaaS.
This is how it looks like;

And this is the distribution of management;

Note sure the original author of second image, taken from one of Mircosoft pages

Now SQL Server is a PaaS, DaaS or SaaS? It is considers as PaaS but it is actually a SaaS. Can we consider it as a DaaS. Yes it is possible but I think that we should consider it as SaaS because it offers more that what traditional RDMS offers.

Wednesday, February 1, 2017

Azure SQL Database - Auditing & Threat Detection

Has your database been attacked by someone?

Most of our applications are data-driven applications and attackers can use various techniques for getting into the database. One of the famous and most common techniques is SQL Injection which is a Code-Injection that modifies the statement written in the application and get it executed as attacker wants.

Here is a link for reading more on SQL-Injection: http://www.w3schools.com/sql/sql_injection.asp

Whether the database is a On-Premises database or Cloud database, if the code has been written poorly, then it is exposed for attackers. And the biggest issue is, in most cases, we do not know whether the database is attacked or not. What if someone can detects possible attacks and notifies you immediately? Yes Microsoft Azure SQL Server can do it.

If you have worked with Azure SQL Servers, you have already noticed a Setting called Auditing & Threat Detection in the SQL Server Blade. It allows you to set Auditing and in addition to that, we can enable a feature called Threat Detection which is still in Preview mode (means it will not always work as you expected until the final version is released.).

Read the post written on Auditing for more info on it: How to check success and failure logins attempts in Azure SQL Database

Threat Detection detects anomalous database activities that are considered as security threats and notifies us with details. This means, it can detects possible SQL-Injections as well. However, as per my experienced, it does not detect immediately at the initial tries but later, notification is sent immediately. As per some experts, it needs some times to learn the patterns for notifying but it is too early to make a such statement as this is still in Preview mode.

Let's see how we can use Threat Detection with an example. I created a Azure SQL database with following schema and inserted a set of records. Assume that this Sales database contains Sales made by Sales Representatives.

-- Create User table
 UserId int identity(1,1) PRIMARY KEY
 , UserName varchar(10) NOT NULL
 , Password varbinary(1000) NOT NULL

 (UserName, Password)
 ('Dinesh', ENCRYPTBYPASSPHRASE('DK?DF%23:D', 'Dinesh123'))
 , ('Jane', ENCRYPTBYPASSPHRASE('DK?DF%23:D', 'Jane123'))
 , ('Jack', ENCRYPTBYPASSPHRASE('DK?DF%23:D', 'Jack123'));

-- Create Sales table
 SalesId int identity(1,1) PRIMARY KEY
 , UserId int NOT NULL
 , Constraint FK_Sales_Users FOREIGN KEY (UserId) REFERENCES dbo.Users (UserId)
 , SalesDate date NOT NULL
 , SalesAmount decimal(16,2)

 (UserId, SalesDate, SalesAmount)
 (1, '2016-01-01', 10000.00)
 , (1, '2016-01-02', 12000.00)
 , (1, '2016-01-03', 14000.00)
 , (2, '2016-01-01', 9000.00)
 , (2, '2016-01-02', 110000.00)
 , (3, '2016-01-01', 17000.00)
 , (3, '2016-01-02', 126000.00)
 , (3, '2016-01-03', 19000.00)

Next step is creating a simple web application. I created an ASP.Net Web Application that contents with the Azure SQL Database created. I added a Web Form that contains two input boxes for accepting User Id and Password, a button for Loading User's Sales and a GridView for showing retrieved data.

This is Button-Click method written.

string sql = "SELECT u.UserName, s.SalesDate, s.SalesAmount FROM dbo.Sales s INNER JOIN dbo.Users u ON s.UserId = u.UserId WHERE ";
sql += " u.UserName = '" + TextBoxUserId.Text + "' AND DECRYPTBYPASSPHRASE('DK?DF%23:D', u.Password) =  '" + TextBoxPassword.Text + "'";

string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sql, connection);

SqlDataReader reader = command.ExecuteReader();

DataTable table = new DataTable();
table.Columns.Add("UserName", System.Type.GetType("System.String"));
table.Columns.Add("SalesDate", System.Type.GetType("System.DateTime"));
table.Columns.Add("SalesAmount", System.Type.GetType("System.Decimal"));

DataRow row;
while (reader.Read())
    row = table.NewRow();
    row["UserName"] = reader["UserName"].ToString();
    row["SalesDate"] = Convert.ToDateTime( reader["SalesDate"]);
    row["SalesAmount"] = Convert.ToDecimal( reader["SalesAmount"]);
GridView1.DataSource = table;


As you see, SqlCommand is not a parameterized command, hence attacker can use a simple SQL-Injection to modify the statement and get different set of data.

All done. If I check for User:Dinesh, I will be seeing Sales related to him.

However, if you use a SQL-Injection like below, I can get all Sales Records regardless of the user. 

We are not going to talk about the SQL-Injection and the poor code written in the Web Application. Let's see how we can get a notification from Azure when something like this is tried out by someone.

As you know, all we have to do is, enable Threat Detection. This how it has to be set up in Azure SQL Server Blade via Auditing & Threat Detection Setting.

Everything required is done. As you see, Threat Detection is ON and all Threat Detection types have been selected. I have added my email address for receiving alerts. Note that, you need to enable Auditing for enabling Threat Detection and it should be configured to all Audited Events.

Now if you try again with a SQL-Injection, you will immediately get a mail with details;

I can click on the View Related SQL Statements and see more details. If I click, it opens the Audit Records Blade that shows all records activities.

As you see, it is very easy to set up and you can be alerted on all suspicious activities. Try and see.