The set identity_insert command in SQL Server, as the name implies, allows the user to insert explicit values into the identity column of a table. Now, someone might wonder, “why on Earth would somebody want to do that?!” Oh well, you know, there is always a good reason! 🙂 I can…
What is the IDENTITY_CACHE Option in SQL Server? The IDENTITY_CACHE option, is a new database scoped configuration option in SQL Server 2017 and Azure SQL Database. This option is set to ON by default. The concept of identity caching in SQL Server is that the Database Engine stores in cache…
SQL Server Management Studio (SSMS) for some time now, is not included in the SQL Server media. Instead, it is being provided as a standalone free program which can be downloaded from Microsoft’s website. The decoupling between SSMS and SQL Server Installation Media This is a good thing and…
Sometimes, when working with T-SQL scripts, you might want to skip the message “(N row(s) affected)” message. This article explains how to suppress the “N Row(s) Affected” output message in SQL Server. Explanation You get this message when you execute T-SQL statements or stored procedures that they affect rows.…
A long-awaited string function added to SQL Server 2016 (and later) the is STRING_SPLIT function. Read on to better understand this cool function and see examples of how you can use it. What does the STRING_SPLIT Function in SQL Server do? As the name implies: this function splits the given…
Temporal tables in SQL Server 2016 and later as well as in Azure SQL Database is a new type of user table which was was introduced in ISO/ANSI SQL 2011. Read on to learn more temporal tables and see a comprehensive example that uses them. What are Temporal Tables in…
JSON stands for JavaScript Object Notation and it is widely used for data representation in the data services world. With built-In JSON support in SQL Server and Azure SQL Database, you can format query results as JSON by adding the “FOR JSON” clause to a SELECT statement in SQL Server…
This article discusses the behavior of the BCP utility when extracting data from SQL Server tables to files and more specifically to CSV files. Example: Description Imagine that you have the below table: As you can see, records 2 and 3 contain null values. Then you export the table contents…
A few weeks ago I was contacted by Webucator, an online training provider that requested my permission to include one of my articles into a free video-training series called “SQL Server Solutions from the Web“. This is one of my articles on FileTables with title: “How to Import and…
Introduction There are many scenarios in the DBMSs world where you might need to use a linked server between two different SQL Server instances in order to execute direct queries between them. However, the majority of T-SQL auto-complete tools have difficulties to list the objects (i.e. tables) of the linked server’s…
Good evening (or good morning) friends all around the globe! Wherever you are, whatever you do, wishing you a nice rest of the day (or night!) 🙂 This is the last of a series of posts that deal with the task of importing and exporting unstructured data in SQL Server.…
A few weeks ago I posted an article on how to import and export unstructured data in SQL Server 2005 by using the “image” data type. In this article I will show how you can manipulate unstructured data in SQL Server 2008 or later by using the FILESTREAM feature. FILESTREAM…
Importing and exporting unstructured data from a database is a common practice, especially in large applications. SQL Server 2008 introduced the FILESTREAM feature that allows storing unstructured data (i.e. music, video, documents, etc.) onto the NTFS file system and manipulating it via the Database Engine. SQL Server 2012 introduced FileTables which is…
SQL Server programmability objects such as stored procedures, functions, assemblies, etc. are widely used, especially in cases of a major database design where you want to have a well-structured database with code reuse and performance. The need to know where are Programmability Objects Stored in SQL Server Even though…
Scenario: You have a fixed-length table column for which you want to add leading characters. Example: In this scenario we have column “ID” which is a varchar field of length 10 and we want to add leading zeros so that its values always have 10 characters. –Create Development/Test Database CREATE DATABASE…