How to Design Memory Optimize Database using SQL Server In-Memory OLTP

‘In-Memory OLTP’ also known as ‘Hekaton’ and ‘In-Memory Optimization’ is in-memory processing technology that allows you to get most out of SQL Server for your transactional workloads. This feature was introduced with SQL Server 2014 release. This post demonstrates the usage of In-Memory OLTP feature with examples. Create A Database 1 2 — Create a Read more about How to Design Memory Optimize Database using SQL Server In-Memory OLTP[…]

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[…]

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[…]

Stored Procedure in MS SQL Server

A Stored Procedure is nothing but a group of one or more T-SQL statements and can be triggered from either a program or another stored procedure to perform a specific task. They allow developers to reuse the code repeatedly. So, if you have a query that requires to write again and again, then better save Read more about Stored Procedure in MS SQL Server[…]