T/SQL Splint String/Text

One common task performed in database applications is given a full name, how can this be split into the first name and last name.  In this article, three different methods are discussed to perform this task using three different functions in SQL Server.  It is assumed in this article that the full name is just made up of one first name and one last name. Using SUBSTRING Function The first method is the use of the SUBSTRING string function,…

Read More

Built-in tools troubleshoot SQL Server memory usage

Dynamic management views Dynamic management views, first introduced with SQL Server 2005, provide information about server and database state. These views are useful for monitoring overall SQL Server health, identifying the root cause of SQL Server performance bottlenecks, and tuning SQL Server instance or database performance. The following is a list of dynamic management views available in SQL Server 20008R2, SQL Server 2012 and SQL Server 2014. You can use these views to obtain SQL Server memory usage information;…

Read More

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

UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task. Let us see the following example. We have two tables Table 1…

Read More

SELECT columns using the LIKE keyword

Using the WHERE clause to return on the records in the Employee table where the employees are a manager of some type. This example uses the % character for any number of characters. Using the LIKE keyword to return records from the Person table where users’ first names end in “ary”. This example uses the _ characters to represent a single character. Using the LIKE keyword to return records from the Person table where users’ first names end in…

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