MS SQL Server Error Types

Error Type =1501,/n Error Description Sort failure.. Error Severity  Level=20

Error Type =1505, Error Description CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID <Index Value>. Most significant primary key is '<Primary Key Name>'.. Error Severity  Level=14

Error Type =1507, Error Description Warning: Deleted duplicate row. Primary key is '<Primary Key Name>'.. Error Severity  Level=10

Error Type =1508, Error Description CREATE INDEX terminated because a duplicate row was found. Primary key is '<Primary Key Name>'.. Error Severity  Level=14

Error Type =1509, Error Description Row compare failure.. Error Severity  Level=20

Error Type =1510, Error Description Sort failed. Out of space or locks in database <Database Name>. Error Severity  Level=17

Error Type =1511, Error Description Sort cannot be reconciled with transaction log.. Error Severity  Level=20

Error Type =1522, Error Description Sort failure. Prevented overwriting of allocation page in database '%.*ls' by terminating sort.. Error Severity  Level=20

Error Type =1523, Error Description Sort failure. Prevented incorrect extent deallocation by aborting sort.. Error Severity  Level=20

Error Type =1528, Error Description Character data comparison failure. An unrecognized Sort-Map-Element type (%d) was found in the server-wide default sort table at SMEL entry [%d].. Error Severity  Level=21

Error Type =1529, Error Description Character data comparison failure. A list of Sort-Map-Elements from the server-wide default sort table does not end properly. This list begins at SMEL entry [%d].. Error Severity  Level=21

Error Type =1530, Error Description CREATE INDEX with DROP_EXISTING was aborted because a row was out of order. Most significant offending primary key is '<Primary Key>'. Explicitly drop and create the index instead.. Error Severity  Level=16

Error Type =1531, Error Description The SORTED_DATA_REORG option cannot be used for a nonclustered index if the keys are not unique within the table. CREATE INDEX was aborted because of duplicate keys. Primary key is '<Primary Key>'.. Error Severity  Level=16

Error Type =1532, Error Description New sort run starting on page %S_PGID found extent not marked as shared.. Error Severity  Level=20

Error Type =1533, Error Description Cannot share extent %S_PGID among more than eight sort runs.. Error Severity  Level=20

Error Type =1534, Error Description Extent %S_PGID not found in shared extent directory.. Error Severity  Level=20

Error Type =1535, Error Description Cannot share extent %S_PGID with shared extent directory full.. Error Severity  Level=20

Error Type =1536, Error Description Cannot build a nonclustered index on a memory-only work table.. Error Severity  Level=20

Error Type =1537, Error Description Cannot suspend a sort not in row input phase.. Error Severity  Level=20

Error Type =1538, Error Description Cannot insert into a sort not in row input phase.. Error Severity  Level=20

Error Type =1540, Error Description Cannot sort a row of size %d, which is greater than the allowable maximum of %d.. Error Severity  Level=16

Error Type =1619, Error Description Could not open tempdb. Cannot continue.. Error Severity  Level=21

Error Type =1620, Error Description Cannot start C2 audit trace. SQL Server is shutting down.. Error Severity  Level=21

Error Type =1621, Error Description Server started with '-f'. Auditing will not be started.. Error Severity  Level=10

Error Type =1701, Error Description Creation of table '<Table Name>' failed because the row size would be <Size>, including internal overhead. This exceeds the maximum allowable table row size, <Maximum Allowable Table Row Size>.. Error Severity  Level=16

Error Type =1702, Error Description CREATE TABLE failed because column '%.*ls' in table '%.*ls' exceeds the maximum of %d columns.. Error Severity  Level=16

Error Type =1703, Error Description Could not allocate disk space for a work table in database '%.*ls'. You may be able to free up space by using BACKUP LOG, or you may want to extend the size of the database by using ALTER DATABASE.. Error Severity  Level=17

Error Type =1704, Error Description Only members of the sysadmin role can create the system table '%.*ls'.. Error Severity  Level=16

Error Type =1705, Error Description You must create system table '%.*ls' in the master database.. Error Severity  Level=16

Error Type =1706, Error Description System table '%.*ls' was not created, because ad hoc updates to system catalogs are not enabled.. Error Severity  Level=16

Error Type =1708, Error Description Warning: The table '%.*ls' has been created but its maximum row size (%d) exceeds the maximum number of bytes per row (%d). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds %d bytes.. Error Severity  Level=10

Error Type =1709, Error Description Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image columns.. Error Severity  Level=16

Error Type =1750, Error Description Could not create constraint. See previous errors.. Error Severity  Level=10

Error Type =1752, Error Description Could not create DEFAULT for column '%.*ls' as it is not a valid column in the table '%.*ls'.. Error Severity  Level=16

Error Type =1753, Error Description Column '<Primary Table.Primary Key>' is not the same length as referencing column '<Referencing Table.Referencing Column>' in foreign key '<Foreign Key Constraint Name>'.. Error Severity  Level=16

Error Type =1754, Error Description Defaults cannot be created on columns with an IDENTITY attribute. Table '%.*ls', column '%.*ls'.. Error Severity  Level=16

Error Type =1755, Error Description Defaults cannot be created on columns of data type timestamp. Table '%.*ls', column '%.*ls'.. Error Severity  Level=16

Error Type =1756, Error Description Skipping FOREIGN KEY constraint '%.*ls' definition for temporary table.. Error Severity  Level=10

Error Type =1757, Error Description Column '%.*ls.%.*ls' is not of same collation as referencing column '%.*ls.%.*ls' in foreign key '%.*ls'.. Error Severity  Level=16

Error Type =1759, Error Description Invalid column '%.*ls' is specified in a constraint or computed-column definition.. Error Severity  Level=16

Error Type =1760, Error Description Constraints of type %ls cannot be created on columns of type %ls.. Error Severity  Level=16

Error Type =1763, Error Description Cross-database foreign key references are not supported. Foreign key '%.*ls'.. Error Severity  Level=16

Error Type =1766, Error Description Foreign key references to temporary tables are not supported. Foreign key '%.*ls'.. Error Severity  Level=16

Error Type =1767, Error Description Foreign key '%.*ls' references invalid table '%.*ls'.. Error Severity  Level=16

Error Type =1768, Error Description Foreign key '%.*ls' references object '%.*ls' which is not a user table.. Error Severity  Level=16

Error Type =1769, Error Description Foreign key '%.*ls' references invalid column '%.*ls' in referencing table '%.*ls'.. Error Severity  Level=16

Error Type =1770, Error Description Foreign key '%.*ls' references invalid column '%.*ls' in referenced table '%.*ls'.. Error Severity  Level=16

Error Type =1772, Error Description Foreign key '%.*ls' defines an invalid relationship between a user table and system table.. Error Severity  Level=16

Error Type =1773, Error Description Foreign key '%.*ls' has implicit reference to object '%.*ls' which does not have a primary key defined on it.. Error Severity  Level=16

Error Type =1774, Error Description The number of columns in the referencing column list for foreign key '%.*ls' does not match those of the primary key in the referenced table '%.*ls'.. Error Severity  Level=16

Error Type =1776, Error Description There are no primary or candidate keys in the referenced table '<Table Name>' that match the referencing column list in the foreign key '<Foreign Key Constraint>'.. Error Severity  Level=16

Error Type =1777, Error Description User does not have correct permissions on referenced table '%.*ls' to create foreign key '%.*ls'.. Error Severity  Level=14

Error Type =1778, Error Description Column '<Primary Table.Primary Key>' is not the same data type as referencing column '<Referencing Table.Referencing Column>' in foreign key '<Foreign Key Constraint>'.. Error Severity  Level=16

Error Type =1779, Error Description Table '%.*ls' already has a primary key defined on it.. Error Severity  Level=16

Error Type =1781, Error Description Column already has a DEFAULT bound to it.. Error Severity  Level=16

Error Type =1784, Error Description Cannot create the foreign key '%.*ls' because the referenced column '%.*ls.%.*ls' is a computed column.. Error Severity  Level=16

Error Type =1785, Error Description Introducing FOREIGN KEY constraint '%.*ls' on table '%.*ls' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.. Error Severity  Level=16

Error Type =1786, Error Description Either column '%.*ls.%.*ls' or referencing column '%.*ls.%.*ls' in foreign key '%.*ls' is a timestamp column. This data type cannot be used with cascading referential integrity constraints.. Error Severity  Level=16

Error Type =1787, Error Description Cannot define foreign key constraint '%.*ls' with cascaded DELETE or UPDATE on table '%.*ls' because the table has an INSTEAD OF DELETE or UPDATE TRIGGER defined on it.. Error Severity  Level=16

Error Type =1788, Error Description Cascading foreign key '%.*ls' cannot be created where the referencing column '%.*ls.%.*ls' is an identity column.. Error Severity  Level=16

Error Type =, Error Description Warning. Line %d: The option '%ls' is obsolete and has no effect.. Error Severity  Level=10

Error Type =1801, Error Description Database '%.*ls' already exists.. Error Severity  Level=16

Error Type =1802, Error Description CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.. Error Severity  Level=11

Error Type =1803, Error Description CREATE DATABASE failed. Could not allocate enough disk space for a new database on the named disks. Total space allocated must be at least %d MB to accommodate a copy of the model database.. Error Severity  Level=17

Error Type =1804, Error Description There is no disk named '%.*ls'. Checking other disk names.. Error Severity  Level=10

Error Type =1805, Error Description The CREATE DATABASE process is allocating %.2f MB on disk '%.*ls'.. Error Severity  Level=10

Error Type =1806, Error Description CREATE DATABASE failed. The default collation of database '%.*ls' cannot be set to '%.*ls'.. Error Severity  Level=16

Error Type =1807, Error Description Could not obtain exclusive lock on database '%.*ls'. Retry the operation later.. Error Severity  Level=17

Error Type =1808, Error Description Default devices are not supported.. Error Severity  Level=21

Error Type =1809, Error Description To achieve optimal performance, update all statistics on the '%.*ls' database by running sp_updatestats.. Error Severity  Level=10

Error Type =1811, Error Description %.*ls' is the wrong type of device for CREATE DATABASE or ALTER DATABASE. Check sysdevices. The statement is aborted.. Error Severity  Level=16

Error Type =1812, Error Description CREATE DATABASE failed. COLLATE clause cannot be used with the FOR ATTACH option.. Error Severity  Level=16

Error Type =1813, Error Description Could not open new database '%.*ls'. CREATE DATABASE is aborted.. Error Severity  Level=16

Error Type =1814, Error Description Could not create tempdb. If space is low, extend the amount of space and restart.. Error Severity  Level=10

Error Type =1901, Error Description Column '%.*ls'. Cannot create index on a column of bit data type.. Error Severity  Level=16

Error Type =1902, Error Description Cannot create more than one clustered index on table '<Table Name>'. Drop the existing clustered index '<Clustered Index Name>' before creating another.. Error Severity  Level=16

Error Type =1903, Error Description Index keys are too large. The %d bytes needed to represent the keys for index %d exceeds the size limit of %d bytes.. Error Severity  Level=16

Error Type =1904, Error Description Cannot specify more than %d column names for statistics or index key list. %d specified.. Error Severity  Level=16

Error Type =1905, Error Description Could not find 'zero' row for index '%.*ls' the table in sysindexes.. Error Severity  Level=21

Error Type =1906, Error Description Cannot create an index on '%.*ls', because this table does not exist in database '%.*ls'.. Error Severity  Level=11

Error Type =1907, Error Description Cannot re-create index '%.*ls'. The new index definition does not match the constraint being enforced by the existing index.. Error Severity  Level=16

Error Type =1909, Error Description Cannot use duplicate column names in index key list. Column name '%.*ls' listed more than once.. Error Severity  Level=16

Error Type =1910, Error Description Cannot create more than %d nonclustered indices or column statistics on one table.. Error Severity  Level=16

Error Type =1911, Error Description Column name '%.*ls' does not exist in the target table.. Error Severity  Level=16

Error Type =1913, Error Description There is already an index on table '%.*ls' named '%.*ls'.. Error Severity  Level=16

Error Type =1914, Error Description Index cannot be created on object '%.*ls' because the object is not a user table or view.. Error Severity  Level=16

Error Type =1916, Error Description CREATE INDEX options %ls and %ls are mutually exclusive.. Error Severity  Level=16

Error Type =1918, Error Description Index (ID = %d) is being rebuilt.. Error Severity  Level=10

Error Type =1919, Error Description Column '%.*ls'. Cannot create index on a column of text, ntext, or image data type.. Error Severity  Level=16

Error Type =1920, Error Description Skipping rebuild of index ID %d, which is on a read-only filegroup.. Error Severity  Level=16

Error Type =1921, Error Description Invalid filegroup '%.*ls' specified.. Error Severity  Level=16

Error Type =1922, Error Description Filegroup '%.*ls' has no files assigned to it. Tables, indexes, and text, ntext, and image columns cannot be created on this filegroup.. Error Severity  Level=16

Error Type =1923, Error Description The clustered index has been dropped.. Error Severity  Level=10

Error Type =1938, Error Description Index cannot be created on %S_MSG '%.*ls' because the underlying object '%.*ls' has a different owner.. Error Severity  Level=16

Error Type =1939, Error Description Cannot create index on view '<View Name>' because the view is not schema bound.. Error Severity  Level=16

Error Type =1940, Error Description Cannot create index on view '<View Name>'. It does not have a unique clustered index.. Error Severity  Level=16

Error Type =1941, Error Description Nonunique clustered index cannot be created on view '%.*ls' because only unique clustered indexes are allowed.. Error Severity  Level=16

Error Type =1942, Error Description Index cannot be created on view '%.*ls' because the view contains text, ntext or image columns.. Error Severity  Level=16

Error Type =1943, Error Description Index cannot be created on view '%.*ls' because the view contains text, ntext or image columns.. Error Severity  Level=16

Error Type =1944, Error Description Index '%.*ls' was not created. This index has a key length of at least %d bytes. The maximum permissible key length is %d bytes.. Error Severity  Level=16

Error Type =1945, Error Description Warning! The maximum key length is %d bytes. The index '%.*ls' has maximum length of %d bytes. For some combination of large values, the insert/update operation will fail.. Error Severity  Level=16

Error Type =1946, Error Description Operation failed. The index entry of length %d bytes for the index '%.*ls' exceeds the maximum length of %d bytes.. Error Severity  Level=16

Error Type =1947, Error Description Index cannot be created on view '%.*ls' because the view contains a self-join on '%.*ls'.. Error Severity  Level=16

Error Type =1948, Error Description Duplicate index names '%.*ls' and '%.*ls' detected on table '%.*ls'.. Error Severity  Level=16

Error Type =1949, Error Description Index on view '%.*ls' cannot be created because function '%s' yields nondeterministic results.. Error Severity  Level=16

Error Type =1950, Error Description Index on view '%.*ls' cannot be created because the view contains an imprecise expression in a GROUP BY clause. Error Severity  Level=16

Error Type =1951, Error Description Index on view '%.*ls' cannot be created because the view contains an imprecise expression in the WHERE clause.. Error Severity  Level=16

Error Type =1952, Error Description Index on view '%.*ls' cannot be created because the view contains an imprecise expression in a join.. Error Severity  Level=16

Error Type =1953, Error Description Index on view '%.*ls' cannot be created because the view contains an imprecise expression in a join.. Error Severity  Level=16

Error Type =1954, Error Description Index on view '%.*ls' cannot be created because the view uses a column bound to a rule.. Error Severity  Level=16

Error Type =1955, Error Description Index on view '%.*ls' cannot be created because the view contains a nondeterministic computed column.. Error Severity  Level=16

Error Type =1956, Error Description Index on view '%.*ls' cannot be created because the view uses a nondeterministic user-defined function.. Error Severity  Level=16

Error Type =1957, Error Description Index on view '%.*ls' cannot be created because the view requires a conversion involving dates or variants.. Error Severity  Level=16

Error Type =1958, Error Description This edition of SQL Server does not support indexed views.. Error Severity  Level=16

Error Type =1959, Error Description Cannot create index on view or computed column because this database is not SQL Server compatible.. Error Severity  Level=16

MS SQL Server Error Types 3

Error Type =11501,/n Error Description The batch could not be analyzed because of compile errors.. Error Severity  Level=16

Error Type =11502, Error Description The type for parameter '%.*ls' cannot be deduced in this context.. Error Severity  Level=16

Error Type =11503, Error Description The parameter type cannot be deduced because a single expression contains two untyped parameters, '%.*ls' and '%.*ls'.. Error Severity  Level=16

Error Type =11504, Error Description The discovered type with user type id %d in database %d is invalid.. Error Severity  Level=16

Error Type =11505, Error Description The discovered type with the XML schema collection id %d in database %d is invalid.. Error Severity  Level=16

Error Type =11506, Error Description The parameter type for '%.*ls' cannot be uniquely deduced; two possibilities are '%ls' and '%ls'.. Error Severity  Level=16

Error Type =11507, Error Description The parameter type for '%.*ls' cannot be deduced because no type would make the query valid.. Error Severity  Level=16

Error Type =11508, Error Description The undeclared parameter '%.*ls' is used more than once in the batch being analyzed.. Error Severity  Level=16

Error Type =11509, Error Description The metadata could not be determined because the statement '%.*ls' is not compatible with the statement '%.*ls'.. Error Severity  Level=16

Error Type =11510, Error Description The metadata could not be determined because the statement '%.*ls' in the main batch is not compatible with the statement '%.*ls' in procedure '%.*ls'.. Error Severity  Level=16

Error Type =11511, Error Description The metadata could not be determined because the statement '%.*ls' in procedure '%.*ls' is not compatible with the statement '%.*ls' in the main batch.. Error Severity  Level=16

Error Type =11512, Error Description The metadata could not be determined because the statement '%.*ls' in procedure '%.*ls' is not compatible with the statement '%.*ls' in procedure '%.*ls'.. Error Severity  Level=16

Error Type =11513, Error Description The metadata could not be determined because statement '%.*ls' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.. Error Severity  Level=16

Error Type =11514, Error Description The metadata could not be determined because statement '%.*ls' in procedure '%.*ls' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.. Error Severity  Level=16

Error Type =11515, Error Description The metadata could not be determined because statement '%.*ls' invokes a CLR procedure. Consider using the WITH RESULT SETS clause to explicitly describe the result set.. Error Severity  Level=16

Error Type =11516, Error Description The metadata could not be determined because statement '%.*ls' in procedure '%.*ls' invokes a CLR procedure. Consider using the WITH RESULT SETS clause to explicitly describe the result set.. Error Severity  Level=16

Error Type =11517, Error Description The metadata could not be determined because statement '%.*ls' invokes a CLR trigger.. Error Severity  Level=16

Error Type =11518, Error Description The metadata could not be determined because statement '%.*ls' in procedure '%.*ls' invokes a CLR trigger.. Error Severity  Level=16

Error Type =11519, Error Description The metadata could not be determined because statement '%.*ls' invokes an extended stored procedure.. Error Severity  Level=16

Error Type =11520, Error Description The metadata could not be determined because statement '%.*ls' in procedure '%.*ls' invokes an extended stored procedure.. Error Severity  Level=16

Error Type =11521, Error Description The metadata could not be determined because statement '%.*ls' uses an undeclared parameter in a context that affects its metadata.. Error Severity  Level=16

Error Type =11522, Error Description The metadata could not be determined because statement '%.*ls' in procedure '%.*ls' uses an undeclared parameter in a context that affects its metadata.. Error Severity  Level=16

Error Type =11523, Error Description The metadata could not be determined because statement '%.*ls' causes indirect recursion.. Error Severity  Level=16

Error Type =11524, Error Description The metadata could not be determined because statement '%.*ls' in procedure '%.*ls' causes indirect recursion.. Error Severity  Level=16

Error Type =11525, Error Description The metadata could not be determined because statement '%.*ls' uses a temp table.. Error Severity  Level=16

Error Type =11526, Error Description The metadata could not be determined because statement '%.*ls' in procedure '%.*ls' uses a temp table.. Error Severity  Level=16

Error Type =11527, Error Description The metadata could not be determined because statement '%.*ls' does not support metadata discovery.. Error Severity  Level=16

Error Type =11528, Error Description The metadata could not be determined because statement '%.*ls' in procedure '%.*ls' does not support metadata discovery.. Error Severity  Level=16

Error Type =11529, Error Description The metadata could not be determined because every code path results in an error; see previous errors for some of these.. Error Severity  Level=16

Error Type =11530, Error Description The metadata could not be determined because there are no code paths that reach the end of the batch.. Error Severity  Level=16

Error Type =11531, Error Description The object id '%d' passed to sys.dm_exec_describe_first_result_set_for_object refers to an object which is not a T-SQL procedure or trigger.. Error Severity  Level=16

Error Type =11532, Error Description The object id '%d' passed to sys.dm_exec_describe_first_result_set_for_object was invalid.. Error Severity  Level=16

Error Type =11533, Error Description Type '%.*ls' is not a valid object name for result set definition.. Error Severity  Level=16

Error Type =11534, Error Description Type '%.*ls' is invalid or not a table type.. Error Severity  Level=16

Error Type =11535, Error Description EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), and the statement tried to send more result sets than this.. Error Severity  Level=16

Error Type =11536, Error Description EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), but the statement only sent %d result set(s) at run time.. Error Severity  Level=16

Error Type =11537, Error Description EXECUTE statement failed because its WITH RESULT SETS clause specified %d column(s) for result set number %d, but the statement sent %d column(s) at run time.. Error Severity  Level=16

Error Type =11538, Error Description EXECUTE statement failed because its WITH RESULT SETS clause specified type '%ls' for column #%d in result set #%d, and the corresponding type sent at run time was '%ls'; there is no conversion between the two types.. Error Severity  Level=16

Error Type =11539, Error Description One of the types specified in WITH RESULT SETS clause has been modified after the EXECUTE statement started running. Please rerun the statement.. Error Severity  Level=16

Error Type =11541, Error Description sp_describe_first_result_set cannot be invoked when SET STATISTICS XML or SET STATISTICS PROFILE is on.. Error Severity  Level=16

Error Type =11542, Error Description sp_describe_undeclared_parameters failed to discover parameter metadata for remote procedure call because parameters were passed by name and by name parameter metadata discovery failed.. Error Severity  Level=16

Error Type =11543, Error Description SET STATISTICS XML ON and SET STATISTICS PROFILE ON are not allowed inside any procedure or batch that executes through an EXECUTE statement that has a WITH RESULT SETS clause.. Error Severity  Level=16

Error Type =11544, Error Description Could not deduce type for parameter in position '%d' for remote call to module '%.*ls'.. Error Severity  Level=16

Error Type =11545, Error Description Remote result set discovery cannot be done inside SELECT INTO.. Error Severity  Level=16

Error Type =11546, Error Description sp_showmemo_xml cannot run when the STATISTICS XML, STATISTICS PROFILE or SHOWPLAN set option is on. Set these options to OFF and re-run sp_showmemo_xml.. Error Severity  Level=16

Error Type =11547, Error Description Only one statement can be used as input parameter to sp_showmemo_xml. Modify the input parameter and re-run the stored procedure.. Error Severity  Level=16

Error Type =11548, Error Description The stored procedure 'sp_showmemo_xml' is not available in this edition of SQL Server.. Error Severity  Level=16

Error Type =11549, Error Description Using stored procedures is not supported. Modify the input parameter and re-run sp_showmemo_xml.. Error Severity  Level=16

Error Type =11550, Error Description The metadata could not be determined because remote metadata discovery failed for statement '%.*ls'.. Error Severity  Level=16

Error Type =11551, Error Description The metadata could not be determined because remote metadata discovery failed for statement '%.*ls' in procedure '%.*ls'.. Error Severity  Level=16

Error Type =11552, Error Description The @browse_information_mode parameter expects a tinyint with values 0, 1 or 2.. Error Severity  Level=16

Error Type =11553, Error Description EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null.. Error Severity  Level=16

Error Type =, Error Description This message has been dropped because the user does not have permission to access the target database. Database ID: %d. Message origin: &quot;%ls&quot;.. Error Severity  Level=16

Error Type =Error, Error Description Description. Error Severity  Level=Severity

Error Type =11601, Error Description %ls statements with a '%.*ls' option are not allowed.. Error Severity  Level=15

Error Type =11602, Error Description %ls statements are not verified.. Error Severity  Level=10

Error Type =11603, Error Description %ls statements are not allowed at the top level.. Error Severity  Level=15

Error Type =11605, Error Description %S_MSG are not allowed at the top level.. Error Severity  Level=15

Error Type =11606, Error Description Specifying server name in '%.*ls' is not allowed.. Error Severity  Level=15

Error Type =11607, Error Description Specifying database name for '%.*ls' in a %ls statement is not allowed.. Error Severity  Level=15

Error Type =11608, Error Description Creating temporary stored procedures is not allowed.. Error Severity  Level=15

Error Type =11609, Error Description An internal error occurred while building the project. %ls. Error Severity  Level=16

Error Type =11610, Error Description There is not enough memory to build the project.. Error Severity  Level=16

Error Type =11611, Error Description Specifying schema elements in the CREATE SCHEMA statement is not supported.. Error Severity  Level=15

Error Type =11612, Error Description Multiple statements in a T-SQL batch are not allowed at the top level.. Error Severity  Level=15

Error Type =11613, Error Description Numbered stored procedures are not supported.. Error Severity  Level=15

Error Type =11614, Error Description %ls is not supported.. Error Severity  Level=15

Error Type =11617, Error Description An unknown error has occurred trying to load '%hs'.. Error Severity  Level=16

Error Type =11618, Error Description Combining column level permissions with other permissions is not allowed in the same GRANT/DENY/REVOKE statement.. Error Severity  Level=15

Error Type =11619, Error Description There is not enough stack available to compile the statment.. Error Severity  Level=16

Error Type =11620, Error Description REVOKE statements can only be used to revoke column level permissions.. Error Severity  Level=15

Error Type =11621, Error Description SQL Server started in Language Service mode.. Error Severity  Level=10

Error Type =11622, Error Description CREATE ASSEMBLY can only be created FROM a constant binary expression.. Error Severity  Level=16

Error Type =11623, Error Description ALTER TABLE statements can only be used to add a single constraint.. Error Severity  Level=15

Error Type =11624, Error Description ALTER ROLE statements can only be used to add a member to a role.. Error Severity  Level=15

Error Type =11625, Error Description %ls' is either corrupt, not readable or not accessible.. Error Severity  Level=16

Error Type =11651, Error Description %ls statements with a '%.*ls' option are not supported in a data-tier application.. Error Severity  Level=10

Error Type =11652, Error Description %ls statements are not supported at the top level in a data-tier application.. Error Severity  Level=10

Error Type =11653, Error Description Cannot execute as the user '%.*ls' because it does not exist.. Error Severity  Level=10

Error Type =11700, Error Description The increment for sequence object '<Sequence Name>' cannot be zero.. Error Severity  Level=16

Error Type =11701, Error Description The absolute value of the increment for sequence object '%.*ls' must be less than or equal to the difference between the minimum and maximum value of the sequence object.. Error Severity  Level=16

Error Type =11702, Error Description The sequence object '<Sequence Name>' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.. Error Severity  Level=16

Error Type =11703, Error Description The start value for sequence object '%.*ls' must be between the minimum and maximum value of the sequence object.. Error Severity  Level=16

Error Type =11704, Error Description The current value '%.*ls' for sequence object '%.*ls' must be between the minimum and maximum value of the sequence object.. Error Severity  Level=16

Error Type =11705, Error Description The minimum value for sequence object '<Sequence Name>' must be less than its maximum value.. Error Severity  Level=16

Error Type =11706, Error Description The cache size for sequence object '%.*ls' must be greater than 0.. Error Severity  Level=16

Error Type =11707, Error Description The cache size for sequence object '%.*ls' has been set to NO CACHE.. Error Severity  Level=10

Error Type =11708, Error Description An invalid value was specified for argument '<Argument Name>' for the given data type.. Error Severity  Level=16

Error Type =11709, Error Description The 'RESTART WITH' argument cannot be used in a CREATE SEQUENCE statement.. Error Severity  Level=15

Error Type =11710, Error Description Argument 'START WITH' cannot be used in an ALTER SEQUENCE statement.. Error Severity  Level=15

Error Type =11711, Error Description Argument 'AS' cannot be used in an ALTER SEQUENCE statement.. Error Severity  Level=15

Error Type =11712, Error Description Argument '%.*ls' can not be specified more than once.. Error Severity  Level=15

Error Type =11714, Error Description Invalid sequence name '%.*ls'.. Error Severity  Level=15

Error Type =11715, Error Description No properties specified for ALTER SEQUENCE.. Error Severity  Level=15

Error Type =11716, Error Description NEXT VALUE FOR function does not support the PARTITION BY clause.. Error Severity  Level=15

Error Type =11717, Error Description NEXT VALUE FOR function does not support the OVER clause in default constraints, UPDATE statements, or MERGE statements.. Error Severity  Level=15

Error Type =11718, Error Description NEXT VALUE FOR function does not support an empty OVER clause.. Error Severity  Level=15

"Error Type =11719, Error Description NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, or derived tables - Views

 NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, or derived tables - User-Defined Functions. Error Severity  Level=15"

Error Type =11720, Error Description NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.. Error Severity  Level=15

Error Type =11721, Error Description NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.. Error Severity  Level=15

Error Type =11722, Error Description NEXT VALUE FOR function is not allowed in the WHEN MATCHED clause, the WHEN NOT MATCHED clause, or the WHEN NOT MATCHED BY SOURCE clause of a merge statement.. Error Severity  Level=15

Error Type =11723, Error Description NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.. Error Severity  Level=15

Error Type =11724, Error Description An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.. Error Severity  Level=15

Error Type =11725, Error Description An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to an aggregate.. Error Severity  Level=15

Error Type =11726, Error Description Object '%.*ls' is not a sequence object.. Error Severity  Level=16

Error Type =11727, Error Description NEXT VALUE FOR functions for a given sequence object must have exactly the same OVER clause definition.. Error Severity  Level=16

Error Type =11728, Error Description The sequence object '%.*ls' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.. Error Severity  Level=16

Error Type =11729, Error Description The sequence object '%.*ls' cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.. Error Severity  Level=10

Error Type =11730, Error Description Database name cannot be specified for the sequence object in default constraints.. Error Severity  Level=16

Error Type =11731, Error Description A column that uses a sequence object in the default constraint must be present in the target columns list, if the same sequence object appears in a row constructor.. Error Severity  Level=16

Error Type =11732, Error Description The requested range for sequence object '%.*ls' exceeds the maximum or minimum limit. Retry with a smaller range.. Error Severity  Level=16

Error Type =11733, Error Description Parameter '%.*ls' must be a positive integer.. Error Severity  Level=16

Error Type =11734, Error Description NEXT VALUE FOR function is not allowed in the SELECT clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.. Error Severity  Level=16

Error Type =11735, Error Description The target table of the INSERT statement cannot have DEFAULT constraints using the NEXT VALUE FOR function when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.. Error Severity  Level=16

Error Type =11736, Error Description Only one instance of NEXT VALUE FOR function per sequence object is allowed in SET or SELECT with variable assignment.. Error Severity  Level=16

Error Type =11737, Error Description NEXT VALUE FOR function does not support the ROWS and RANGE clauses.. Error Severity  Level=15

Error Type =11738, Error Description The use of NEXT VALUE FOR function is not allowed in this context.. Error Severity  Level=16

Error Type =11739, Error Description NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.. Error Severity  Level=16

Error Type =11740, Error Description NEXT VALUE FOR function cannot be used in a default constraint if ROWCOUNT option has been set, or the query contains TOP or OFFSET.. Error Severity  Level=16

Error Type =11741, Error Description NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.. Error Severity  Level=16

Error Type =11742, Error Description NEXT VALUE FOR function can only be used with MERGE if it is defined within a default constraint on the target table for insert actions.. Error Severity  Level=16

Error Type =11800, Error Description RESTORE WITH SNAPSHOTRESTOREPHASE=2 for database '%ls' failed because an earlier RESTORE WITH SNAPSHOTRESTOREPHASE=1 may have failed as a result of a network error. Retry the restore operation through SQL Writer after addressing any network issues and mak. Error Severity  Level=16

Error Type =11901, Error Description Column '%.*ls.%.*ls' is a federated column, while referencing column '%.*ls.%.*ls' in foreign key '%.*ls' is not.. Error Severity  Level=16

Error Type =11902, Error Description Federation scheme key '%.*ls' is not a part of this federation.. Error Severity  Level=16

Good Bye Mail Format 2

 Hi,

With a heavy heart, I must convey to you, my goodbye. It was a difficult decision to make, but I had to do it, nonetheless.

Today is my last day of work at XYZ. It has been a wonderful 2.5 years here, working with all of you.

I have enjoyed my tenure here and I appreciate having had the opportunity to work with you. you all have provided me support and through your encouragement and guidance I have been able to excel at the projects offered to me.

Thanks again for everything. I wish you all the best.

ABC

Good Bye Mail Format

 Dear All,

Today my tenure of working in the ABC ends!

I’d be remiss if I didn’t send an email to express my heartfelt thanks.

Deciding to move on from the ABC family was not as easy as I thought it would be. Being part of such an amazing organization has been an enormous privilege for me.

I wanna say thank you to each and every person I’ve met during my work time here, 

You all have taught me many valuable lessons that I’ll take with me as I move on to my new professional journey.

I will always remember and treasure the time we have been allowed to share over the past and it has been my good fortune to collaborate closely with many of you.

I would like to thank all my mentors Mr. XYZ for supporting me during my tenure here.

Special thanks to LMN Sir for showing his trust in me during my time here.

Last but not least, kindly accept my apologies for any mistakes or hard feelings that may have happened during my time here.

I am reachable at my personal contact details as: dcdcdcdc@gmail.com || 783XXXX13, with the hope that our paths will surely cross again in the future.

SQL Error Type List with description

Error Type =1001,/n Error Description Line %d: Length or precision specification %d is invalid.. Error Severity  Level=16

Error Type =1002, Error Description Line %d: Specified scale %d is invalid.. Error Severity  Level=16

Error Type =1003, Error Description Line %d: %ls clause allowed only for %ls.. Error Severity  Level=15

Error Type =1004, Error Description Invalid column prefix '%.*ls': No table name specified. Error Severity  Level=16

Error Type =1005, Error Description Line %d: Invalid procedure number (%d). Must be between 1 and 32767.. Error Severity  Level=15

Error Type =1006, Error Description CREATE TRIGGER contains no statements.. Error Severity  Level=15

Error Type =1007, Error Description The %S_MSG '%.*ls' is out of the range for numeric representation (maximum precision 38).. Error Severity  Level=15

Error Type =1008, Error Description The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.. Error Severity  Level=15

Error Type =1010, Error Description Invalid escape character '%.*ls'.. Error Severity  Level=15

Error Type =1011, Error Description The correlation name '%.*ls' is specified multiple times in a FROM clause.. Error Severity  Level=15

Error Type =1012, Error Description The correlation name '%.*ls' has the same exposed name as table '%.*ls'.. Error Severity  Level=15

Error Type =1013, Error Description Tables or functions "Object Name" and "Object Name" have the same exposed names. Use correlation names to distinguish them.. Error Severity  Level=15

Error Type =1014, Error Description TOP clause contains an invalid value.. Error Severity  Level=15

Error Type =1015, Error Description An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.. Error Severity  Level=15

Error Type =1016, Error Description Outer join operators cannot be specified in a query containing joined tables.. Error Severity  Level=15

Error Type =1019, Error Description Invalid column list after object name in GRANT/REVOKE statement.. Error Severity  Level=15

Error Type =1020, Error Description Column list cannot be specified for object-level permissions.. Error Severity  Level=15

Error Type =1021, Error Description FIPS Warning: Line %d has the non-ANSI statement '%ls'.. Error Severity  Level=10

Error Type =1022, Error Description FIPS Warning: Line %d has the non-ANSI clause '%ls'.. Error Severity  Level=10

Error Type =1023, Error Description Invalid parameter %d specified for %ls.. Error Severity  Level=15

Error Type =1024, Error Description FIPS Warning: Line %d has the non-ANSI function '%ls'.. Error Severity  Level=10

Error Type =1025, Error Description FIPS Warning: The length of identifier '%.*ls' exceeds 18.. Error Severity  Level=10

Error Type =1027, Error Description Too many expressions are specified in the GROUP BY clause. The maximum number is %d when either CUBE or ROLLUP is specified.. Error Severity  Level=15

Error Type =1028, Error Description The CUBE and ROLLUP options are not allowed in a GROUP BY ALL clause.. Error Severity  Level=15

Error Type =1029, Error Description Browse mode is invalid for subqueries and derived tables.. Error Severity  Level=15

Error Type =1031, Error Description Percent values must be between 0 and 100.. Error Severity  Level=15

Error Type =1032, Error Description Cannot use the column prefix '%.*ls'. This must match the object in the UPDATE clause '%.*ls'.. Error Severity  Level=15

Error Type =1033, Error Description The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.. Error Severity  Level=15

Error Type =1035, Error Description Incorrect syntax near '%.*ls', expected '%.*ls'.. Error Severity  Level=15

Error Type =1036, Error Description File option %hs is required in this CREATE/ALTER DATABASE statement.. Error Severity  Level=15

Error Type =1037, Error Description The CASCADE, WITH GRANT or AS options cannot be specified with statement permissions.. Error Severity  Level=15

Error Type =1038, Error Description Cannot use empty object or column names. Use a single space if necessary.. Error Severity  Level=15

Error Type =1039, Error Description Option '%.*ls' is specified more than once.. Error Severity  Level=15

Error Type =1040, Error Description Mixing old and new syntax in CREATE/ALTER DATABASE statement is not allowed.. Error Severity  Level=15

Error Type =1041, Error Description Option %.*ls is not allowed for a LOG file.. Error Severity  Level=15

Error Type =1042, Error Description Conflicting %ls optimizer hints specified.. Error Severity  Level=15

Error Type =1043, Error Description %hs' is not yet implemented.. Error Severity  Level=15

Error Type =1044, Error Description Cannot use an existing function name to specify a stored procedure name.. Error Severity  Level=15

Error Type =1045, Error Description Aggregates are not allowed in this context. Only scalar expressions are allowed.. Error Severity  Level=15

Error Type =1046, Error Description Subqueries are not allowed in this context. Only scalar expressions are allowed.. Error Severity  Level=15

Error Type =1047, Error Description Conflicting locking hints specified.. Error Severity  Level=15

Error Type =1048, Error Description Conflicting cursor options %ls and %ls.. Error Severity  Level=15

Error Type =1049, Error Description Mixing old and new syntax to specify cursor options is not allowed.. Error Severity  Level=15

Error Type =1050, Error Description This syntax is only allowed within the stored procedure sp_executesql.. Error Severity  Level=15

Error Type =, Error Description The collation '%.*ls' of receiving variable is not equal to the collation '%.*ls' of column '%.*ls'.. Error Severity  Level=16

Error Type =1051, Error Description Cursor parameters in a stored procedure must be declared with OUTPUT and VARYING options, and they must be specified in the order CURSOR VARYING OUTPUT.. Error Severity  Level=15

Error Type =1052, Error Description Conflicting %ls options %ls and %ls.. Error Severity  Level=15

Error Type =1053, Error Description For DROP STATISTICS, you must give both the table and the column name in the form 'tablename.column'.. Error Severity  Level=15

Error Type =1054, Error Description Syntax '*' is not allowed in schema-bound objects.. Error Severity  Level=15

Error Type =1055, Error Description %.*ls' is an invalid name because it contains a NULL character.. Error Severity  Level=15

Error Type =1056, Error Description The maximum number of elements in the select list is %d and you have supplied %d.. Error Severity  Level=15

Error Type =1057, Error Description The IDENTITY function cannot be used with a SELECT INTO statement containing a UNION operator.. Error Severity  Level=15

Error Type =1058, Error Description Cannot specify both READ_ONLY and FOR READ ONLY on a cursor declaration.. Error Severity  Level=15

Error Type =1059, Error Description Cannot set or reset the %ls option within a procedure.. Error Severity  Level=15

Error Type =1060, Error Description The number of rows in the TOP clause must be an integer.. Error Severity  Level=15

Error Type =1061, Error Description The text/ntext/image constants are not yet implemented.. Error Severity  Level=16

Error Type =1062, Error Description The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.. Error Severity  Level=16

Error Type =1063, Error Description A filegroup cannot be added using ALTER DATABASE ADD FILE. Use ALTER DATABASE ADD FILEGROUP.. Error Severity  Level=16

Error Type =1064, Error Description A filegroup cannot be used with log files.. Error Severity  Level=16

Error Type =1065, Error Description The NOLOCK, READUNCOMMITTED, and READPAST lock hints are only allowed in a SELECT statement.. Error Severity  Level=15

Error Type =1066, Error Description Warning. Line %d: The option '%ls' is obsolete and has no effect.. Error Severity  Level=10

Error Type =1067, Error Description The SET SHOWPLAN statements must be the only statements in the batch.. Error Severity  Level=15

Error Type =1068, Error Description Only one list of index hints per table is allowed.. Error Severity  Level=16

Error Type =1069, Error Description Index hints are only allowed in a FROM clause.. Error Severity  Level=16

Error Type =1070, Error Description CREATE INDEX option '%.*ls' is no longer supported.. Error Severity  Level=15

Error Type =1071, Error Description Cannot specify a JOIN algorithm with a remote JOIN.. Error Severity  Level=16

Error Type =1072, Error Description A REMOTE hint can only be specified with an INNER JOIN clause.. Error Severity  Level=16

Error Type =1073, Error Description %.*ls' is not a recognized cursor option for cursor %.*ls.. Error Severity  Level=15

Error Type =1074, Error Description Creation of temporary functions is not allowed.. Error Severity  Level=15

Error Type =1075, Error Description RETURN statements in scalar valued functions must include an argument.. Error Severity  Level=15

Error Type =1076, Error Description Function '%s' requires at least %d argument(s).. Error Severity  Level=15

Error Type =1077, Error Description INSERT into an identity column not allowed on table variables.. Error Severity  Level=15

Error Type =1078, Error Description %.*ls %.*ls' is not a recognized option.. Error Severity  Level=15

Error Type =1079, Error Description A variable cannot be used to specify a search condition in a fulltext predicate when accessed through a cursor.. Error Severity  Level=15

Error Type =1101, Error Description Could not allocate new page for database '%.*ls'. There are no more pages available in filegroup %.*ls. Space can be created by dropping objects, adding additional files, or allowing file growth.. Error Severity  Level=17

Error Type =1102, Error Description IAM page %S_PGID for object ID %ld is incorrect. The %S_MSG ID on page is %ld; should be %ld. The entry in sysindexes may be incorrect or the IAM page may contain an error.. Error Severity  Level=22

Error Type =1103, Error Description Allocation page %S_PGID in database '%.*ls' has different segment ID than that of the object which is being allocated to. Run DBCC CHECKALLOC.. Error Severity  Level=21

Error Type =1105, Error Description Could not allocate space for object '%.*ls' in database '%.*ls' because the '%.*ls' filegroup is full.. Error Severity  Level=17

Error Type =1109, Error Description Could not read allocation page %S_PGID because either the object ID (%ld) is not correct, or the page ID (%S_PGID) is not correct.. Error Severity  Level=21

Error Type =1203, Error Description Process ID %d attempting to unlock unowned resource %.*ls.. Error Severity  Level=20

Error Type =1204, Error Description The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.. Error Severity  Level=19

Error Type =1205, Error Description Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.. Error Severity  Level=13

Error Type =1206, Error Description Transaction manager has canceled the distributed transaction.. Error Severity  Level=18

Error Type =1211, Error Description Process ID %d was chosen as the deadlock victim with P_BACKOUT bit set.. Error Severity  Level=13

Error Type =1220, Error Description No more lock classes available from transaction.. Error Severity  Level=17

Error Type =1222, Error Description Lock request time out period exceeded.. Error Severity  Level=13

Error Type =1223, Error Description Attempting to release application lock '%.*ls' that is not currently held.. Error Severity  Level=16

SQl Server Query to delete the All Object execpt the specific schema

 USE Temp_data

DECLARE @Procedure_name NVARCHAR(MAX)

DECLARE @QUERY NVARCHAR(MAX)

DECLARE @Table_name NVARCHAR(MAX)

SELECT  CONCAT(a.name,'.',b.name)  as proc_name into #Temp_proc_name FROM SYS.schemas  A

INNER JOIN SYS.procedures B ON A.schema_id = B.schema_id

WHERE A.name != 'dbo'

WHILE EXISTS ( SELECT 1 FROM #Temp_proc_name )

BEGIN

SET @Procedure_name =  (SELECT top 1 proc_name from #Temp_proc_name order by proc_name desc)

SET @QUERY = N'DROP PROCEDURE ' + @Procedure_name;

EXECUTE sp_executesql @QUERY

DELETE FROM #Temp_proc_name WHERE proc_name = @Procedure_name

END

SELECT  CONCAT(a.name,'.',b.name)  as table_name into #Temp_table_name FROM SYS.schemas  A

INNER JOIN SYS.tables B ON A.schema_id = B.schema_id

WHERE A.name != 'dbo'

WHILE EXISTS ( SELECT 1 FROM #Temp_table_name )

BEGIN

SET @Table_name =  (SELECT top 1 table_name from #Temp_table_name order by table_name desc)

SET @QUERY = N'DROP TABLE ' + @Table_name;

EXECUTE sp_executesql @QUERY

DELETE FROM #Temp_table_name WHERE table_name = @Table_name

END

DROP TABLE #Temp_table_name

DROP TABLE #Temp_proc_name

INDEXED VIEW IN SQL SERVER and Materialized Views in Oracle

 INDEXED VIEW IN SQL SERVER

 

Indexed View is similar to Materialized Views. Indexed Views used in SQL server and Materialized Views used in Oracle.

Indexed Views

The Result Set is stored in the Database. Query Performance can be dramatically enhanced using Indexed Views. Create an Indexed View by implementing a UNIQUE CLUSTERED index on the view. The results of the view are stored in the leaf-level pages of the clustered index.

An Iindexed View automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

Guidelines for Creating Indexed Views

The SQL Server Query Optimizer automatically determines whether a given query will benefit from using an Index View.

Create Indexed Views when:

1. The performance gain of improved speed in retrieving results outweighs the increased maintenance cost.

2. The underlying data is infrequently updated.

3. Queries perform a significant number of joins and aggregations that either process many rows or are performed frequently by many users.

Restrictions on Creating Indexed Views

Consider the following guidelines:

1. The first index that you create on the view must be a UNIQUE CLUSTRERD index

2. You must create the view with the SCHEMABINDING option.

3. The view can reference base tables, but it cannot reference other views.

4. You must use two-part names to reference tables.

For example: -

 

CREATE VIEW VW_EXAMPLE

WITH SCHEMABINDING

AS

SELECT SUM(UnitPrice * Quantity * (1.00-Discount)) As Rev,

OrderDate, ProductID, COUNT_BIG() AS COUNT

FROM dbo.[Order_details] od

INNER JOIN dbo.Orders O

WHERE od.OrderID = O.OrderID

GROUP BY OrderDate, ProductID

----- Create UNIQUE CLUSTERED index on the view

CREATE UNIQUE CLUSTERED INDEX INDEX_VW_EXAMPLE
  ON VW_EXAMPLE (OrderDate, ProductID)

WITH SCHEMABINDING

We bind the object to the schema of all the underlying tables and views. This means that the underlying tables and views cannot be modified in a way that would affect the definition of the schema-bound object.

NOTE:

 

For schema binding, SQL server requires a more specific and safer SELECT statement. There are a couple of restrictions, which we must remember:

1. We must use two-part names, when referencing tables, views or functions (SchemaName.ObjectName).

2. We cannot use SELECT *, so we must choose specific column names. Otherwise, we will get the following error:

For example:

CREATE VIEW dbo.vwName

WITH SCHEMABINDING

AS

SELECT * FROM dbo.Employee

 

It shows you below error: -

Msg 1054, Level 15, State 6, Procedure vwName, Line x

Syntax ‘*’ is not allowed in schema-bound objects.

 

Materialized Views

Materialized view in SQL is also a logical structure which is stored physically on the disc. Like a view in Materialized views in SQL we are using simple select statement to create it. You should have created materialized views privileges to create a Materialized view. Definition of Materialized views (called as MV) has been stored in databases.  

 

 

Materialized views in SQL Syntax and Examples:

“Materialized views are also known as snapshots.”

Snapshots acts like a physical table because data from snapshots are storing in to physical memory. Snapshot retrieves data very fast. So, for performance tuning Snapshots are used. Following is the syntax of materialized view:

Create materialized view View_Name

 

Build [Immediate/Deffered]

 

Refresh [Fast/Complete/Force]

 

on [Commit/Demand]

 

as Select ..........;

 

Using above syntax, you can create materialized views. The Syntax includes some different optional fields:

1.Build Immediate: Means materialized views(mv) created immediately.

2.Build Deferred: Means materialized views(mv) created after one refresh.

3.Refresh on commit:

This option committed the data in materialized views in SQL immediately after data inserted and committed in table. This option is known as incremental refresh option. View is not fully refreshed with this option

4.Refresh on Demand:

Using this option, you can add the condition for refreshing data in materialized views.