Friday, December 31, 2010

When is an explicit object reference casting is required?


General speaking, if the left hand side of an assignment is a more specific type (subtype) and the right hand side is a more general type (supertype), then explicit casting is required. On the other hand, when you assign a subclass reference to a variable of superclass, the casting is performed automatically, or not required.

Another case is calling methods from an object reference, you can not access methods that are only declared and implemented in its subclass. You have to explicit cast the object reference into the actual object type.

For example,

class Super {
}
class Sub extends Super {
public void writeLog() {
System.out.println("log");
}
class Program {
public static void main(String[] args) {
Sub b = new Sub();
// sub type reference can be assigned to super type without casting
Super a = b;
// super type reference has to be casted before assigned to sub type
  b = (Sub)a;
((Sub)a).writeLog(); // method only defined in sub type
}
}

Java compiler is not responsible for checking if the casting is correct or not, just like some of the bindings only occur at run time ( XyzWs Java FAQ: Run time binding or compile time binding?). Java virtual machine does the checking at run time to find out whether the actual reference object is a legitimate object of the new type. If not, there will be a runtime exception: ClassCastException.


Wednesday, December 29, 2010

Fwd: SQL Server Data Types




1.  What's the difference between CHAR and VARCHAR data types and when do I use them?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters.  The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length.  If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size.  On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

 2.  What's the difference between NCHAR and NVARCHAR data types and when do I use them?

NCHAR and NVARCHAR data types are both Unicode character data types with a maximum length of 4,000 characters.  The main difference between these 2 data types is that an NCHAR data type is fixed-length while an NVARCHAR is variable-length.  If the number of characters entered in an NCHAR data type column is less than the specified column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for NCHAR is two times n bytes while for NVARCHAR is two times the number of characters entered (in bytes).

You should use NCHAR data type when the data values in a column are expected to be consistently close to the same size.  On the other hand, you should use NVARCHAR when the data values in a column are expected to vary considerably in size.

 3.  What's the difference between CHAR and NCHAR data types and when do I use them?

CHAR and NCHAR data types are both character data types that are fixed-length.  Below is the summary of the differences between these 2 data types:

  CHAR(n) NCHAR(n)
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size n bytes 2 times n bytes

You would use NCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.

 4.  What's the difference between VARCHAR and NVARCHAR data types and when do I use them?

VARCHAR and NVARCHAR data types are both character data types that are variable-length.  Below is the summary of the differences between these 2 data types:

  VARCHAR(n) NVARCHAR(n)
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size Actual Length (in bytes) 2 times Actual Length (in bytes)

You would use NVARCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.

 5. What's the difference between TINYINT, SMALLINT, INT and BIGINT data types and when do I use them?

TINYINT, SMALLINT, INT and BIGINT are all the same in the sense that they are all exact number data types that use integer data.  The difference between these data types are in the minimum and maximum values that each can contain as well as the storage size required by each data type, as shown in the following table:

Data Type Minimum Value Maximum Value Storage Size
tinyint 0 255 1 byte
smallint -2^15 (-32,768) 2^15 - 1 (32,767) 2 bytes
int -2^31 (-2,147,483,648) 2^31 - 1 (2,147,483,647) 4 bytes
bigint -2^63 (-9,223,372,036,854,775,808) 2^63 - 1 (9,223,372,036,854,775,807) 8 bytes

Choosing which of these data types to use depends on the value you want to store for the column or variable.  The rule of thumb is to always use the data type that will require the least storage size.  Don't always use INT as your data type for whole numbers if you don't need to.  If you simply need to store a value between 0 and 255 then you should define your column as TINYINT


6.  What's the difference between NUMERIC and DECIMAL data types and when do I use them?

There is no difference between NUMERIC and DECIMAL data types.  They are synonymous to each other and either one can be used.  DECIMAL/NUMERIC data types are numeric data types with fixed precision and scale.

DECIMAL (p [, s ])
NUMERIC (p [, s ])

In declaring a DECIMAL or NUMERIC data type, p, which is the precision, specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.  The precision must be a value from 1 through the maximum precision of 38.  The s is the scale and it specifies the maximum number of decimal digits that can be stored to the right of the decimal point.  Scale, which defaults to 0 if not specified, must be a value from 0 to the precision value.

The following table specifies the storage size required based on the precision specified for the NUMERIC or DECIMAL data type:

Precision Storage Size
1 - 9 5 bytes
10- 19 9 bytes
20-28 13 bytes
29-38 17 bytes
 7.  What's the difference between FLOAT and REAL data types and when do I use them?

FLOAT and REAL data types are both approximate number data types for use with floating point numeric data.  Floating point data is approximate; not all values in the data type range can be precisely represented.  The differences between these 2 data types are in the minimum and maximum values each can hold as well as the storage size required, as specified in the following table:

Data Type n Minimum Value Maximum Value Precision Storage Size
float [(n)] 1-24 -1.79E + 308 1.79E + 308 7 digits 4 bytes
  25-53 -1.79E + 308 1.79E + 308 15 digits 8 bytes
real   -3.40E + 38 3.40E + 38 7 digits 4 bytes

For FLOAT data type, the n is the number of bits used to store the mantissa in scientific notation and thus dictates the precision and storage size and it must be a value from 1 through 53.  If not specified, this defaults to 53.  In SQL Server, the synonym for REAL data type is FLOAT(24).  If your data requires only a maximum of 7 digits precision, you can either use the REAL data type or FLOAT data type with 24 as the parameter (FLOAT(24)).

 8. What's the difference between SMALLDATETIME and DATETIME data types and when do I use them?

A datetime data type is date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).  Values are rounded to increments of .000, .003, or .007 seconds.

On the other hand, a smalldatetime data type is a date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute.  smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers.  The first 4 bytes store the number of days before or after the base date, January 1, 1900.  The base date is the system reference date.  Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime.  SQL Server stores smalldatetime values as two 2-byte integers.  The first 2 bytes store the number of days after January 1, 1900.  The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

Data Type Minimum Value Maximum Value Time Accuracy Storage Size
smalldatetime January 1, 1900 June 6, 2079 up to a minute 4 bytes
datetime January 1, 1753 December 31, 9999 one three-hundredth of a second 8 bytes

smalldatetime is usually used when you don't need to store the time of the day such as in cases of effectivity dates and expiration dates.  datetime  is used if the time of the day is needed and up to the second accuracy is required.

 9.  What's the difference between SMALLMONEY and MONEY data types and when do I use them?

MONEY and SMALLMONEY are both monetary data types for representing monetary or currency values.  The differences between these 2 data types are in the minimum and maximum values each can hold as well as in the storage size required by each data type, as shown in the following table:

Data Type Minimum Value Maximum Value Storage Size
smallmoney -214,748.3648 214,748.3647 4 bytes
money -2^63 (-922,337,203,685,477.5808) 2^63 - 1 (+922,337,203,685,477.5807) 8 bytes

Both SMALLMONEY and MONEY data types has an accuracy to a ten-thousandths of a monetary unit.  The rule of thumb is to always use the data type that will require the least storage size.  If the monetary value that you will store is less than 214,748.3647 then you should use SMALLMONEY; otherwise use the MONEY data type.

 10.  How do I store a boolean value in SQL Server?

In SQL Server, there's no boolean data type.  The nearest data type that can be used in place of boolean data is the BIT data type, which is an integer data type that can accept a value of 1, 0 or NULL value only.





SQL Server Date Formats 2


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.

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.



Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD - SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD - SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM

Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
1999-01-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12
Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 2006 1
Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1
Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1
DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] 11 September 1
Month DD 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1
DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1
DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
12/92
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
92/12
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] 122506
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] 12252006
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] 240702
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] 24072002
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] Sep-02 1
Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] Sep-2002 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] 25-Dec-05 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] 25-Dec-2005 1

1 To make the month name in upper case, simply use the UPPER string function.



SQL Server Maximum Capacity Specifications



Maximum Sizes/Numbers
Object SQL Server 7.0 SQL Server 2000 SQL Server 2005 (32-bit)
Batch size 65,536 * Network Packet Size1 65,536 * Network Packet Size1 65,536 * Network Packet Size1
Bytes per short string column 8,000 8,000 8,000
Bytes per text, ntext, or image column 2 GB-2 2 GB-2 2 GB-2
Bytes per GROUP BY, ORDER BY 8,060 8,060 8,060
Bytes per index 900 9002 9002
Bytes per foreign key 900 900 900
Bytes per primary key 900 900 900
Bytes per row 8,060 8,060 8,0608
Bytes in source text of a stored procedure Lesser of batch size or 250 MB Lesser of batch size or 250 MB Lesser of batch size or 250 MB
Bytes per VARCHAR(MAX), VARBINARY(MAX), XML, TEXT, or IMAGEcolumn N/A N/A 2^31-1
Characters per NTEXT or NVARCHAR(MAX) column. N/A N/A 2^30-1
Clustered indexes per table 1 1 1
Columns in GROUP BY, ORDER BY Limited only by number of bytes per GROUP BY, ORDER BY Limited only by number of bytes per GROUP BY, ORDER BY Limited only by number of bytes per GROUP BY, ORDER BY
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement 10 10 10
Columns per index 16 16 167
Columns per foreign key 16 16 16
Columns per primary key 16 16 16
Columns per base table 1,024 1,024 1,024
Columns per SELECT statement 4,096 4,096 4,096
Columns per INSERT statement 1,024 1,024 1,024
Connections per client Maximum value of configured connections Maximum value of configured connections Maximum value of configured connections
Database size 1,048,516 TB 1,048,516 TB 1,048,516 TB
Databases per instance of SQL Server 32,767 32,767 32,767
Filegroups per database 256 256 32,767
Files per database 32,767 32,767 32,767
File size (data) 32 TB 32 TB 16 TB
File size (log) 4 TB 32 TB 2 terabytes
Foreign key table references per table4 253 253 253
Identifier length (in characters) 128 128 128
Instances per computer N/A 16 16
Length of a string containing SQL Statements (batch size) 65,536 * Network packet size 1 65,536 * Network packet size 1 65,536 * Network packet size 1
Locks per connection Maximum locks per server Maximum locks per server Maximum locks per server
Locks per instance of SQL Server 2,147,483,647 (static) 40% of SQL Server memory (dynamic) 2,147,483,647 (static) 40% of SQL Server memory (dynamic) Up to 2,147,483,6475
Nested stored procedure levels6 32 32 32
Nested subqueries 32 32 32
Nested trigger levels 32 32 32
Nonclustered indexes per table 249 249 249
Objects concurrently open in an instance of SQL Server3 2,147,483,647 (or available memory) 2,147,483,647 (or available memory)  
Objects in a database 2,147,483,6473 2,147,483,6473 2,147,483,6473
Parameters per stored procedure 1,024 1,024 2,100
Parameters per user-defined function 1,024 1,024 2,100
Partitions per partitioned table or index N/A N/A 1,000
REFERENCES per table 253 253 253
Rows per table Limited by available storage Limited by available storage Limited by available storage
Statistics on non-indexed columns     2,000
Tables per database Limited by number of objects in a database3 Limited by number of objects in a database3 Limited by number of objects in a database3
Tables per SELECT statement 256 256 256
Triggers per table Limited by number of objects in a database3 Limited by number of objects in a database3 Limited by number of objects in a database3
UNIQUE indexes or constraints per table 249 nonclustered and 1 clustered 249 nonclustered and 1 clustered 249 nonclustered and 1 clustered
XML indexes N/A N/A 249

1 Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational database engine.  The default packet size is 4 kilobytes (KB), and is controlled by the network packet size configuration option.

2 The maximum number of bytes in any index key cannot exceed 900 in SQL Server 2000 and SQL Server 2005.  You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns.  In SQL Server 2005, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes.

3 Database objects include objects such as tables, views, stored procedures, extended stored procedures, user-defined functions, triggers, rules, defaults, and constraints.  The sum of the number of all objects in a database cannot exceed 2,147,483,647.

4 Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253.  Depending on the hardware configuration hosting SQL Server, specifying additional foreign key constraints may be expensive for the query optimizer to process.

5 This value is for static lock allocation.  Dynamic locks are limited only by memory.

6 If a stored procedure accesses more than 8 databases, or more than 2 databases in interleaving, you will receive an error.

7 If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index.  In SQL Server 2005, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns.

8 SQL Server 2005 supports row-overflow storage which enables variable length columns to be pushed off-row.  Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server.


SQL Server 2005 - VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)




Microsoft SQL Server 2005 introduces a new data type, the XML data type.  The XML data type lets you store XML documents and fragments in a SQL Server database.  An XML fragment is an XML instance that is missing a single top-level element.  You can create columns and variables of XML type and store XML instanced in them.

In addition to the new XML data type, Microsoft SQL Server 2005 has enhanced three of the existing SQL Server data types, namely the VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.  The VARCHAR(MAX) data type indicates that the maximum storage size for the VARCHAR data type is 2^31-1 bytes.  The NVARCHAR data types indicates that the maximum storage size fr the NVARCHAR data type is 2^31-1 bytes.  Lastly, the VARBINARY(MAX) data type indicates that the maximum storage size for the VARBINARY data type is 2^31-1 bytes.


The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).  The storage size is the actual length of data entered + 2 bytes.  The data entered can be 0 characters in length.  Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

The maximum storage size for NVARCHAR(MAX) is also 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).  The storage size, in bytes, is two times the number of characters entered + 2 bytes.  The data entered can be 0 characters in length.  Since each Unicode character in an NVARCHAR data type uses two bytes, the maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.

The maximum storage size for VARBINARY(MAX) is the same as the maximum storage size for VARCHAR(MAX) and NVARCHAR(MAX), which is 2^31-1 (2,147,483,647 bytes or 2GB - 1 bytes).  The storage size is the actual length of the data entered + 2 bytes.  The data that is entered can be 0 bytes in length.


When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000.  If the length is greater than 8000, you have to use the  MAX specifier as the length.  If a length greater than 8000 is specified, the following error will be encountered (assuming that the length specified is 10000):

Server: Msg 131, Level 15, State 3, Line 1
The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

Yes, VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) will replace the TEXT, NTEXT and IMAGE data types, respectively.  TEXT, NTEXT and IMAGE data types will be removed in the future version of Microsoft SQL Server.  Avoid using these data types when using SQL Server 2005 and use VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) instead.




Yes, you can declare local variables of VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.  This is unlike the data types that these enhanced data types are replacing because declaring local variables of TEXT, NTEXT and IMAGE data types is not allowed.  You will encounter the following error message when a local variable of TEXT, NTEXT or IMAGE data type is defined:

Server: Msg 2739, Level 16, State 1, Line 7
The text, ntext, and image data types are invalid for local variables.

No, there is no CHAR(MAX), NCHAR(MAX) or BINARY(MAX).  The main reason why the MAX specifier is not included for these data types is because these data types are fixed-length data.  If the MAX specifier was included for these data types, it will be a big waste of disk space as each column will consume 2GB-1 bytes even if only a short string value is assigned to the column.


To create a column of VARCHAR data type with a maximum of 10,000 characters, you declare the column as VARCHAR(MAX) data type and simply add a CHECK constraint to the column by checking the length of the column and making sure it is less than or equal to 10,000 characters.  To illustrate, here's how it will look like:

CREATE TABLE [dbo].[VarChar10000] ( [VarChar10000] VARCHAR(MAX) )
GO

ALTER TABLE [dbo].[VarChar10000]
ADD CONSTRAINT [MaxLength10000]
CHECK (DATALENGTH([VarChar10000]) <= 10000)
GO

With the column defined as VARCHAR(MAX), it can accept a string longer than 8,000 characters.  But with the CHECK constraint, it will not allow a string with a length of more than 10,000 characters.  To verify that the column will not allow a string of more than 10,000 characters, try executing the following INSERT command, which will insert a string value of 11,000 characters:

INSERT INTO [dbo].[VarChar10000] ( [VarChar10000] )
VALUES (REPLICATE(CAST('A' AS VARCHAR(MAX)), 11000))

The following error will be shown when the INSERT statement above is executed:

Server: Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the CHECK constraint "MaxLength10000".
The conflict occurred in database, table "dbo.VarChar10000", column 'VarChar10000'.
The statement has been terminated.


The SELECT INTO operation



-- SQL Server SELECT INTO - sql server select into new table
-- select all persons into new table with last name starting with 'A'
SELECT ID=BusinessEntityID, FullName=FirstName + ' '+ LastName
INTO ListA
FROM AdventureWorks2008.Person.Person WHERE LEFT(LastName,1) = 'A'
------------

-- SQL select into table create - sql select into syntax - sql create table select
-- Create table from select sql server - sql select into statement
-- Select into sql server - select into temp table - create table as select
-- Insert data from one table to another table in sql - sql select into table

USE AdventureWorks2008;
SELECT * INTO dbo.SalesOrderHeader FROM Sales.SalesOrderHeader WHERE Year(OrderDate)=2004
SELECT TOP (0) SalesOrderID, OrderDate INTO SOH FROM Sales.SalesOrderHeader
SELECT * INTO SOH FROM Sales.SalesOrderHeader
SELECT * INTO SOH1 FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate)=2004
SELECT * INTO SOH2 FROM Sales.SalesOrderHeader ORDER BY SalesOrderID desc
SELECT SalesOrderID, OrderDate, SubTotal INTO SOH3 FROM Sales.SalesOrderHeader
-- SQL select into group by - transact sql group by - sql server select into table
SELECT [Year]=YEAR(OrderDate), Orders=COUNT(*)
       INTO SOH4 FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate)
SELECT *, [CreateDate]=getdate() INTO SOH5 FROM Sales.SalesOrderHeader

-- SQL Server select into temporary table - sql server select into temp table
SELECT TotalOrders = COUNT(*) INTO #TotalOrders FROM Sales.SalesOrderHeader

SELECT ProductID = CAST(ProductID as INT), ListPrice, Color
       INTO #ProdList FROM Production.Product ORDER BY ListPrice desc

SELECT Name=CONVERT(VARCHAR(45),NULL), Age=CONVERT(INT,NULL) INTO tempdb.dbo.Person
SELECT TOP (100) * INTO POH  FROM Purchasing.PurchaseOrderHeader ORDER BY NEWID()      
SELECT TOP (0) * INTO POH1 FROM Purchasing.PurchaseOrderHeader

SELECT * INTO #spwho FROM OPENQUERY (DELLSTAR,'exec sp_who') -- select into from sproc
-- SQL select into from joined tables - sql join - sql server select into new table

SELECT soh.SalesOrderID, OrderDate, OrderQty, ProductID INTO SalesOrder

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID=sod.SalesOrderID


/* SELECT INTO creates a brand new permanent or temporary table and populates
it with the result set, if any, of the SELECT query. The SELECT INTO
statement can be used to combine data from one or more tables or views into
one new table in the same or different database. It can also be used just to
create an empty table. The IDENTITY property will carry over to the new table,
to prevent it see example further down.*/


-- SQL select into copy table - sql select into from table
-- SQL select into table create - quick overview of select into create table
USE [AdventureWorks2008]; - Create table as select table
SELECT * INTO dbo.Location FROM Production.Location
/*
(1 row(s) affected) - 1 table is created
(14 row(s) affected) - table is populated with 14 rows
*/
 
-- Script out new table using Management Studio Object Explorer
-- IDENTITY property on LocationID is inherited - No indexes or constraints created

/* CREATE TABLE [dbo].[Location](
      [LocationID] [smallint] IDENTITY(1,1) NOT NULL,
      [Name] [dbo].[Name] NOT NULL,
      [CostRate] [smallmoney] NOT NULL,
      [Availability] [decimal](8, 2) NOT NULL,
      [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY] */
-- Cleanup - sql delete table
DROP TABLE dbo.Location
------------

-- SQL Server select into variable
DECLARE @Price money, @Product varchar(32)
SELECT @Product=Name, @Price = ListPrice
FROM AdventureWorks2008.Production.Product
WHERE ProductID = 800
SELECT ProductName=@Product, ListPrice=@Price
/*    ProductName             ListPrice
      Road-550-W Yellow, 44   1120.49    */
------------

-- 1. Create an empty table with the same layout as the Sales.Currency table.

USE AdventureWorks;
-- Create an empty curreny table - SQL select into create table
-- SQL create table with select into - Create table as select sql server
SELECT * INTO Sales.NewCurrency
FROM Sales.Currency
WHERE 1 > 2 -- Condition never fulfilled
--(0 row(s) affected)
/* Equivalent code
 
CREATE TABLE [Sales].[NewCurrency](
      [CurrencyCode] [nchar](3) NOT NULL,
      [Name] [dbo].[Name] NOT NULL,
      [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
*/
 
SELECT * FROM Sales.NewCurrency
GO
-- (0 row(s) affected)
 
-- Add default for ModifiedDate
ALTER TABLE Sales.NewCurrency ADD CONSTRAINT
      DF_NewCurrency_ModifiedDate DEFAULT getdate() FOR ModifiedDate
GO
 
-- Transact sql populate table
INSERT Sales.NewCurrency (CurrencyCode, Name) VALUES ('USD', 'US Dollar')
INSERT Sales.NewCurrency (CurrencyCode, Name) VALUES ('EUR', 'EURO')
GO
 
-- SQL query table
SELECT * FROM Sales.NewCurrency
GO
/*
CurrencyCode      Name        ModifiedDate
USD               US Dollar   2008-12-18 23:50:56.290
EUR               EURO        2008-12-18 23:52:00.193
*/
 
-- MSSQL delete table
DROP TABLE Sales.NewCurrency
------------
 
-- 2. SELECT INTO use in stored procedures.
 
-- SQL select into stored procedure - SELECT INTO SQL Server
-- SQL create table from another table - SQL server create table as select
USE AdventureWorks2008;
GO
CREATE PROCEDURE sprocDailySales @Date date
AS
BEGIN
-- SELECT INTO temporary table - create table select mssql - select into temp table sql
      SELECT SalesOrderID
      INTO #SOH
      FROM Sales.SalesOrderHeader
      WHERE OrderDate = @Date
 
      SELECT      [Date]=@Date,
                  TotalQty = SUM(OrderQty),
                  TotalDollar = SUM(LineTotal)
      FROM #SOH
      INNER JOIN Sales.SalesOrderDetail sod
            ON #SOH.SalesOrderID = sod.SalesOrderID
END
GO
 
EXEC sprocDailySales '2004-02-02'
GO
/* Results
 
Date        TotalQty    TotalDollar
2004-02-02  148         49410.450000

*/

------------

-- 3. SELECT INTO new table from stored procedure execution.
 

-- SQL select into from stored procedure - SQL OPENROWSET statement
-- Create table select sql server - sqlserver create table as select

SELECT *
INTO #BOM
FROM OPENROWSET ( 'SQLOLEDB','SERVER=.;Trusted_Connection=yes',
'SET FMTONLY OFF EXEC AdventureWorks.dbo.uspGetBillOfMaterials 800, ''2004-02-01''');
GO
 
-- SELECT from temporary table
SELECT * FROM #BOM ORDER BY BOMLevel, ComponentDesc
GO
/* Partial results
 
ProductAssemblyID ComponentID ComponentDesc TotalQuantity StandardCost
800               952         Chain         1.00          8.9866
800               948         Front Brakes  1.00          47.286
800               945         Front Derailleur  1.00      40.6216
*/
DROP TABLE #BOM
------------
 
-- 4. Create empty table based on the Production.Product table.
 
-- SQL create table as select
-- Creating an empty table without inheriting the identity property
-- If no CONVERT, the identity property will be set for ProductID
SELECT TOP (0) ProductID=CONVERT(int, ProductID),
ProductName=Name, ListPrice, Color
INTO dbo.testProduct
FROM Production.Product
GO
 
/* Equivalent code:
 
CREATE TABLE [dbo].[testProduct](
      [ProductID] [int] NULL,
      [ProductName] [dbo].[Name] NOT NULL,
      [ListPrice] [money] NOT NULL,
      [Color] [nvarchar](15) NULL
) ON [PRIMARY]
*/
 
-- We can insert into ProductID due to identity off
INSERT testProduct VALUES (1001, 'Rocky Mountain Bike', 900.0, 'Blue')
GO
 
-- Delete table
DROP TABLE testProduct
------------
 
-- 5. Copy table, structure & content, with select into for test and development.
 
USE tempdb;
-- SQL select * into table
-- SQL select into tempdb
SELECT * INTO EPH FROM AdventureWorks.HumanResources.EmployeePayHistory
GO
-- SQL select from new table
SELECT EmployeeID, RateChangeDate
FROM EPH
WHERE RateChangeDate BETWEEN '19971101' AND '19980105'
GO
/* Results
 
EmployeeID  RateChangeDate
3     1997-12-12 00:00:00.000
4     1998-01-05 00:00:00.000
*/
-- SQL update select into table
UPDATE EPH SET RateChangeDate = '1998-01-05 00:00:01.000'
WHERE EmployeeID = 4
  AND RateChangeDate = '19980105'
GO
 
-- SQL drop table
DROP TABLE tempdb.dbo.EPH

------------

 

-- 6. Create populated copy of Sales.SalesOrderHeader and empty it with TRUNCATE.

-- The following SELECT INTO query will create a populated table copy
USE tempdb;
-- SQL SELECT INTO table
-- SQL Server CREATE TABLE as SELECT 
SELECT * INTO SOH
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY SalesOrderID
GO
-- (31465 row(s) affected)
 
-- Empty table by truncating
TRUNCATE TABLE SOH
GO
-- Command(s) completed successfully.
 
SELECT COUNT(*) FROM SOH
-- Result: 0
------------

 

-- 7. Create a temporary table with SELECT INTO based on Product table.

-- SQL create empty temporary table - SQL Server SELECT INTO
-- SQL select into temp table - sql server select into temp table
-- SQL sequential numbering - sql server identity

-- SQL sequential update - mapping non-sequential numbers to sequential numbers
SELECT TOP (0) ID=identity(int,1,1),
               ProductID=CAST(ProductID AS INT),
               ListPrice, Color
INTO #Product
FROM Production.Product
GO
 
-- SQL insert select - populate table
-- SQL Server select into existing table
INSERT INTO #Product (ProductID, ListPrice, Color)
SELECT TOP (5) ProductID, ListPrice, Color
FROM Production.Product
WHERE ListPrice > 0.0 and Color is not NULL
GO
 
-- SQL select from temp table
SELECT * FROM #Product
GO
/* Results
 
ID    ProductID   ListPrice   Color
1     680         1431.50     Black
2     706         1431.50     Red
3     707         34.99       Red
4     708         34.99       Black
5     709         9.50        White

*/
------------
 

-- 8. Create populated table with SELECT INTO from Person.Contact.

-- select into sql server
-- create new table and populate it in one query
-- SQL use
USE tempdb;
-- SQL top function
-- SQL newid - random sorting
-- SQL 3-parts reference
-- SQL select random 5 rows
SELECT      TOP(5)
            ContactID,
            FullName = FirstName+' '+LastName,
            Email=EmailAddress
INTO Person
FROM AdventureWorks.Person.Contact
WHERE EmailPromotion = 2
ORDER BY NEWID()
 
-- SQL select all columns
SELECT *
FROM Person
/* Results
 
ContactID   FullName          Email
4303        Luke Flores       luke1@adventure-works.com
8654        Danielle Stewart  danielle26@adventure-works.com
13340       Lori Ortega       lori22@adventure-works.com
8799        Gilbert Nath      gilbert38@adventure-works.com
5487        Austin Shan       austin28@adventure-works.com
*/
-- SQL drop table
DROP TABLE Person
GO

------------

-- 9. Copy table into a different database with SELECT INTO .

-- SQL copy table with select into
-- Create table as select sql server
-- Copy table from one database to another with select into
-- SQL add column to new table
USE tempdb;
 
SELECT *, CopyDate = GETDATE()
INTO Department
FROM AdventureWorks.HumanResources.Department
ORDER BY DepartmentID
GO
 
SELECT DepartmentID, Department=Name, CopyDate
FROM Department ORDER BY DepartmentID
GO
/* Partial results
 
DepartmentID            Department        CopyDate
1                       Engineering       2012-01-08 15:09:59.997
2                       Tool Design       2012-01-08 15:09:59.997
3                       Sales             2012-01-08 15:09:59.997
*/
 
-- SQL drop table
DROP TABLE Department
GO
------------
 
-- 10. SELECT INTO create table; remap dates with UPDATE statement.
 

USE tempdb;
-- SQL Server select into  
SELECT TOP 100 *
INTO PurchaseOrderHeader
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY NEWID()
GO
 
-- SQL server create table as select
-- Remap order dates to recent dates
-- SQL multiple value assignment
DECLARE @OrderDate datetime
SET @OrderDate = getdate()
UPDATE PurchaseOrderHeader
SET @OrderDate = OrderDate = dateadd(day,-1,@OrderDate)
GO
 
SELECT TOP (5) PurchaseOrderID, VendorID,OrderDate
FROM PurchaseOrderHeader
GO
/* Results
 
PurchaseOrderID   VendorID    OrderDate
2848              12          2012-01-08 10:07:27.663
91                80          2012-01-07 10:07:27.663
2788              37          2012-01-06 10:07:27.663
1538              69          2012-01-05 10:07:27.663
3687              72          2012-01-04 10:07:27.663
*/
 
-- SQL drop table
DROP TABLE tempdb.dbo.PurchaseOrderHeader
GO

----------

-- 11. SELECT INTO existing table workaround.

-- SQL select into existing table
USE tempdb;
-- First we create a table with select into
-- SQL create table as select
SELECT PurchaseOrderID = CAST (PurchaseOrderID AS INT),
 RevisionNumber, [Status], EmployeeID, VendorID,
 ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight,
 TotalDue, ModifiedDate
INTO POH
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
GO
 
-- Instead of select into, we do truncate first, then insert select
TRUNCATE TABLE POH;
-- SQL insert into select
-- SQL select into existing table
INSERT INTO POH
SELECT * FROM AdventureWorks.Purchasing.PurchaseOrderHeader
GO
 
-- SQL drop table
DROP TABLE tempdb.dbo.POH
GO

----------

-- 12. SELECT INTO table create from a complex query.

-- SQL select into from multiple tables
-- Create table with select into from complex query
USE AdventureWorks;
SELECT SalesStaff =C.LastName+', '+ C.FirstName
, ZipCode = A.PostalCode
, TotalSales='$'+CONVERT(varchar, convert(money,SUM(SOD.LineTotal)),1)
, PercentOfTotal = CAST(100.0* SUM(SOD.LineTotal) / SUM(SUM(SOD.LineTotal))               
OVER (PARTITION BY 1, 2 ) AS NUMERIC (4,1))
INTO tempdb.dbo.SalesStaffReport
FROM Person.Contact C
INNER JOIN Person.[Address] A
      ON A.AddressID = C.ContactID
INNER JOIN Sales.SalesOrderHeader SOH
      ON SOH.SalesPersonID = C.ContactID
INNER JOIN Sales.SalesOrderDetail SOD
      ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE TerritoryID IS NOT NULL
GROUP BY C.FirstName, C.LastName, A.PostalCode, C.ContactID
ORDER BY SalesStaff, ZipCode
GO
-- SQL select random
SELECT TOP (5) *
FROM tempdb.dbo.SalesStaffReport
ORDER BY NEWID()
GO
/* Results:
 
SalesStaff        ZipCode     TotalSales        PercentOfTotal
Esteves, Janeth   98055       $1,827,066.71     2.3
Elliott, Shannon  98027       $3,325,092.59     4.1
Elson, Jauna      98055       $6,427,005.55     8.0
Dusza, Maciej     98027       $9,293,903.00     11.5
Dyck, Shelley     98027       $10,367,007.43    12.9
*/
-- SQL drop table
DROP TABLE tempdb.dbo.SalesStaffReport
GO
---------- 
 
-- 13. SELECT INTO new table from system procedure execution.

 

-- SQL select into temporary table
-- SQL openrowset system procedure execution
SELECT * INTO #spwho
FROM OPENROWSET ( 'SQLOLEDB','SERVER=.;Trusted_Connection=yes',
'SET FMTONLY OFF EXEC sp_who');
GO
SELECT * FROM #spwho
GO
/* Partial results
 
spid  ecid  status            loginame    hostname    blk
1     0     background        sa                      0   
2     0     background        sa                      0   
3     0     suspended         sa                      0   
*/

 

-- SQL select into create temporary table
-- SQL openquery system procedure execution
SELECT DB=DB_NAME(dbid),* INTO #splock
FROM OPENQUERY(DELLSTAR, 'EXEC sp_lock')
SELECT TOP(2) * FROM  #splock
GO
/* Results
 
DB          spid  dbid  ObjId IndId Type  Resource    Mode  Status
msdb        51    4     0     0     DB                S     GRANT
Accounting  52    23    0     0     DB                S     GRANT

*/

----------
 

-- 14. SELECT INTO new table from dynamic stored procedure execution.

 

----------
-- Dynamic stored procedure to temporary table
----------
USE tempdb;
GO
-- SQL create dynamic stored procedure
CREATE PROCEDURE DynamicSprocToTempTable
AS
  BEGIN
    DECLARE  @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT top (5) * from                 AdventureWorks.Purchasing.PurchaseOrderHeader
                ORDER BY NEWID()'
    -- Dynamic SQL
    EXEC Sp_executesql    @SQL
  END
GO
 
-- Test stored procedure
EXEC tempdb.dbo.DynamicSprocToTempTable
GO
-- Select into temporary table from OPENQUERY
-- SQL select into temp table
SELECT * INTO   #poh
FROM   Openquery(SERVERALPHA,'exec tempdb.dbo.DynamicSprocToTempTable')
GO
 
SELECT PurchaseOrderID, VendorID, OrderDate
FROM   #poh
 
/* Results
 
PurchaseOrderID  VendorID    OrderDate
2662              50          2004-05-19 00:00:00.000
2454              44          2004-05-01 00:00:00.000
2547              96          2004-05-10 00:00:00.000
901               54          2003-10-13 00:00:00.000
2675              74          2004-05-22 00:00:00.000
*/
GO
 
-- Cleanup
DROP PROCEDURE DynamicSprocToTempTable
DROP TABLE #poh

----------
 

-- 15. SELECT INTO new table with UNIQUE ID column.


USE
tempdb;
-- SQL select into with identity function
SELECT TOP (100) ID=identity(int,1,1),
                        PurchaseOrderID=convert(int,PurchaseOrderID), OrderDate
INTO POH
FROM AdventureWorks.Purchasing.PurchaseOrderHeader ORDER BY NEWID()
GO
SELECT * from POH
GO
/* Partial results
 
ID    PurchaseOrderID   OrderDate
1     447               2003-09-06 00:00:00.000
2     1881              2004-03-07 00:00:00.000
3     2519              2004-05-08 00:00:00.000
4     1404              2004-01-13 00:00:00.000
*/
DROP TABLE tempdb.dbo.POH
GO
 
-- SQL select into with row_number function
SELECT TOP (100) ID=ROW_NUMBER() OVER(ORDER BY OrderDate DESC),
                         PurchaseOrderID, OrderDate
INTO POH
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
GO
SELECT * from POH
GO
/* Partial results
 
ID    PurchaseOrderID   OrderDate
1     4002              2004-10-23 00:00:00.000
2     3985              2004-09-03 00:00:00.000
3     3986              2004-09-03 00:00:00.000
4     3987              2004-09-03 00:00:00.000
5     3988              2004-09-03 00:00:00.000
*/
DROP TABLE tempdb.dbo.POH

GO

----------
 

-- 16. SELECT INTO table create is very fast.
 
-- SELECT INTO is minimally logged - inside transaction only
-- SELECT INTO not logged into the transaction log file
-- SELECT INTO table cannot be recovered with transaction log restore
-- SELECT INTO table can only be recovered after full database backup
USE AdventureWorks2008;
BEGIN TRANSACTION
SELECT * INTO ProdZ FROM Production.Product
SELECT COUNT(*) FROM ProdZ
-- Result: 504
ROLLBACK TRANSACTION
SELECT COUNT(*) FROM ProdZ
/* Msg 208, Level 16, State 1, Line 6
Invalid object name 'ProdZ'.
*/

----------
 

-- 17. SELECT INTO temporary table with GROUP BY aggregate query.


-- List bestseller products

-- SQL select into temporary table
USE Northwind;
SELECT   ProductName,
         TotalSold = Sum(Quantity)
INTO #BestSellerProducts
FROM     [Order Details] od
         INNER JOIN Products p
           ON p.ProductID = od.ProductID
GROUP BY ProductName
HAVING   Sum(Quantity) > 1.75 * (SELECT Avg(TotalSold)
                                 FROM   (SELECT   TotalSold = (Sum(Quantity))
                                         FROM     [Order Details]
                                         GROUP BY ProductID) x)
ORDER BY TotalSold DESC
GO
SELECT * FROM #BestSellerProducts
GO
/* Results
 
ProductName             TotalSold
Camembert Pierrot       1577
Raclette Courdavault    1496
Gorgonzola Telino       1397
Gnocchi di nonna Alice  1263
*/
 
-- Cleanup
DROP TABLE #BestSellerProducts
GO

----------
 

-- 18. Using SELECT INTO solution instead of deleting many (i.e. millions) rows.


-- SQL using SELECT INTO instead of huge delete
- SQL delete table
-- SQL Server large delete - SQL delete millions of rows
-- Note: this is a demo only with small tables
USE tempdb;
 
SELECT *
INTO   SOH
FROM   AdventureWorks.Sales.SalesOrderHeader
GO
-- (31464 row(s) affected)
SELECT count(* )
FROM   SOH
GO
-- 31464
 
/* We need to delete all records prior to 2004/01/01 like:
 
delete SOH where OrderDate < '2004-01-01'
 
Since DELETE is a logged operation, it may take long especially with multiple indexes.
Instead we build a brand new table with the records we need to keep
and rebuild the indexes, defaults, etc, finally we switch the table with sp_rename.
*/
-- Select into records we want to keep
SELECT *
INTO   SOHStage
FROM   SOH
WHERE  OrderDate >= '2004-01-01'
GO
-- (13950 row(s) affected)
 
-- SQL rename tables  - switch new trimmed table for current table
BEGIN TRANSACTION
EXEC sp_rename
  SOH ,
  SOHArchive
 
EXEC sp_rename
  SOHStage ,
  SOH
COMMIT TRANSACTION
GO
 
/* Messages
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
*/
SELECT count(* )
FROM   SOH
GO
-- 13950
 
-- Cleanup
DROP TABLE tempdb.dbo.SOH
DROP TABLE tempdb.dbo.SOHArchive
GO

------------

 

-- 19. Using SELECT INTO temporary table from view.

-- Select into temporary table from view

-- Select from view into table
-- SQL currency formatting - format money
-- SQL coalesce
SELECT
       [FullName]
      ,[SalesPersonID] AS StaffID
      ,[SalesTerritory]
      ,COALESCE('$'+convert(varchar,[2002],1),'') AS [2002]
      ,COALESCE('$'+convert(varchar,[2003],1),'') AS [2003]
      ,COALESCE('$'+convert(varchar,[2004],1),'') AS [2004]
  INTO #Sales
  FROM [AdventureWorks].[Sales].[vSalesPersonSalesByFiscalYears]
  ORDER BY FullName
GO
 
SELECT * FROM #Sales
ORDER BY FullName
GO
 
/* Partial results
 
FullName                StaffID     SalesTerritory    2002
David R Campbell        283         Northwest         $1,017,402.86
Garrett R Vargas        278         Canada            $930,259.47
Jae B Pak               285         United Kingdom   
Jillian  Carson         277         Central           $2,737,537.88
José Edvaldo Saraiva    282         Canada            $2,088,491.17
Linda C Mitchell        276         Southwest         $2,260,118.45
*/
 
-- Cleanup
DROP TABLE #Sales

GO

------------

 

-- 20. Using SELECT INTO for random sampling of a large table.


-- SQL Random Select - See also tablesample

-- Random sampling in T-SQL - SQL Server random row selection
SELECT TOP (2) PERCENT * INTO SOHsample
FROM AdventureWorks2008.Sales.SalesOrderHeader

-- (630 row(s) affected)

------------

 

-- 21. SELECT INTO from STORED PROCEDURE EXECUTION.


-- SQL select into table create from sproc

-- T-SQL dynamic SQL OPENQUERY - QUOTENAME
/*
DATA ACCESS to current SQL Server instance can be setup the following way
exec sp_serveroption @server = 'PRODSVR\SQL2008'
      ,@optname = 'DATA ACCESS'
      ,@optvalue = 'TRUE'
 
This way, OPENQUERY can be used against current instance
(Usually OPENQUERY is used to access linked servers.)
*/
-- SQL 2008 new feature: instant assignment to @localvariable
DECLARE @DynamicQuery nvarchar(max) =
      'SELECT *
      INTO BikeBOM
      FROM OPENQUERY(' + QUOTENAME(CONVERT(sysname, @@SERVERNAME))+ ',
      ''EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 400,
      ''''2003-11-21'''''')'
PRINT @DynamicQuery
/*
SELECT *
INTO BikeBOM
FROM OPENQUERY([PRODSVR\SQL2008],
'EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 3,
''2003-12-01''')
*/
 
EXEC sp_executeSQL @DynamicQuery
 
 
SELECT TOP ( 5 ) *
FROM BikeBOM
ORDER BY NEWID()
GO
/* Partial results
 
ProductAssemblyID ComponentID ComponentDesc
765               826         Road-650 Black, 58
769               826         Road-650 Black, 48
823               400         LL Mountain Rear Wheel
985               823         Mountain-500 Silver, 42
983               815         Mountain-400-W Silver, 46
*/
 
-- Cleanup
DROP TABLE BikeBOM
GO
------------
 
-- 22. SELECT INTO import data from Excel worksheet with distributed query . 
 

-- T-SQL import Excel worksheet into database table
SELECT * 
INTO ContactList
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\data\excel\Contact.xls', 'SELECT * FROM [Contact$]')
-- (19972 row(s) affected)

------------