SQL Server: Error – ‘The operation ‘AUTO_CLOSE’ is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.’

The operation ‘AUTO_CLOSE’ is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup. You are getting this error because your database set to AUTO_CLOSE ON, which is the default setting for any database in SQL Server. If you are trying to create a memory-optimized file group or data file to use In-Memory OLTP feature of SQL Server, you Read more about SQL Server: Error – ‘The operation ‘AUTO_CLOSE’ is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.’[…]

Different types of keys in SQL Server

What is Key in SQL? SQL Keys are logical database objects that play a very important role in database. Strictly speaking, a KEY is a constraint or rule that is used for data consistency and accuracy, to maintain uniqueness, to create relationships between database object and optimization purposes etc. For Example: A NOT NULL constraint is a rule that Read more about Different types of keys in SQL Server[…]

Table Variables in SQL Server

In Previous post Temporary Tables in SQL Server, we learnt what is temporary tables and how they are used for storing data temporarily. There is an another database object which can be used for same called Table Variables. In this post, we will discuss what are temporary tables when to use them in details. What are Table Variables? Read more about Table Variables in SQL Server[…]

Temporary tables in sql server

Sometimes, manipulation of data involves storing of data temporarily. Temporary Tables are one of the most common tables used for the same purpose. In this post, we will discuss what are temporary tables when to use them in details. What are Temporary Tables? A temporary table in SQL Server, as the name suggests, is a database table Read more about Temporary tables in sql server[…]

Best practices of writing stored procedure

In Previous post  “Stored Procedure in MS SQL Server”, we learnt about the stored procedure basics and the benefits and flexibility it provides. Now, It’s time to learn the best practices to write well crafted, good performance tuned and concise stored procedures. I’m listing some of the best practices that must be followed every time you write Stored Read more about Best practices of writing stored procedure[…]

SQL Server Management Studio Shortcut keys

Do you want to improve your productivity as a DBA or developer in daily works? One simplest way is to use as many shortcuts as possible instead of using your mouse and the menus while working.  In this article, I am going to show you my favourite SQL Server Management Studio Shortcut keys. Starting SQL Read more about SQL Server Management Studio Shortcut keys[…]

How to concatenate multi-rows column into a single string in SQL Server

OH… So, you came across a problem scenario where you need to concatenate multiple rows of a column into single string of text. There are many ways to concatenate multi-rows into a string. Let’s understand all the ways by T-SQL examples to satisfy the following problem query. “We have a CITY (ID, Name) table and Read more about How to concatenate multi-rows column into a single string in SQL Server[…]

Cursors in SQL Server

In this tutorial, you will learn cursors concept in SQL Server and will Learn how to create, use it with syntax and examples. Why Cursor Introduced? Relational databases are very good at processing, handling, managing data in a result sets (The complete set of rows returned by the T-SQL statement satisfying the conditions if any Read more about Cursors in SQL Server[…]

Set Operations in SQL Server – UNION, UNION ALL, INTERSECT, EXCEPT

This tutorial explains a useful tool called SQL Set Operators with syntax and examples which helps in combining information of similar type from multiple tables or queries. The set operator’s operations look like SQL JOIN although there is a big difference. SQL joins tends to combine columns from different tables, whereas SQL set operators combine rows from distinct queries. Read more about Set Operations in SQL Server – UNION, UNION ALL, INTERSECT, EXCEPT[…]

SQL Index in MS SQL Server

In this tutorial, I will give you the basic idea about the index – the most effective tuning method to store data to access it quickly and efficiently in SQL Server and will Learn how to create, rename and drop indexes to improve the performance of the query being executed on the table in SQL Server with Read more about SQL Index in MS SQL Server[…]