0 SQL Server 2012 - New Logical Functions

SQL Server 2012 introduces a new category of built-in scalar functions, the logical functions, which as the name implies, performs logical operations.  SQL Server 2012 introduces 2 new scalar functions under this newly created logical function category, namely the CHOOSE function and the IIF function.  The CHOOSE logical function returns the item at the specified index from a list of values while the IIF logical function returns one of two values, depending on whether the Boolean expression evaluates to true or false. CHOOSE Logical Function The CHOOSE logical function acts like an index into an array, where the array is composed...

0 SQL Server 2012 - New String Functions

SQL Server 2012 introduces 2 new string functions, namely the CONCAT string function and the FORMAT string function. The CONCAT string function concatenates two or more string values while the FORMAT string function formats a value with the specified format and optional culture. CONCAT String Function The CONCAT string function, which is short for concatenate, returns a string that is the result of concatenating two or more string values. CONCAT ( <string_value1>, <string_value2> [, <string_valueN> ] ) The input parameters, string_value1, string_value2, string_valueN, are the string values that will be concatenated...

0 Uses of the CHARINDEX Function

The CHARINDEX string function returns the starting position of the specified expression in a character string.  It accepts three parameters with the third parameter being optional. CHARINDEX ( expression1, expression2, [ , start_location ] ) The first parameter is the expression that contains the sequence of characters to be found.  The second parameter is the expression searched for the specified sequence.  This is typically a column from a table.  The third parameter, which is optional, is the character position to start searching expression1 in expression2. SELECT CHARINDEX('the', 'the quick brown fox jumps over the...

0 How To Use Subqueries In SQL

Introduction In this article I am going to explain how to use Subqueries in SQL. A select statement known as subquery if it is codded within another SQL statement. Subquery can return a single value or a result set. This result set may contain a single column or more than one column. There are four way to create subquery  in a SELECT statement. In WHERE Clause In HAVING Clause In FROM Clause In SELECT Clause Example select * from mcninvoices where invoicetotal > ( select avg(invoicetotal) from invoices ) Output   Source : http://www.dotnetheaven.com/article/how-to-use-subqueries-in-sq...

0 How To Create UNIQUE Constraint In SQL

Introduction  If you want unique value in any specific column use UNIQUE constraints. UNIQUE constraints does not allow any duplicate value in specific column and provide unique value. Both Primary key constraint and unique constraint provide unique value. When you want unique value in any specific column but you are not interested to use primary key use Unique. Difference between Primary key Constraint and Unique Constraint You can use multiple unique constraints on a table but primary key only one. You can use null value in unique constraint but not in primary key constraint but you can use NULL value only one time in unique constraint.   A...

0 SQL Server 2012 New Date Functions

SQL Server 2008 introduced 4 new data types associated with the date and time, namely, the DATE, TIME, DATETIME2 and DATETIMEOFFSET data types.  The DATE data types stores only a date value while the TIME data type defines a time of a day without time zone awareness and is based on a 24-hour clock.  The DATETIME2 data type defines a date that is combined with a time of day that is based on a 24-hour clock and has a larger date range, a larger default fractional precision and an optional user-specified precision.  Lastly, the DATETIMEOFFSET data type defines a date that is combined with a time of day that has time zone awareness...

0 SQL Server DATEDIFF() Function

Definition and Usage The DATEDIFF() function returns the time between two dates. Syntax DATEDIFF(datepart,startdate,enddate) Where startdate and enddate are valid date expressions and datepart can be one of the following: datepartAbbreviation yearyy, yyyy quarterqq, q monthmm, m dayofyeardy, y daydd, d weekwk, ww weekdaydw, w hourhh minutemi, n secondss, s millisecondms microsecondmcs nanosecondns Example Now we want to get the number of days between two dates. We use the following SELECT statement: SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate Result: DiffDate 61 Example Now we want to get the number of days...