Consider a case that we want to select common rows containing name, designation, department from two tables named staff, officers’
In this scenario we can use the INTERSECT operands in following way
SELECT nameofStaff, designationofStaff, departmentofStaff FROM staff INTERSECT SELECT nameofOfficer, designationofOfficer, departmentofOfficer FROM Officers
Above script will provide us distinct rows that are common on given columns. Number of columns should be same on both sides of INTERSECT. And data types of columns should also match. For more details please visit BOL
It may be required to change NULL value of a column with a valid alternate value. This scenario is mostly there for report generation purpose. T-SQL provide a very handy function ISNULL() to replace NULL value with alternate value. Following is syntax for using ISNULL()
ISNULL accepts two parameters. First parameter will always be the column for which alternate is required in case of its NULL value. Second parameter is the alternate value that is required to replace the NULL value. Second value may be any other column or other value. Alternate value may be implicitly converted to data type of first parameter. For example you may provide datetime as alternate value for character family data type but you can not provide character family data type as alternate of date time value.
For example if we are required to display City as alternate of NULL value of AddressLine2
USE AdventureWorks GO SELECT AddressLine1, ISNULL(AddressLine2,city), PostalCode FROM Person.address
Similarly to display a string of characters as alternate of NULL AddressLine2
USE AdventureWorks GO SELECT AddressLine1, ISNULL(AddressLine2,'No Address Line 2'), PostalCode FROM Person.address
This user defined function would be very helpful while working with string concatenations. If one of the strings being concatenated is NULL then end result of concatenation would be NULL. To avoid this situation use ISNULL where NULL value is expected.Just consider following two examples for use and effect of ISNULL in string concatenation.
-- String concatenation with NULL value SELECT 'Value1 '+'Value2 '+NULL AS ConcatenatedString GO -- String concatenation by using ISNULL SELECT 'Value1 '+'Value2 '+isnull(NULL, 'NULL-Replaced') AS ConcatenatedString GO
In one of previous posts about Dealing the growth of Transactional Log Files i discussed briefly about function of transactional log files in SQL Server and also there were some prevention measures to prevent growth of transactional log file on large amount.
There may be situation that we have to get rid of grown file. In that case obvious work around is to truncate the transactional log. To truncate the transaction log for AdventureWorks database
ALTER DATABASE AdvetureWorks SET recovery SIMPLE GO DBCC shrinkfile (LogFileNameofAdventureWorks) GO ALTER DATABASE AdventureWorks SET recovery FULL GO
As in case of simple recovery mode SQL Server transaction log is not maintained for that database. So the transactional log will be dropped for database when recovery model is set to simple.
It is important to mention that on going transactions should be considered before changing recovery models.
Use AdventureWorks GO SELECT Name, Modify_date FROM sys.tables WHERE name = 'ProductPhoto' GO
It is important to understand that modification date means modification in structure or properties of a table. Any operation on data of table does not have any link to modification date of that table that is being retrieved through this script. And to get this information for all of your tables in a database, just remove the where clause.
My article related to Dropping multiple objects with a single DROP statement is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Dropping multiple objects with a single DROP statement
Column names are commonly used in order by clause of any select statement in T-SQL. For example a simple select statement in AdventureWorks database using order by clause is as follows.
USE AdventureWorks GO SELECT name, groupName, modifieddate FROM humanresources.department ORDER BY Groupname GO
Result is obviously three columns ordered by [GroupName]. Let us use another more simple approach while using order by clause. Instead of using column name, we may bring order to our results by using integer in order by clause. For example ordering our result set through integer, we have following way.
USE AdventureWorks GO SELECT name, groupName, modifieddate FROM humanresources.department ORDER BY 2 GO
Result of both of our scripts is in same order. SQL Server starts count of columns used in select list by 1 and increments by 1. In case of above scripts used, as we have three columns in select list, so we may use integer value from 1 to 3 for first, second and third column respectively. Value provided may not be less than 1. And also it may not be greater than number of columns used in select list. As in our case maximum value is 3.
This syntax of order by using integers may be used in all versions of SQL Server. It is simple than using full column names. Changing the order of columns in select list will also change the criteria being used in order by clause.
A short coming is there in this approach. By using integer in order by clause you can not order by any column that is not in select list. While by using column names in order list you can order even by the column that is not in select list.