Tip of the Day : SQL Server Tutorial - SELECT Statement 101

SQL Server Error Messages

SQL Server 2008

Compound Operators

SQL Server 2008 introduces the Compound Operators which execute some operation, such as + (addition), - (subtraction), * (multiplication) and / (division), and set an original value to the result of the operation.

Multiple Value Inserts Within a Single INSERT Statement

One of the new programmability enhancements in the Database Engine introduced in SQL Server 2008 for Transact-SQL is the row constructor which consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma.  With the row constructor, you can now insert multiple values using a single INSERT statement.

Row Constructor (or Table-Valued Constructor) as Derived Table

In this article, we will show how to use the row constructor (also called as table-valued constructor) as a derived table which you can use in the FROM clause of a SELECT statement and as a source table in a MERGE statement, yet another new statement introduced in SQL Server 2008.

MERGE Statement

One of the common tasks in database management is the maintenance of lookup tables.  Maintenance of these look-up tables are usually done using stored procedures where the normal task is to add the new record if it does not exist or update the existing record if it already exists based on the identified primary key of the table.  SQL Server 2008 introduces a new statement called MERGE statement which combines these functionalities into just a single statement.  The MERGE statement performs an INSERT, UPDATE or DELETE operation on a target table based on the results of a join with source table.

Table-Valued Parameters

SQL Server 2008 introduces a new parameter type called the table-valued parameters.  With the table-valued parameters, which are declared by using user-defined table types, you can now send multiple rows of data to a function or stored procedure without creating a temporary table or many parameters.

Error in Importing/Exporting Geography/Geometry Columns in SQL Server 2008 Management Studio

When importing or exporting a table with a geometry or geography column, you might encounter an "unknown column type conversion" error.  This article shows how to manually resolve the issue.

SQL Server 2008 to Windows Server 2008 Connection Error 1326

This article discusses the two ways (automatic and manual) of opening the firewall port for SQL Server on Windows Server 2008 to solve the issue of "Could not open a connection to SQL Server (Microsoft SQL Server, Error: 1326)".

Convert Latitude/Longitude (Lat/Long) to Geography Point

Prior to SQL Server 2008, geography locations are usually stored as latitude and longitude coordinates in two separate columns.  With the introduction of spatial data types in SQL Server 2008, particularly the GEOGRAPHY data type, this can now be stored as points in a single column stored as a spatial data object.

MERGE Statement With Table-Valued Parameters

This article continues the discussion of the Table-Valued Parameters article and will combine this new feature with the MERGE statement, yet another new feature of SQL Server 2008.

SqlConnection Connection String

This article shows the different connection strings that you can use when connecting to SQL Server 2008 using .NET Framework Data Provider for SQL Server, the SqlConnection class.

SQL Server 2008 Date Formats

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers.

SQL Server 2005

Maximum Capacity Specifications for SQL Server

This article compares the maximum sizes and numbers of various objects defined in SQL Server 7.0, SQL Server 2000 and SQL Server 2005 databases or referenced in Transact-SQL statements.

Configuration Options for SQL Server 2000 and SQL Server 2005

This article compares, in a tabular format, all available configuraion options for both SQL Server 2000 and SQL Server 2005, showing the range of possible settings and default values.

SQL Server 2005 Data Warehousing Datasheet

Accelerate Business Insight with SQL Server 2005 for Data Warehousing.

Frequently Asked Questions - SQL Server 2005 Express Edition

Frequently Asked Questions - VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)

Tips and Tricks

Comma-Delimited Output

Return a comma-separated value without the use of cursors.

Determine Missing Identity Values

Easily identify missing identity values in your table.

Generate Random Numbers

An alternative to SQL Server's RAND mathematical function.

Generate Random Records

Generate a certain number of random records from any of your tables.

Sort IP Addresses

Sort IP addresses that are stored as VARCHAR by their numeric values.

Split Name / Split String

Different ways of splitting a full name to get the first name and last name.

Query/Read and Import an Excel File

Query/read and import an Excel file without using DTS and without loading the data to a SQL table.

Execute a Batch of SQL Scripts

Execute a batch of .sql scripts located in a specific directory without having to manually open each .sql script and executing them in Query Analyzer.

SQL Server Date Formats

Provides a list on how to convert datetime values into different date formats.

SET vs. SELECT When Assigning Variables

Shows the differences between using the SET command and the SELECT statement when assigning a value to a local variable.

String Functions

Count Character Occurrence

Returns the number of times a given input character exists in a given input string.

Count String Occurrence

Returns the number of times a given search string exists in a given input string.

Count Words

Returns the number of words in a given input string.

InitCap / String Capitalization

Emulates the behavior of Oracle's InitCap string function, which changes the first letter of each word in uppercase and all others in lowercase.

Simple String Encryption/Decryption

A string encryption and decryption function that uses a simple algorithm to encrypt and decrypt string values.

Trim Leading Zeros

Trims the leading zeros of an alphanumeric value.

Date Functions

Get Age

Computes for the age given the birth date and the reference date.

Determine Leap Year

Determines whether the input date is a leap year or not.

Get First Day of the Week

Returns the first day of the week for a given date.

Get First Day of the Month

Returns the first day of the month for a given date.

Get First Day of the Quarter

Returns the first day of the quarter for a given date.

Get Last Day of the Month

Returns the last day of the month for a given date.

Get Date Only

Returns the input date removing the time portion.

Get Number of Days in a Month

Returns the number of days in a month.

Get Number of Days in a Year

Returns the number of days in a year.

Tree Functions

Get Tree Path

Returns the path of a node from the parent to the current node in a hierarchical/tree-type table.

Get Tree Node Level

Returns the level of a given node in a hierarchical/tree-type table.

SQL Server Built-in Functions

Aggregate Functions

SQL Server aggregate functions perform a calculation on a set of values and return a single value.  With the exception of the COUNT aggregate function, all other aggregate functions ignore NULL values.  Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

Configuration Functions

SQL Server configuration functions return information about the current configuration option settings.

Date and Time Functions

SQL Server date and time functions are scalar functions that perform an operation on a date and time input value and returns either a string, numeric, or date and time value.

Mathematical Functions

SQL Server scalar math functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value.

Metadata Functions

SQL Server metadata functions return information about the database and database objects.

Security Functions

SQL Server security functions return information about users and roles.  Security functions return information that is useful in managing security.

String Functions

SQL Server string functions are scalar functions that perform an operation on a string input value and return a string or numeric value.

System Functions

SQL Server system functions perform operations on and return information about values, objects, and settings in SQL Server.