![]() You must explicitly enable page checksums on upgraded databases using ALTER DATABASE blah SET PAGE_VERIFY CHECKSUM. Myth #17: variety of myths around page checksums.ġ7a) page checksums are enabled automatically when you upgrade from SQL Server 2000 or 7.0 I described page checksums in depth in the blog post How to tell if the IO subsystem is causing corruptions? ![]() However, if the key values are long, a regular index is not likely to perform as well as a checksum index.(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)Ī few people have suggested some of the myths around page checksums so today is another multi-mythbusting extravaganza! Well, I get excited at least :-) Alternatively, an index could be built directly on the column indexed. WHERE checksum(N'Vegie-spread') = cs_PnameĬreating the index on the computed column materializes the checksum column, and any changes to the ProductName value will be propagated to the checksum column. Add a second searchĬondition to catch stray cases where checksums match, The checksum index can be used for equality searches. The checksum index can be used as a hash index, particularly to improve indexing speed when the column to be indexed is a long character column. Create a checksum index.ĬREATE INDEX Pname_index ON Products (cs_Pname) The hash index is built by adding a computed checksum column to the table being indexed, then building an index on the checksum column. The CHECKSUM function may be used to build hash indices. Examples Using CHECKSUM to build hash indices CHECKSUM values should not be compared against BINARY_CHECKSUM values. In contrast, in a case-insensitive server, CHECKSUM returns the same checksum values for those strings. For example, the BINARY_CHECKSUM values for the strings "McCavity" and "Mccavity" are different. The string data types are char, varchar, nchar, nvarchar, or sql_variant (if its base type is a string data type). The order of columns used in the case of CHECKSUM(*) is the order of columns specified in the table or view definition, including computed columns.ĬHECKSUM and BINARY_CHECKSUM return different values for the string data types, where locale can cause strings with different representation to compare equal. However, there is a small chance that the checksum will not change.īINARY_CHECKSUM and CHECKSUM are similar functions: they can be used to compute a checksum value on a list of expressions, and the order of expressions affects the resultant value. If one of the values in the expression list changes, the checksum of the list also usually changes. For the purpose of this definition, NULL values of a given type are considered to compare as equal. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index, which can be used for equality searches over the columns.ĬHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. ![]() The hash value is intended for use in building hash indices. Return TypesĬHECKSUM computes a hash value, called the checksum, over its list of arguments. Is an expression of any type except a noncomparable data type. Noncomparable data types are text, ntext, image, and cursor, as well as sql_variant with any of the above types as its base type. CHECKSUM returns an error if any column is of noncomparable data type. Specifies that computation is over all the columns of the table. SyntaxĬHECKSUM ( * | expression ) Arguments CHECKSUM is intended for use in building hash indices. Returns the checksum value computed over a row of a table, or over a list of expressions.
0 Comments
Leave a Reply. |