TSql all in ONE: SQL Components

SQL Components

SQL’s Basic Objects

Transact-SQL, has the same basic features as other common programming languages:

  • Literal values (also called constants)
  • Delimiters
  • Comments
  • Identifiers
  • Reserved keywords
Literal values

A literal value is an alphanumerical, hexadecimal, or numeric constant. A string constant contains one or more characters of the character set enclosed in two single straight quotation marks

–valid string constants and hexadecimal constants

‘mario’

‘9876’

‘Apostrophe is displayed like this: can”t’

0x53514C0D — hexadecimal constant

— not valid

‘AB’C‘ (odd number of single quotation marks)

New York”

Delimiters

Double quotation marks can also be used as delimiters for so-called delimited identifiers. Delimited identifiers allow the use of reserved keywords as identifiers and also to allow spaces in the names of database objects

— delimited identifiers

SET
QUOTED_IDENTIFIER
ON

CREATE
TABLE “SELECT”
(“TABLE” int)
— SUCCESS

SET
QUOTED_IDENTIFIER
ON

SELECT “sometext” AS Value — FAIL because “sometext” is not a literal

Comment

There are two different ways to specify a comment in a Transact-SQL statement. Using the pair of characters /* and */ marks the enclosed text as a comment. The characters — (two hyphens) indicate that the remainder of the current line is a comment.

— comment on 1 line

/* here

is still a comment*/

Identifiers

Identifiers are used to identify database objects such as databases, tables, and indices.

How:

  • Character strings that may include up to 128 characters and can contain letters, numerals, or the following characters: _, @, #, and $.
  • Each name must begin with a letter or one of the following characters: _, @, or #.

Data Types

All the data values of a column must be of the same data type, apart from SQL_VAARIANT data type; they are

  • Numeric data types
  • Character data types
  • Temporal (date and/or time) data types
  • Miscellaneous data types
  • DECIMAL with VARDECIMAL storage type
Numeric Data Types

http://msdn.microsoft.com/en-us/library/ms187752.aspx

Character Data Types

2 general forms of character data types

  • single-byte characters
  • Strings of Unicode characters.
  • have fixed length
  • variable length
Temporal Data Types

Transact-SQL supports the following temporal data types:

  • DATETIME
  • SMALLDATETIME
  • DATE
  • TIME
  • DATETIME2
  • DATETIMEOFFSET

The DATE data type is stored in 3 bytes and has the range 01/01/0001 to 12/31/9999. The TIME data type is stored in 3–5 bytes. The DATETIME2 data type is also a new data type that stores high-precision

— date and time

— The following date descriptions can be used:

’28/5/1959′
— (with SET DATEFORMAT dmy)

‘May 28, 1959’

‘1959 MAY 28’

SET
DATEFORMAT DMY

Miscellaneous Data Types

Transact-SQL supports several data types that do not belong to any of the previous data type groups :

  • Binary data types
  • BIT
  • Large object data types
  • CURSOR
  • UNIQUEIDENTIFIER
  • SQL_VARIANT
  • TABLE
  • XML
  • Spatial
  • HIERARCHYID
  • User-defined data types
Binary data types

BINARY and VARBINARY are the two binary data types to represent data objects in the internal format of the system

The values of the BIT data type are stored in a single bit

Large Object Data Types

Large objects (LOBs) are data objects with the maximum length of 2GB

TO define LOBs:

  • Use the data types VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) // preferred
  • Use the so-called text/image data type

Use the FILESTREAM attribute to a VARBINARY(MAX) column to store large binary data directly in an NTFS file system.

http://www.mssqltips.com/tip.asp?tip=1489

UNIQUEIDENTIFIER Data Type

As its name implies, a value of the UNIQUEIDENTIFIER data type is a unique identification number stored as a 16-byte binary string; it used to guarantee uniqueness worldwide (through GUID)

SQL_VARIANT Data Type

The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values.

HIERARCHYID Data Type

The HIERARCHYID data type is used to store an entire hierarchy.

Use GetAncestor(), GetDescendant(), Read(), and Write().

Transact-SQL Functions

Transact-SQL functions can be either aggregate functions or scalar functions

Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value.

http://databases.about.com/od/sql/l/aaaggregate1.htm

Scalar functions that are used in the construction of scalar expressions. (It operates on a single value or list of values)

They can be categorized as follows:

  • Numeric functions
  • Date functions

— Date Functions

SELECT
GETDATE()

— > 2011-06-06 18:07:52.700

SELECT
DATEPART(month,
‘2011-06-06’)

— > 6, part item of a date date as an integer

SELECT
DATENAME(weekday,
‘2011-06-06’)

— > Monday, part item of the date date as a character string

  • String functions

— String Functions

SELECT
ASCII(‘A’)

— > A

SELECT
LEFT(‘Ireland’, 3)

— > Ire, Returns the first length characters from the string z.

  • System functions

— System functions

SELECT
CAST(‘3000000000’
AS
BIGINT)

— > 3000000000, Converts an expression a into the specified data type type (if possible)

SELECT
COALESCE(NULL, 1,2)

— > 1, the first expression that is not NULL.

  • Metadata functions

— metadata functions

SELECT
DB_ID(‘AdventureWorks’)

SELECT
OBJECT_NAME(3243433)

Functions return information concerning the specified database and database objects.

http://msdn.microsoft.com/en-us/library/ms187812.aspx

Global Variables

Global variables are special system variables that can be used as if they were scalar constants. They have to be preceded by the prefix @@

— global vars

SELECT
@@IDENTITY

SELECT
@@ROWCOUNT

NULL Values

A NULL value is a special value that may be assigned to a column. This value is normally used when information in a column is unknown or n/a

Any NULL value in the argument of aggregate functions AVG, SUM, MAX, MIN, and COUNT is eliminated before the respective function is calculated

A column of a table allows NULL values if its definition explicitly contains NULL.

— NULL

SET
ANSI_NULL_DFLT_ON
ON

SET
CONCAT_NULL_YIELDS_NULL
OFF

SELECT
‘Mario’
+
NULL

— > NULL

References

Buy it 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s