Using WHERE Clause in SQL Server Query

In this blog we shall look into the usage of WHERE clause in SQL Server. WHERE clause can be used in Select, Update, Delete statements to filter the rows being affected by the query. In fact it can be used inside an Insert statement too which has a Select statement. WHERE clause ensures the data is filtered when it is retrieved in select statements or it ensures only those records are affected when updating / deleting is performed.

A WHERE clause contains one or more conditions depending on which the records are filtered in the table(s). The conditions in WHERE clause can be based on one or more operators and the operators that can be used in a WHERE clause can be =, <>, != , >, <, BETWEEN, IN, LIKE, NOT and many more. When there are multiple conditions, those conditions can be combined with AND / OR.

Let us look at some examples to understand different types of WHERE clause.

Examples for WHERE Clause

Example 1: Using “=”

SELECT EmployeeID, Title, Gender, HireDate
 FROM AdventureWorks.HumanResources.Employee
 WHERE Title = 'Production Technician - WC20'

 In the above SQL statement, the condition used in WHERE ensure that those records that have the value “Production Technician – WC20” in the Title column will be displayed. You might have noticed that the string value Production Technician – WC20 is enclosed in single quotes, the reason being that all string values and Date and time related columns need to compared with at value which is enclosed in single quotes.

Example 2 A: Using “LIKE” and %

SELECT EmployeeID, Title, Gender, HireDate
FROM AdventureWorks.HumanResources.Employee
WHERE Title LIKE '%Technician'

In the above SQL statement, the condition used in WHERE is more wider than the one in previous example. It uses LIKE keyword to filter those records that end with the value “Technician” in the column Title. Notice that when we use LIKE we do not use = symbol as we would like to filter the data according to a patterm matching and the criteria in this condition is any rows / records which end with the string Technician. You also notice that ‘%’ is used before the string, which indicates that any string value in that position is valid in the condition.

SELECT EmployeeID, Title, Gender, HireDate
FROM AdventureWorks.HumanResources.Employee
WHERE Title LIKE '%Technician%'

Example 2 B: Using “LIKE” and %

In this statement, the condition is little bit changed to that in previous example. It has an additional ‘%’ at the end of the expression, indicating that any record is a valid record which contains the string “Technician” in column Title, no matter if it is at the beginning or startig or the end of the string value.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Finding Log Size for all Databases in SQL Server

Monitoring the size of Transaction Log files is one of those important tasks for a SQL Server Database Administrator. I monitor regularly in order to ensure that my database log files do not grow tremendously in size and potentially run out of space. The script in this article will give the list of Databases and their Transaction Log files size in MB in the descending order.

Script used in this blog…

SELECT INSTANCE_NAME AS [DATABASE],
(CNTR_VALUE/1000) AS Size_In_MB FROM MASTER.dbo.SYSPERFINFO
WHERE COUNTER_NAME LIKE '%Log File(s) Size (KB)%'
AND INSTANCE_NAME NOT IN ('_TOTAL','mssqlsystemresuorce')
ORDER BY Size_In_MB DESC

 You may also take a look at one of my previous blogs related to transaction logs. Find Transaction Log Space Used

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

How to Troubleshoot SQL Server Connection Problems

In this blog we shall take a look at the steps to troubleshoot connection problems in SQL Sever. We need to keep in mind, there are lot of things that might be part of such an issue and also we need to consider which user or what application is reporting this connection problems. The exact cause of the connection failure can only be troubleshooted by testing and trying multiple remedial steps, eliminate that step and keep moving forward.

  • Is SQL Server currently running or SQL Server accepting new connections?
    • Make sure that SQL Server on that machine is currently running, before making any other attempts / trials. SQL Server status should be checked that it is not stopped  or paused ( of course the error message indicates if the SQL Server is not accepting any new connections). Either way see that SQL Server is running currently. Start SQL Server if needed from Configuration Manager or Services etc.
  • Are the required Protocols Enabled?
    • Ensure that all required protocols are enabled on the SQL Server. There is nothing wrong with enabling all protocols, but a word of caution about the VIA protocol, it should be disabled on most of the systems (depends on Server Hardware) and should be enabled only upon verifying with the Hardware Vendor.
  • Check the SQL Server Authentication Mode.
    • This step is also important, any connection coming to SQL Server either has to be a Windows User or a SQL Server user. If you are trying to connect using a SQL Server login, make sure the authentication mode on the SQL is Mixed Mode, else the login attemps will fail and you will not be able to connect to SQL Server.
  • Is the User / Login status active or enabled?
    • Make sure you are connecting to the SQL Server using an account which is not disabled. If a different user complains about connection issues (different credentials), make sure that account is not disabled in SQL Server (if SQL Login) or the Windows Active Directory account is not expired / locked.
  • Check the Firewall settings.
    • Make sure that the firewall is not blocking the port on which SQL Server listens. 
  • Check the SQL connection information.
    •  You need to check if the SQL Server being referred in the connection string is a default instance or a named instance. If the instance is a named instance, you will need to ensure that the connection string includes the instance information also, i.e, it has to be something like the IP Address,Port # such as 192.168.10.123,3456 or the Servername\Instance such as PhysicalServer\InstanceName.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty