Today is one of those rare days where you can see a lots of 1s in the date i.e, 11/11/2011. To add to that, the clock ticks 11:11:11 everyday, so I planned to capture the SQL Server datetime value returned by GETDATE() function. As I have to execute GETDATE() function at that exact moment, which is very unlikely, I used WAITFOR TIME statement as shown below..
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
Ever faced a sitaution where you had to find out all the database files (Data and Log) of a SQL Instance?
I came across this situaion many times, especially when I am working with a new SQL Instance which was taken over by my team OR when the previous admin of that box has left the company and its now my turn to maintain / administer that instance.
In order to list the files, we just need to query the system table sysaltfiles. The picture below is a screen show that I ran earlier today on one of my SQL Instances.
SELECT * FROM SYSALTFILES
This query can be further tweaked to get only the data files or only the log files by adding where clause and filter by griupid column. Groupid = 1 indicates it is a data file and groupid = 0 indicates it is a log file.
Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.
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.
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.