SQL Server Accidental DBA

SQL Server Error Message 6263 – Enable SQL Server for CLR Support

The SQL Server Error Message 6263 occurs when you try to execute a CLR/.Net procedure and it is disabled.  The complete error message somewhat looks like as below Msg 6263, Level …
Read More »

Reverse string in SQL Server

SQL Server has in build function REVERSE to reverse string. A T-SQL example is given below The function is very much direct. It takes an input string and returns the reversed …
Read More »

Recursive triggers in SQL Server

In this blog we’ll look at Recursive triggers in SQL Server. Triggers are said to be recursive/nested when a trigger on a table calls another trigger on same or different table …
Read More »

Function to parse alphanumeric characters from string in SQL Server

Alphanumeric string consists of alphabets and numbers for example a string 123xyz456abc is an alphanumeric string. Many a times it is required to extract only alphanumeric characters from a string excluding …
Read More »

Find working days between two dates in SQL Server

Many a times it is required to find working days between two dates. We do know that DATEDIFF function can be used to find differences between two dates; however the working …
Read More »

Could not find a table or object named ''. check sysobjects - SQL Server Error 2501

The error “Could not find a table or object named ”. check sysobjects” tell that SQL Server can’t find the table or the object name being referenced in a query and …
Read More »

T SQL code formatter

An important aspect of writing code is formatting. No one likes badly formatted code. It’s hard to understand/maintain for people inheriting it and even for the author, when he/she checks it …
Read More »

Except vs NOT IN performance in SQL Server

In this blog I’ll look at EXCEPT vs NOT IN performance differences. EXCEPT command introduced in SQL Server 2005 is used to compare two tables/view/sub queries. It returns distinct rows from …
Read More »

Disable Enable Indexes SQL Server

One of the most common questions on forums is how to disable enable indexes SQL Server. Index can be disabled or enabled as shown below USE AdventureWorks2014 GO -- Query1: disable …
Read More »

List all databases in SQL Server

Here are few ways  to list all databases in SQL Server SELECT database_id, name from sys.databases GO SELECT dbid, name from sysdatabases GO sp_helpdb GO sp_databases GO sp_MSforeachDB ' PRINT ''?'' …
Read More »

SQL Server error 130 - Cannot perform an aggregate function on an expression containing an aggregate

or a sub query SQL Server throws SQL Server error 130 when parameter to an aggregate function is either an expression or a sub query. An aggregate function such as AVG/MAX/COUNT …
Read More »

SQL Server tablesample example

The TABLESAMPLE clause is used to limit the number of rows returned from a table to a number or percentage of rows. Let’s look at TABLESAMPLE example. The TABLESAMPLE returns fairly …
Read More »

T-SQL find tables without clustered index

One of the important tasks when optimizing a SQL Server for performance is to find and convert heaps to clustered index. A HEAP is a messy collection of rows piled up …
Read More »

Disadvantages of triggers in SQL Server

DML Triggers are stored procedures which are automatically executed when a DML operation is performed on a table/view defined in a trigger. A good use of trigger is to audit table …
Read More »

Change TSQL batch separator GO

GO is not a T-SQL statement instead it’s a command to separate T-SQL statements from one another into different batches, recognized by SQLCMD, OSQL and SSMS. To change TSQL batch separator …
Read More »

ASCII to Decimal and Decimal to ASCII in SQL Server

ASCII to decimal conversion can be done as shown below The ASCII function takes one character as parameter and returns its equivalent integer ASCII value. Decimal to ASCII conversion is done …
Read More »

SQL Server error code 544 - Cannot insert explicit value for identity column

The SQL Server error code 544 – Cannot insert explicit value for identity column, occurs when one tries to insert an explicit value for an identity column and identity_insert is off …
Read More »

Primary key not null in SQL Server

A Jr. Developer asked me why a primary key not null in SQL Server. A primary key uniquely identifies a row in a table and a NULL can’t identify any row. …
Read More »

Trace flags in SQL Server

Trace flags in SQL Server are used to switch ON/OFF specific SQL Server features temporarily. A trace flag can be a global or a session only. A global trace flag effects …
Read More »

Get SQL Server configuration information

The sys.configurations view can be used to get SQL Server configuration information. Let’s analyze the output of sys.configurations view. As shown in above snapshot, the sys.configuration view lists down different server …
Read More »

« Newer EntriesOlder Entries »

One Comment on “SQL Server Accidental DBA”

Leave a Reply

Your email address will not be published. Required fields are marked *