How To Alter User-Defined Table Types in SQL Server

I cannot use ALTER to modify the type nor can I drop and recreate it since it is being referenced by existing Stored Procedures. That means, reference must be removed first. In this post, We will learn how to alter User-Defined Table types in SQL Server. Example Case: Let’s say we have multiple stored procedure Read more about How To Alter User-Defined Table Types in SQL Server[…]

How To Get Row Count For All Tables In SQL Server Database

Recently, I was working in analyzing a healthcare database where one simple task was to identify the big tables in terms of “Number of records”. So. I needed to get the row count for each tables in a SQL Server Database. There are many way to get this data. See the below T-SQL scripts: 1. Read more about How To Get Row Count For All Tables In SQL Server Database[…]

How to Track Stored Procedure, Function, View changes in SQL Server

Do you want to track the changes made on database objects like Stored Procedures, Views, Functions? SQL Server supports DDL Triggers that can be used to accomplish this task. SQL Server DDL Triggers is a special kind of trigger that fire in response to Data Definition Language (DDL) statements – CREATE, ALTER, DROP (Table, Function, Read more about How to Track Stored Procedure, Function, View changes in SQL Server[…]

How to search a specific text in a SQL Server Stored Procedures, Functions, Views or Triggers

As a DB developer, I often needed to find a stored procedures, functions, views and triggers  that contains a specific piece of text string. For an example, say I want to find any Stored Procedures, Views or Functions that contains a text ‘codemog’. So, How to search a specific text in a SQL Server Stored Procedures,Views or functions Read more about How to search a specific text in a SQL Server Stored Procedures, Functions, Views or Triggers[…]

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

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

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