Tips for Effective SQL Server Database Maintenance

“ I have been asked several times for advice on how to effectively maintain a production database. Sometimes the questions come from none-DBAs who are implementing new solutions and want help fine-tuning maintenance practices to fit their new databases’ characteristics. As with the majority of tasks and procedures in the IT world, there isn’t an easy one-size-fits-all solution for effective database maintenance, but there are some key areas that nearly always need to be addressed. My top five areas of…

Read More

Get Numbers from Alpha Numeric String – Get Numeric Numbers Only

So, someone had stored thousands of SSN numbers in a table but now they have an issue because some of them are incomplete or are not correctly formatted. Thank you to blog.sqlauthority.com and little creativity I have come up with a SQL script that fixes this issue. Here is the T/SQL script:

Read More

T-SQL: RIGHT, LEFT, SUBSTRING and CHARINDEX Functions

This article explains the base functionality and uses of the LEFT, RIGHT, SUBSTRING and CHARINDEX functions in SQL. Samples script: Sample Results: Variable Data Results ——————– HELLO WORLD RIGHT Results ————- RLD LEFT Results ———— HEL CHARINDEX Results —————– 6 SUBSTRING Results —————– LO WO Combined Results ——————– HELLO  

Read More

Find the Server Name or Instance Name using T-SQL

The following instructions are supposed to return the same results,  but sometimes the results can be different. The reason is that SERVERPROPERTY automatically reports changes in the network name of the computer, while @@SERVERNAME don’t. SELECT CONVERT(sysname, SERVERPROPERTY(N’servername’)) GO SELECT @@SERVERNAME When they are run on the default instance they return just the server name, but when run over a named instance they return the server name in the following format: servername/instancename.

Read More

List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.

Read More

Selecting Domain from Email Address

Recently I came across a quick need where I needed to retrieve domain of the email address. The email address is in the database table. I quickly wrote following script which will extract the domain and will also count how many email addresses are there with the same domain address.

Read More