{"id":1823,"date":"2014-06-18T00:00:00","date_gmt":"2014-06-18T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-tables-11-questions-you-were-too-shy-to-ask\/"},"modified":"2021-09-29T16:21:36","modified_gmt":"2021-09-29T16:21:36","slug":"sql-server-tables-11-questions-you-were-too-shy-to-ask","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-tables-11-questions-you-were-too-shy-to-ask\/","title":{"rendered":"SQL Server Tables – 11 Questions You Were Too Shy to Ask"},"content":{"rendered":"
\n

The Questions<\/h2>\n
    \n
  1. “I often come across T-SQL script in which the names of tables, columns and other objects are enclosed in brackets. Even data types are often treated this way. Do I have to enclose object names in brackets?<\/a>“<\/li>\n
  2. “I’m trying to create a table that contains a foreign key, but I keep getting an error message saying that there are no primary or candidate keys in the referenced table that match the referencing column list. What’s going on?<\/a>“<\/li>\n
  3. “Why are there so many different ways to create a primary key? <\/a>“<\/li>\n
  4. “I’m developing several stored procedures whose logic requires temporary structures for storing data. Which are better, table variables or temporary tables? <\/a>“<\/li>\n
  5. “I keep reading that I’m supposed to avoid null values in my databases. Should I define default constraints rather than permit nullable columns?<\/a>“<\/li>\n
  6. “When I try to run a CREATE TABLE AS SELECT (CTAS) statement in SQL Server, I receive an error. Does SQL Server support the CTAS statement?<\/a>“<\/li>\n
  7. “When creating objects in a SQL Server database, should I assign them to the default (dbo) schema or to a custom schema?<\/a>“<\/li>\n
  8. “When I try to incorporate a subquery in my CHECK constraints, SQL Server generates an error. Is there a way to use subqueries in CHECK constraints? <\/a>“<\/li>\n
  9. “I see that many SQL Server developers add a prefix to object names, such as ‘tbl’ for a table or ‘sp’ for a stored procedure. Is it considered best practices to include prefixes? <\/a>“<\/li>\n
  10. “Why bother using the FILESTREAM features in SQL Server for BLOB data? Why not just store the files directly on the file system?<\/a>“<\/li>\n
  11. “When I try to persist a computer column that contains the GETDATE function, I receive an error saying that I can’t persist the column because it’s non-deterministic. What difference does it make?<\/a>“<\/li>\n<\/ol>\n

    “I often come across T-SQL script in which the names of tables, columns and other objects are enclosed in brackets. Even data types are often treated this way. Do I have to enclose object names in brackets?”<\/h3>\n

    \tNo, you don’t, except in certain situations. Part of the confusion comes from the auto-generated scripts in SQL Server Management Studio (SSMS), which enclose all identifiers in brackets, whether they need it or not. For example, I added the <\/code> table to the AdventureWorks2012<\/code> database and then used SSMS to generate the following T-SQL script: <\/p>\n

    USE [AdventureWorks2012]\nGO\n\n\/****** Object:  Table [dbo].[Region & Group]    Script Date: 6\/9\/2014 7:09:18 PM ******\/\nSET ANSI_NULLS ON\nGO\n\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE TABLE [dbo].[Region & Group](\n\t[Region ID] [int] IDENTITY(1,1) NOT NULL,\n\t[Sales Region-Group] [nvarchar](30) NOT NULL,\nPRIMARY KEY CLUSTERED \n(\n\t[Region ID] ASC\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,\n   ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]\n) ON [PRIMARY]\n\nGO\t\n<\/pre>\n
     \tAlthough this is only a simple example, you can see that the database, schema, table, column, data type and filegroup names are all enclosed in brackets. However, the brackets are actually required only for the table and column names because they contain spaces, special characters (<\/code> and -<\/code>), and the reserved keyword Group<\/code>. <\/p>\n
     \tMany teams (most?) plan their naming strategies to avoid spaces, special characters, and reserved keywords. In some cases, however, a word used as an identifier in an earlier SQL Server version might become a reserved keyword in a later version. If you port your database to the newer version under such circumstances, you’ll need to delimit the identifier with brackets or, as you’ll see shortly, in double quotes. <\/p>\n
     \tIt might surprise you to see data types bracketed along with the other identifiers, but data types are named objects, not simply reserved keywords. As a result, they must conform to the same naming rules as other objects. That means you can name user-defined types just like you do other objects, which opens up the possibility of spaces, special characters, and reserved keywords. <\/p>\n
     \tTo avoid any possible confusion, SSMS takes the safest route possible when generating T-SQL script by enclosing all identifiers in brackets, without distinguishing between those that need them and those that do not. That said, many developers find this to be an annoying and cumbersome practice that makes the code difficult to read. For that reason, they might delimit identifiers only when necessary and skip the rest. <\/p>\n
     \tLet’s return to our sample <\/code> table and look at its definition: <\/p>\n
    USE AdventureWorks2012;\nGO\n \nIF OBJECT_ID('Region & Group', 'U') IS NOT NULL\nDROP TABLE Region & Group;\nGO\n \nCREATE TABLE Region & Group\n(\n  Region ID INT NOT NULL IDENTITY PRIMARY KEY,\n  Sales Region-Group NVARCHAR(30) NOT NULL\n);\nGO<\/pre>\n
     \tIf we were to try to run this script as is, we’d receive syntax errors, not surprising given the hodgepodge of spaces and special characters. When the database engine tries to parse the code, it cannot distinguish the space in the name from other spaces. It also sees Group<\/code> as a reserved keyword and the ampersand (<\/code>) as a bitwise operator. In other words, the parser doesn’t have a clue what’s going on. <\/p>\n
     \tIf we insist on continuing with this naming strategy, we can easily rectify the situation by enclosing the questionable table and column names in brackets, as in the following example: <\/p>\n
    USE AdventureWorks2012;\nGO\n \nIF OBJECT_ID('Region & Group', 'U') IS NOT NULL\nDROP TABLE [Region & Group];\nGO\n \nCREATE TABLE [Region & Group]\n(\n  [Region ID] INT NOT NULL IDENTITY PRIMARY KEY,\n  [Sales Region-Group] NVARCHAR(30) NOT NULL\n);\nGO<\/pre>\n
     \tNotice we need to delimit only the offending identifiers. Now the database engine will create the table with no problem. As an alternative to this approach, we can use double quotes instead of brackets, as in the following T-SQL: <\/p>\n
    USE AdventureWorks2012;\nGO\n \nIF OBJECT_ID('Region & Group', 'U') IS NOT NULL\nDROP TABLE \"Region & Group\";\nGO\n \nCREATE TABLE \"Region & Group\"\n(\n  \"Region ID\" INT NOT NULL IDENTITY PRIMARY KEY,\n  \"Sales Region-Group\" NVARCHAR(30) NOT NULL\n);\nGO<\/pre>\n
     \tBe aware, however, that we can use double quotes only if the database’s QUOTED_IDENTIFIER<\/code> option is set to ON<\/code>. This setting conforms to ISO standards and is the default when creating a database in SQL Server. If the option is set to OFF<\/code>, the names enclosed in double quotes are treated as literal string values, the same as with single quotes, although even if the option is set to OFF<\/code>, we can still use brackets: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nSET QUOTED_IDENTIFIER OFF;\nGO\n\nIF OBJECT_ID('Region & Group', 'U') IS NOT NULL\nDROP TABLE [Region & Group];\nGO\n \nCREATE TABLE [Region & Group]\n(\n  [Region ID] INT NOT NULL IDENTITY PRIMARY KEY,\n  [Sales Region-Group] NVARCHAR(30) NOT NULL\n);\nGO\t\n<\/pre>\n
     \tNotice that the script includes a SET<\/code> statement that sets the QUOTED_IDENTIFIER<\/code> option to O FF<\/code>, but the table will still be created with no problem. That’s not the case with double quotes. The following example will generate syntax errors when you try to run it: <\/p>\n
    example will generate syntax errors when you try to run it:\nUSE AdventureWorks2012;\nGO\n\nSET QUOTED_IDENTIFIER OFF;\nGO\n \nIF OBJECT_ID('Region & Group', 'U') IS NOT NULL\nDROP TABLE \"Region & Group\";\nGO\n \nCREATE TABLE \"Region & Group\"\n(\n  \"Region ID\" INT NOT NULL IDENTITY PRIMARY KEY,\n  \"Sales Region-Group\" NVARCHAR(30) NOT NULL\n);\nGO<\/pre>\n
     \tBut simply changing the QUOTED_IDENTIFIER<\/code> option to ON<\/code> fixes the problem: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nSET QUOTED_IDENTIFIER ON;\nGO\n \nIF OBJECT_ID('Region & Group', 'U') IS NOT NULL\nDROP TABLE \"Region & Group\";\nGO\n \nCREATE TABLE \"Region & Group\"\n(\n  \"Region ID\" INT NOT NULL IDENTITY PRIMARY KEY,\n  \"Sales Region-Group\" NVARCHAR(30) NOT NULL\n);\nGO<\/pre>\n
     \tThe database engine now creates the table without a hitch. Note, however, even with the QUOTED_IDENTIFIER<\/code> option is set to ON<\/code>, if we use single quotes for identifiers, as in the following example, the script will generate more syntax errors: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nSET QUOTED_IDENTIFIER ON;\nGO\n \nIF OBJECT_ID('Region & Group', 'U') IS NOT NULL\nDROP TABLE 'Region & Group';\nGO\n \nCREATE TABLE 'Region & Group'\n(\n  'Region ID' INT NOT NULL IDENTITY PRIMARY KEY,\n  'Sales Region-Group' NVARCHAR(30) NOT NULL\n);\nGO<\/pre>\n
     \tJust to be safe, some SQL Server developers might choose to enclose all their identifiers in brackets or double quotes, even if they avoid spaces, special characters, and reserved keywords. Others might do everything they can to avoid having to delimit their identifiers. It’s mostly a matter of preference. <\/p>\n
    “I’m trying to create a table that contains a foreign key, but I keep getting an error message saying that there are no primary or candidate keys in the referenced table that match the referencing column list. What’s going on?”<\/h3>\n
     \tChances are, you’re receiving the error because the column referenced in your FOREIGN KEY<\/code> constraint is not configured with a PRIMARY KEY<\/code> or UNIQUE<\/code> constraint or is not part of a unique index. In other words, the referenced column or columns must be unique. A FOREIGN KEY<\/code> constraint controls the data that can be stored in a table and enforces data integrity. For this to be possible, the values in the referenced column must be unique so you can clearly establish a link between the rows in the two tables. <\/p>\n
     \tIf we try to create a FOREIGN KEY<\/code> constraint that references a non-unique column we’ll receive an error. For example, suppose we add the SalesRegion<\/code> and RegionalSales<\/code> tables to our database: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n \nIF OBJECT_ID('SalesRegion', 'U') IS NOT NULL\nDROP TABLE SalesRegion;\nGO\n \nCREATE TABLE SalesRegion\n(\n  RegionID INT NOT NULL IDENTITY,\n  RegionName NVARCHAR(30) NOT NULL\n);\nGO\n\nCREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  RegionID INT NOT NULL\n    REFERENCES SalesRegion(RegionID)\n);\nGO<\/pre>\n
     \tThe RegionalSales<\/code> table includes a FOREIGN KEY<\/code> constraint that references the RegionID<\/code> column in the SalesRegion<\/code> table. However, that column has not been defined with a PRIMARY KEY<\/code> or UNIQUE<\/code> constraint, nor is it part of a unique index. As a result, the script generates the following error: <\/p>\n
    Msg 1776, Level 16, State 0, Line 1\nThere are no primary or candidate keys in the referenced table 'SalesRegion' that match the \n    referencing column list in the foreign key 'FK__RegionalS__Regio__4D9F7493'.\nMsg 1750, Level 16, State 0, Line 1\nCould not create constraint. See previous errors.\n<\/pre>\n
     \tThis, of course is easily fixed by defining a primary key on the RegionID<\/code> column: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n \nIF OBJECT_ID('SalesRegion', 'U') IS NOT NULL\nDROP TABLE SalesRegion;\nGO\n \nCREATE TABLE SalesRegion\n(\n  RegionID INT NOT NULL IDENTITY PRIMARY KEY,\n  RegionName NVARCHAR(30) NOT NULL\n);\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  RegionID INT NOT NULL\n    REFERENCES SalesRegion(RegionID)\n);\nGO\n<\/pre>\n
     \tNow when we run the script, both tables are created without a hitch. If we insert data into the RegionalSales<\/code> table that does not violate the foreign key, we should have no problem. But if the data violates the foreign key, we’ll receive an error. <\/p>\n
    “Why are there so many different ways to create a primary key?”<\/h3>\n
     \tWhy? Because circumstances can vary from one table to the next, depending on whether you’re creating a primary key on one column or on multiple columns and on whether you’re creating the primary key on an existing table or as part of the definition of a new table. That said, SQL Server still provides multiple ways to achieve the same results. <\/p>\n
     \tTake, for example, the following CREATE TABLE<\/code> statement, which defines the primary key as a constraint on the SalesID<\/code> column: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE()\n);\nGO<\/pre>\n
     \tThis is the easiest way to create a primary key: as part of a column definition in a CREATE TABLE<\/code> statement. You need only include the PRIMARY KEY<\/code> keywords in the column definition, and the database engine does the rest. It sets the column’s nullability to NOT NULL<\/code>, if the column is not already configured that way, and creates a unique clustered index on the column, if a clustered index is not defined elsewhere; otherwise, it creates a unique nonclustered index. <\/p>\n
     \tWhen defining a column PRIMARY KEY<\/code> constraint, you can get even more specific by providing the constraint name and specifying the type of index, as shown in the following example: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT \n    CONSTRAINT pk_RegionalSales PRIMARY KEY CLUSTERED,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE()\n);\nGO\n<\/pre>\n
     \tNotice that we’ve included the constraint name (pk_RegionalSales<\/code>) and index type (CLUSTERED<\/code>). If we don’t assign a name to the primary key, the database engine generates something messy such as PK__Regional__C952FB12B0CC5751<\/code>. Luckily, we can avoid it by including the information as part of a column constraint, as we’ve done here, or by defining a table constraint, as shown in the following example: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  CONSTRAINT pk_RegionalSales \n    PRIMARY KEY CLUSTERED (SalesID ASC) \n);\nGO   \n<\/pre>\n
     \tThis time around, the SalesID<\/code> column is set up as a regular INT<\/code> column, with the constraint definition added separately. Again, we’re able to name the constraint (pk_RegionalSales<\/code>) and specify that the index be clustered. <\/p>\n
     \tIt’s up to you whether you use a column constraint or table constraint when defining a primary key on a single column. However, to create a composite primary key, that is, one that contains more than one column, you must use a table constraint. For example, the following CREATE TABLE<\/code> statement creates a primary key on the SalesID<\/code> and DetailID<\/code> columns: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL,\n  DetailID INT NOT NULL,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  CONSTRAINT pk_RegionalSales \n    PRIMARY KEY CLUSTERED (SalesID, DetailID ASC) \n);\nGO<\/pre>\n
     \tNotice our constraint definition now specifies the two columns, in the order they should be indexed. Also, as before, we’ve named the constraint and specified the index type. <\/p>\n
     \tNow let’s look at another approach to defining a primary key, this time on an existing table. In the following example, we first create our table and then run an ALTER TABLE<\/code> statement to create the primary key: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE()\n);\nGO\n\nALTER TABLE RegionalSales\nADD CONSTRAINT pk_RegionalSales \n  PRIMARY KEY CLUSTERED (SalesID ASC)\nGO<\/pre>\n
     \tWe use the ALTER TABLE<\/code> statement simply to add the constraint. Like the earlier examples, the constraint is based on only the SalesID<\/code> column. However, we can also use this approach to create a composite primary key on an existing table: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL,\n  DetailID INT NOT NULL,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE()\n);\nGO\n\nALTER TABLE RegionalSales\nADD CONSTRAINT pk_RegionalSales \n  PRIMARY KEY CLUSTERED (SalesID, DetailID ASC)\nGO<\/pre>\n
     \tThat’s all there is to creating a primary key; however, there are a couple of other issues worth noting. If you define a table constraint in your CREATE TABLE<\/code> statement or add the constraint using an ALTER TABLE<\/code> statement, the primary key column must already be configured as NOT NULL<\/code>. In addition, if you specify that a clustered index be created when defining the primary key, a clustered index cannot already exist on the table. <\/p>\n
    “I’m developing several stored procedures whose logic requires temporary structures for storing data. Which are better, table variables or temporary tables?”<\/h3>\n
    On the surface, temporary tables and table variables are very similar. They both store table data in the tempdb<\/code> database and are limited in duration. And creating either type is a simple enough process. For example, the following T-SQL script creates the #sales<\/code> temporary table, populates the table, and retrieves the data: <\/p>\n
    retrieves the data:\nCREATE TABLE #sales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  SalesDate DATETIME NOT NULL DEFAULT GETDATE()\n);\n\nINSERT INTO #sales (SalesAmount) VALUES (123.44);\nINSERT INTO #sales (SalesAmount) VALUES (499.22);\nINSERT INTO #sales (SalesAmount) VALUES (846.29);\n\nSELECT * FROM #sales;<\/pre>\n
     \tAs you can see, we create a temp table similar to how we create a regular table, except that we precede the table name with the hash mark (#<\/code>). This instructs the database engine to create a local temporary table visible only to the current session. If we want to create a global temporary table available to all sessions, we use double hash marks (##<\/code>). <\/p>\n
     \tIn either case, we can insert data into and retrieve data from the temporary table, as we’ve done with #sales<\/code> in the example above. The following table shows the results returned by the SELECT<\/code> statement: <\/p>\n\n\n\n\n\n\n
    \n
    SalesID<\/code><\/p>\n<\/td>\n
    		\n
    SalesAmount<\/code><\/p>\n<\/td>\n
    		\n
    SalesDate<\/code><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    123.44<\/p>\n<\/td>\n
    		\n
    2014-06-10    09:17:35.813<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    499.22<\/p>\n<\/td>\n
    		\n
    2014-06-10    09:17:35.817<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    846.29<\/p>\n<\/td>\n
    		\n
    2014-06-10    09:17:35.817<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNow let’s try to do the same thing with a table variable. In the following T-SQL script, we declare a table variable, insert data into the variable, and then retrieve that data: <\/p>\n
    DECLARE @sales TABLE\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  SalesDate DATETIME NOT NULL DEFAULT GETDATE()\n);\n\nINSERT INTO @sales (SalesAmount) VALUES (123.44);\nINSERT INTO @sales (SalesAmount) VALUES (499.22);\nINSERT INTO @sales (SalesAmount) VALUES (846.29);\n\nSELECT * FROM @sales;<\/pre>\n
     \tNotice that the declaration includes the TABLE<\/code> data type, which gives the variable its table structure. At the same time, we define the columns, their data types, and their nullability. Notice also that, like the temporary table example, we also define an IDENTITY<\/code> column, a PRIMARY KEY<\/code> constraint, and a DEFAULT<\/code> constraint. Next, we insert the same data we inserted into the temporary table and then retrieve that data, as shown in the following table: <\/p>\n\n\n\n\n\n\n
    \n
    SalesID<\/code><\/p>\n<\/td>\n
    		\n
    SalesAmount<\/code><\/p>\n<\/td>\n
    		\n
    SalesDate<\/code><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    123.44<\/p>\n<\/td>\n
    		\n
    2014-06-10    09:25:38.843<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    499.22<\/p>\n<\/td>\n
    		\n
    2014-06-10    09:25:38.843<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    846.29<\/p>\n<\/td>\n
    		\n
    2014-06-10    09:25:38.843<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tAs you can see, the temporary table and the table variable achieve the same results, at least based on what we’ve done here. We need to look a little deeper to understand the differences between them. <\/p>\n
     \tIn general, temporary tables are more robust than table variables. For example, you can run TRUNCATE<\/code> and ALTER TABLE<\/code> statements against a temp table, but not a table variable. A temp table can also be used in a SELECT INTO<\/code> statement, participate in a transaction, and explicitly create clustered and nonclustered indexes. A table variable cannot. A table variable is also limited in scope to the procedure or batch it is called within, and it cannot be referenced by other procedures in the same session. Plus, SQL Server maintains column statistics for temp tables, but not table variables, which can be important to query performance. <\/p>\n
     \tAs a result, it would seem that temporary tables are the way to go, given all their advantages. So why use table variables at all? To begin with, you must use them when defining table-valued functions, which do not permit temp tables. Also, creating a table variable lets you skip the statistics overhead that goes with a temp table, if the optimal query plan is unlikely to change. Table variables also require fewer locking resources and are not affected by transactional rollbacks. <\/p>\n
     \tSo for smaller workloads with a limited scope, a table variable might be all you need. However, when you require the advantages offered by temp tables, use those. Phil Factor offers a great discussion on temporary tables and table variables in his article “Temporary Tables in SQL Server<\/a>.” <\/p>\n
    “I keep reading that I’m supposed to avoid null values in my databases. Should I define default constraints rather than permit nullable columns?”<\/h3>\n
     \tThe debate over whether or not to avoid null values has been raging on for years in the database community. For some developers, the avoidance of NULL<\/code> has taken on almost religious significance. Even Microsoft recommends that you avoid null values because they incur more complexity when querying and updating data. In addition, other column options, such as PRIMARY KEY<\/code> constraints, cannot be used with nullable columns. Yet if you search through the sample Adventure Works databases, you’ll find they’re riddled with null values. <\/p>\n
     \tRegardless of where one might fall on the debate, there’s no denying that NULL<\/code> is a different type of value that requires special handling in SQL Server. A null value indicates the absence of an actual data value. It is not the same as an empty string or a 0, and it provides no mechanism for understanding why a value might be missing. A null value is sometimes interpreted to mean “unknown” or “not applicable” or perhaps something else, but the only fact you can state with certainty is that the value is missing and NULL<\/code> is being used in its place. <\/p>\n
     \tNot surprisingly, a missing value can complicate even the simplest of operations. Consider the standard Boolean logic used in a WHERE<\/code> clause. A typical predicate expression might compare a column value with a literal value. If the values match, a True<\/code> is returned and the row is included. Otherwise, a False<\/code> is returned and the row is not included. Even if you use logical operators to create compound expressions, you’re still relying on that true\/false logic. <\/p>\n
     \tHowever, when you add null values to the mix, you’re introducing a three-valued logic that must incorporate the missing data. Consequently, you might discover subtle differences in your query results that are not always apparent or readily understood. Comparing known data to missing data is never a straightforward process. Even a basic arithmetic operation can be affected if NULL<\/code> shows up on the scene. Imagine trying to multiply 10<\/code> by NULL<\/code>: 10 times something missing<\/em> is still something missing. A full discussion of the impact of NULL<\/code> is beyond the scope of this answer, but suffice it to say that these reasons are enough for many SQL Server developers to want to avoid null values altogether. <\/p>\n
     \tIn some cases, you can get around null values by modifying your schema, essentially taking an ultra-normalization approach by putting the offending column in a separate table, but that can lead to extraordinarily complex joins even to perform the simplest queries. <\/p>\n
     \tAnother approach often touted is to use default values in place of null values. For example, suppose we start with the following table, which contains several nullable columns: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  Freight MONEY NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  ShipDate DATETIME NULL,\n  Comments NVARCHAR(MAX) NULL\n);\nGO\n\nINSERT INTO RegionalSales (SalesAmount) VALUES(476.36);\n\nSELECT * FROM RegionalSales;<\/pre>\n
     \tAfter we create the table, we insert data, leaving a couple columns with missing values. When we then retrieve that data, we receive results similar to the following: <\/p>\n\n\n\n\n
    \n
    SalesID<\/code><\/p>\n<\/td>\n
    		\n
    SalesAmount<\/code><\/p>\n<\/td>\n
    		\n
    Freight<\/code><\/p>\n<\/td>\n
    		\n
    OrderDate<\/code><\/p>\n<\/td>\n
    		\n
    ShipDate<\/code><\/p>\n<\/td>\n
    		\n
    Comments<\/code><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    476.36<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    2014-06-10    10:00:18.250<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNot surprisingly, if we were to insert lots of rows in this table, we could potentially have a table teaming with null values. To get around this, we can define defaults on the nullable columns and configure them as NOT NULL<\/code>: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\n CREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  Freight MONEY NOT NULL DEFAULT 0,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  ShipDate DATETIME NOT NULL DEFAULT '',\n  Comments NVARCHAR(MAX) NOT NULL DEFAULT ''\n);\nGO\n\nINSERT INTO RegionalSales (SalesAmount) VALUES(476.36);\n\nSELECT * FROM RegionalSales;<\/pre>\n
     \tFor the ShipDate<\/code> and Comments<\/code> columns, we define each default as an empty string, and for the Freight<\/code> column, we define the default as 0<\/code>. We then insert a row, once again specifying a value only for the SalesAmount<\/code> column. The default values take care of the rest, as shown in the following results: <\/p>\n\n\n\n\n
    \n
    SalesID<\/code><\/p>\n<\/td>\n
    		\n
    SalesAmount<\/code><\/p>\n<\/td>\n
    		\n
    Freight<\/code><\/p>\n<\/td>\n
    		\n
    OrderDate<\/code><\/p>\n<\/td>\n
    		\n
    ShipDate<\/code><\/p>\n<\/td>\n
    		\n
    Comments<\/code><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    476.36<\/p>\n<\/td>\n
    		\n
    0.00<\/p>\n<\/td>\n
    		\n
    2014-06-10    10:03:17.350<\/p>\n<\/td>\n
    		\n
    1900-01-01    00:00:00.000<\/p>\n<\/td>\n
    		<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tFor the Comments<\/code> column, the default approach might work okay in certain circumstances. The default is an empty string, so the column simply appears empty. Keep in mind, however, that using an empty string as a default can impact operations that rely on expressions such as COALESCE<\/code>, operators such as IS NULL<\/code>, aggregate functions such as MAX<\/code>, concatenation operations and sorting. <\/p>\n
     \tUsing a default gets even more complicated for other column types. For example, the ShipDate<\/code> column also uses an empty string as a default. However, because this is a DATETIME<\/code> column, the stored value must be specific date and time (which is actually stored as two integers). When a value is not included in a DATETIME<\/code> column, the database engine uses the base date, 1900-01-01 00:00:00.000<\/code>. <\/p>\n
     \tThen we have the Freight<\/code> column, whose default value is 0<\/code>. Ask your general variety accountants about the use of 0, and they will undoubtedly tell you that 0 and NULL are absolutely not one in the same and should never<\/em> be treated as such. Assuming a 0 where a value is not supplied can have a serious impact on calculations. For example, if you were to run the AVG<\/code> aggregate function against the Freight<\/code> column, the function would ignore null values but include all the zeroes in the calculations. <\/p>\n
     \tSo the default route is not all its cracked up to be, yet adding tables doesn’t necessarily cut it either. Depending on the data, you might be able to use binary, XML or other types of storage to accommodate particular types of data that might have a lot of missing data, such the properties on a customer record. On the other hand, you might find the way you’re supporting null values presents no problems for your applications. In general, however, it’s a good idea to minimize null values. And in those cases when using them makes the most sense, be aware of how the data is being queried and what those null values mean to your result sets. <\/p>\n
    “When I try to run a CREATE TABLE AS SELECT (CTAS) statement in SQL Server, I receive an error. Does SQL Server support the CTAS statement?”<\/h3>\n
     \tYes, it does, sort of, in a somewhat different form. But first, let’s clarify what we’re talking about here. The CTAS statement is part of the SQL standard and looks something like the following: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('Employees', 'U') IS NOT NULL\nDROP TABLE Employees;\nGO\n\nCREATE TABLE Employees AS (SELECT * from vEmployees);\nGO<\/pre>\n
     \tEssentially, we’re using a SELECT<\/code> statement to create the Employees<\/code> table. Unfortunately, despite the fact that this form of the CREATE TABLE<\/code> statement is standard SQL and implemented by such database systems as Oracle and MySQL, SQL Server doesn’t support it, so if you try to run the statement you’ll receive a syntax error. The good news is that SQL Server provides another method for achieving the same results, the SELECT...INTO<\/code> statement, as shown in the following example: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('Employees', 'U') IS NOT NULL\nDROP TABLE Employees;\nGO\n\nSELECT * INTO Employees FROM vEmployees;\nGO<\/pre>\n
     \tBasically, we’ve added an INTO<\/code> clause to a SELECT<\/code> statement to create a table based on our result set. Consequently, the new table will have the same columns as the original columns and be populated with the returned data. However, this approach doesn’t copy over any constraints or indexes, leaving you with a basic heap. In addition, the SELECT...INTO<\/code> statement works only if the new table does not already exist. If it does exist, then you must use an INSERT<\/code> statement containing a SELECT<\/code> statement that retrieves the values to be inserted into the target table. <\/p>\n
    “When creating objects in a SQL Server database, should I assign them to the default (dbo) schema or to a custom schema?”<\/h3>\n
     \tMicrosoft recommends that you use custom schemas to control access in SQL Server databases, rather than the default dbo<\/code> schema. Custom schemas provide distinct namespaces for individual groups of objects and their data. For example, you might create a schema for Human Resources, one for Marketing, and another for Production. Each schema would contain the tables, views, stored procedures, functions, and other database objects specific to that area of focus. <\/p>\n
     \tPrior to SQL Server 2005, owners and database objects were tightly coupled, with users and object owners being treated as one in the same. In fact, a user could not be deleted without first removing the database objects or changing their owner. As of SQL Server 2005, however, that dependency has been removed, providing greater flexibility and control, while reducing administrative overhead. <\/p>\n
     \tSchemas provide secure containers for protecting database objects and their data. They also provide a way to logical group those objects and data. For example, objects and data related to production would reside in the Production<\/code> schema, objects and data related to Human Resources would reside in the HR<\/code> schema, and so on. Schemas make it easier to manage access across functional areas without increasing administrative complexities or jeopardizing data. <\/p>\n
     \tIn addition, because the dbo<\/code> schema is the default, it could provide an easier path for hackers to exploit a database because the schema as well known and a more likely target. It’s also not a particularly descriptive name, as is a schema such as Sales<\/code>. <\/p>\n
     \tThat said, there’s no technical reason why you should or should not use dbo<\/code>. Depending on your project, creating a lot of schemas might be overkill. For many teams, it comes down to personal preference. You won’t break anything by using dbo<\/code>, but you might appreciate the advantages that come from creating your own schemas. <\/p>\n
    “When I try to incorporate a subquery in my CHECK constraints, SQL Server generates an error. Is there a way to use subqueries in CHECK constraints?”<\/h3>\n
     \tYou cannot use subqueries in CHECK<\/code> constraints. Here’s proof. The following T-SQL script attempts to create the RegionalSales<\/code> table, which includes a CHECK<\/code> constraint on the ProdID<\/code> column: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\nCREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  ProdID INT NOT NULL CHECK(\n    (SELECT SUM(i.Quantity) FROM Production.ProductInventory i \n      WHERE i.ProductID = ProdID) > 0)\n);\nGO<\/pre>\n
     \tThe CHECK<\/code> constraint uses a subquery to aggregate data in the ProductInventory<\/code> table in order to ensure that the item is in stock before entering the product ID. Unfortunately, if you were to try to run the CREATE TABLE<\/code> statement, you would receive the following error: <\/p>\n
    Msg 1046, Level 15, State 1, Line 8\nSubqueries are not allowed in this context. Only scalar expressions are allowed.<\/pre>\n
     \tSee? Just like I told you. No subqueries. Only scalar expressions. <\/p>\n
     \tOne way around this dilemma is to create a scalar function that incorporates the subquery’s logic. The following function definition retrieves the number of products on-hand, based on the inputted product ID: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID(N'dbo.CheckQuantity', N'FN') IS NOT NULL\nDROP FUNCTION dbo.CheckQuantity;\nGO\n\nCREATE FUNCTION dbo.CheckQuantity (@ProdID INT)\nRETURNS INT\nWITH SCHEMABINDING AS\nBEGIN\n  DECLARE @quantity SMALLINT\n  SET @quantity = \n    (SELECT SUM(i.Quantity) FROM Production.ProductInventory i \n      WHERE i.ProductID = @ProdID)\n  RETURN @quantity;\nEND;\nGO<\/pre>\n
     \tNow we can use the function in our CHECK<\/code> constraint, as shown in the following CREATE TABLE<\/code> statement: <\/p>\n
    IF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\nCREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  ProdID INT NOT NULL CHECK(\n    dbo.CheckQuantity(ProdID) > 0 \n      AND dbo.CheckQuantity(ProdID) IS NOT NULL)\n);\nGO<\/pre>\n
     \tNotice that when we call the function, we pass in the ProdID<\/code> column. We’ve also included AND dbo.CheckQuantity ( ProdID ) IS NOT NUL L<\/code> in the CHECK<\/code> constraint to handle the possibility of the function returning a null value because the product doesn’t exist. <\/p>\n
     \tOnce we’ve created our table, we can try out our function by attempting to insert data into the table: <\/p>\n
    INSERT INTO RegionalSales (SalesAmount, ProdID)\nVALUES(438.22, 316);\n\nSELECT * FROM RegionalSales;\n<\/pre>\n
     \tBecause 316 is a valid product ID and is in stock, we’re able to insert the data. The SELECT<\/code> statement confirms this by returning the following results: <\/p>\n\n\n\n\n
    \n
    SalesID<\/code><\/p>\n<\/td>\n
    		\n
    SalesAmount<\/code><\/p>\n<\/td>\n
    		\n
    OrderDate<\/code><\/p>\n<\/td>\n
    		\n
    ProdID<\/code><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    438.22<\/p>\n<\/td>\n
    		\n
    2014-06-10    12:18:28.437<\/p>\n<\/td>\n
    		\n
    316<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNow let’s try to insert a product ID for which no products are in stock: <\/p>\n
    INSERT INTO RegionalSales (SalesAmount, ProdID)\nVALUES(438.22, 853);\n<\/pre>\n
     \tAs expected, we receive a message saying that we have a conflict with the CHECK<\/code> constraint: <\/p>\n
    Msg 547, Level 16, State 0, Line 1\nThe INSERT statement conflicted with the CHECK constraint \"CK__RegionalS__ProdI__02133CD2\". The conflict occurred in database \"AdventureWorks2012\", table \"dbo.RegionalSales\", column 'ProdID'.\nThe statement has been terminated.\n<\/pre>\n
     \tWe’ll receive the same message if we try to pass in a fake ID: <\/p>\n
    INSERT INTO RegionalSales (SalesAmount, ProdID)\nVALUES(438.22, 1111);\n<\/pre>\n
     \tSo a scalar function can provide a reasonable workaround to the subquery restriction when creating a CHECK<\/code> constraint. Be aware, however, that scalar functions are executed on a row-by-row basis. If you’re inserting or updating lots of rows, there will be lots of processing, especially if you’re function is performing complex aggregations or other operations. Be aware, too, that some developers have found that implementing a user-defined function in a CHECK<\/code> constraint can result in unexpected results, so be sure to test your solution thoroughly. At times, you might find you can achieve the results you need by using other mechanisms, such as foreign keys or triggers. You’ll have to take it on a case-by-case basis. <\/p>\n
    “I see that many SQL Server developers add a prefix to object names, such as ‘tbl’ for a table or ‘sp’ for a stored procedure. Is it considered best practices to include prefixes?”<\/h3>\n
     \tFew issues in the world of database development continue to be as hotly contested as whether to add prefixes to database object names. Many teams avoid them altogether. Others embrace them at all levels, using conventions such as tbl<\/em> for tables, vw<\/em> for views, and sp<\/em> for stored procedures, ending up with names similar to tblRegionalSales<\/code>, vwQuarterlySales<\/code>, or spUpdateSalesAmounts<\/code>. Some teams use them for only certain types of objects, such as views or stored procedures. <\/p>\n
     \tProponents of such an approach argue that adding prefixes provides a quick and easy method for identifying the type of object you’re working with so you know instantly whether you’re dealing with a table or a view or some other type of entity. At the same time, prefixes have no impact on performance or security, but can help expedite searching for particular object types. <\/p>\n
     \tEven so, prefixing database object names has grown out of favor with many database developers, the belief, at least in part, is that a name is no place for metadata. And they might be right. Consider this scenario. Your database includes the tblRegionalSales<\/code> table. Because of changing business needs, you must replace that table with tblRegionalSales2<\/code>. However, a number of other database objects and application components include references to tblRegionalSales<\/code>, so you create a view named tblRegionalSales<\/code> to avoid a major meltdown (even if only as a temporary fix). Suddenly you have a view with a tbl prefix, throwing your entire naming system out of whack. <\/p>\n
     \tA name and the object it represents are not one in the same, and you cannot assume that a name will continue to reference the same type of object forever. That said, breaking the naming conventions in this way is probably not the end of civilization as we know it, but you do want to be aware of the potential for confusion at some point down the line if you choose this route. <\/p>\n
     \tPerhaps more important than whether or not your team uses prefixes is to be certain that whatever naming conventions you agree upon, you apply them consistently to all database objects. If one developer uses prefixes and another does not, you’ll likely cause even more confusion than choosing one approach over the other. <\/p>\n
    “Why bother using the FILESTREAM features in SQL Server for BLOB data? Why not just store the files directly on the file system?”<\/h3>\n
     \tIn some cases, you might not want to use the FILESTREAM<\/code> capabilities in SQL Server. Microsoft recommends using FILESTREAM<\/code> storage only when your files are larger than 1 MB, you require fast read access, and your application’s middle-tier code needs access to those files. <\/p>\n
     \tOne of the main advantages of using FILESTREAM<\/code> is that you can store and retrieve your BLOB files together with your relational data, all within the same transaction, thus avoiding an elaborate workaround to sync stored files with data in SQL Server. The files are also included in your database backup and restore operations, and you can access BLOB data from within your .NET code, which is typical of a middle-tier operation. At the same time, you can take advantage of the ability of the NTFS file system to save and retrieve the files faster than if they were stored directly in SQL Server. <\/p>\n
     \tTo implement FILESTREAM<\/code> storage, you must first ensure that it is enabled on your SQL Server instance, which you can do through the server properties in SSMS. From there, you must add a FILESTREAM<\/code> filegroup to your database and then add a file to the filegroup, as shown in the following example: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nALTER DATABASE AdventureWorks2012\nADD FILEGROUP AW_images\nCONTAINS FILESTREAM;\nGO\n\nALTER DATABASE AdventureWorks2012\nADD FILE\n(\n  id=\"ProductImages\"',\n  FILENAME = 'C:\\Datafiles\\Filestream\\ProductImages.ndf'\n)\nTO FILEGROUP AW_images;\nGO<\/pre>\n
     \tIn this case, we’ve created a filegroup named AW_images<\/code> and added the file ProductImages.ndf<\/code>. We can now define a table that includes a FILESTREAM<\/code> column: <\/p>\n
    IF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\nCREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  ProdID INT NOT NULL,\n  ProdImage VARBINARY(MAX) FILESTREAM NULL,\n  RowID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE\n    DEFAULT NEWID()\n);\nGO<\/pre>\n
     \tNotice that the ProdImage<\/code> column includes the FILESTREAM<\/code> option to indicate that FILESTREAM<\/code> storage should be used. Also notice that, in order to support FILESTREAM<\/code> storage, we must include a unique ROWGUIDCOL<\/code> column, either as a separate column (as we’ve done here) or as part of the primary key. <\/p>\n
     \tAs you can see, implementing FILESTREAM<\/code> storage is a fairly straightforward process that lets you easily integrate your BLOB and relational data to facilitate application access to both sets of data. If you don’t need these capabilities, there’s no sense incurring the additional overhead, although with relatively little effort, you can have an integrated system that could benefit your applications as well as their users. <\/p>\n
    “When I try to persist a computer column that contains the GETDATE function, I receive an error saying that I can’t persist the column because it’s non-deterministic. What difference does it make?”<\/h3>\n
     \tSQL Server doesn’t like surprises. If we want to persist a computed column, it must be deterministic, that is, it must always produce the same results when given the same input. Most of the time, this issue won’t come up. But if we introduce a non-deterministic function such as GETDATE<\/code> into the mix, we can no longer predict those results. Each time we run GETDATE<\/code> it returns a different value (unless we’re very, very fast). The database engine refuses to persist such ambiguity. <\/p>\n
     \tSQL Server’s resistance to the idea of a non-deterministic computed column is easy enough to demonstrate. The following T-SQL code creates the RegionalSales<\/code> table, which includes the DeliverDate<\/code> computed column: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\nCREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  DeliverDate AS (DATEADD(dd, 14, GETDATE()))\n);\nGO\n\nINSERT INTO RegionalSales (SalesAmount) VALUES (583.22);\n\nSELECT * FROM RegionalSales;\n\n<\/pre>\n
     \tNotice the GETDATE<\/code> function in the computed column definition. Because we have not persisted the column, we can create the table with no problem, after which, we can insert data and retrieve the results, shown in the following table: <\/p>\n\n\n\n\n
    \n
    SalesID<\/code><\/p>\n<\/td>\n
    		\n
    SalesAmount<\/code><\/p>\n<\/td>\n
    		\n
    OrderDate<\/code><\/p>\n<\/td>\n
    		\n
    DeliverDate<\/code><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    583.22<\/p>\n<\/td>\n
    		\n
    2014-06-10    13:08:59.723<\/p>\n<\/td>\n
    		\n
    2014-06-24    13:09:02.783<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNow let’s change our table definition slightly by adding the PERSISTED<\/code> keyword to our computed column: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\nCREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  DeliverDate AS (DATEADD(dd, 14, GETDATE())) PERSISTED\n);\nGO<\/pre>\n
     \tBecause we’ve added the PERSISTED<\/code> keyword, SQL Server should create an index on the DeliverDate<\/code> column that persists all data. Unfortunately, when we now try to create the table, we receive the following error: <\/p>\n
    Msg 4936, Level 16, State 1, Line 2\nComputed column 'DeliverDate' in table 'RegionalSales' cannot be persisted because the column is non-deterministic.\n<\/pre>\n
     \tBecause we’ve included the GETDATE<\/code> function in our computed column, we cannot persist that column. However, in this case, we can get around the limitation by instead specifying the OrderDate<\/code> column, which itself uses the GETDATE<\/code> function: <\/p>\n
    USE AdventureWorks2012;\nGO\n\nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n\nCREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesAmount MONEY NOT NULL,\n  OrderDate DATETIME NOT NULL DEFAULT GETDATE(),\n  DeliverDate AS (DATEADD(dd, 14, OrderDate)) PERSISTED\n);\nGO<\/pre>\n
     \tNow we can create the table with no problem. The GETDATE<\/code> function used in the OrderDate<\/code> column is essentially invisible to the DeliverDate<\/code> column. You might not always be able to come up with this sort of workaround, but it does help give you a better sense of the deterministic nature of persisted computed columns. <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
    There are some aspects of tables in SQL Server that a lot of people get wrong, purely because they seem so obvious that one feels embarrassed about asking questions. Robert Sheldon reckons that no questions about SQL Tables are off-limits, and deserve frank answers.  …<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4156,4150,4151,4252,5771],"coauthors":[],"class_list":["post-1823","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-asp","tag-sql","tag-sql-server","tag-t-sql-programming","tag-too-shy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1823","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1823"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1823\/revisions"}],"predecessor-version":[{"id":92527,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1823\/revisions\/92527"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1823"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1823"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1823"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1823"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}