Concepts Flashcards
A table has three clustered indexes. The same column is there in all the three indexes. How many times does sql server store the column values?
SQL Server would store that data four times—once in a
clustered index or heap and in each of the three nonclustered indexes
What is sql server table or index partition?
Every
partition, in a nutshell, is an internal data structure (index or heap) independent from
other partitions in the object. SQL Server allows the use of a different partition strategy
for every index in the table; however, in most cases, all indexes are partitioned in the
same way and aligned with each other.
Is every table/index in sql server partitioned?
True.
Every table/index in SQL Server is partitioned. Non-partitioned tables are
treated as single-partition tables/indexes internally.
What is the size of a data page in sql server and how many bytes are available to a user?
the actual data is stored in data rows on 8KB data pages
with 8,060 bytes available to users.
The pages that store users’ data may belong
to which three different categories / allocation units based on the type of data they store.
The three categories or allocation units are
1) IN_ROW_DATA
2) ROW_OVERFLOW_DATA and
3) LOB_DATA
Which DMF is used to get index fragmentation details?
The sys.dm_db_index_physical_stats data management function is usually used
to analyze index fragmentation. It also displays the information about data pages on a
per–allocation unit basis
What is IN_ROW_DATA allocation unit in sql server?
IN_ROW_DATA allocation unit pages store the main data row objects, which consist
of internal attributes and the data from fixed-length columns, such as int, datetime,
float, and others. The in-row part of a data row must fit on a single data page and,
therefore, cannot exceed 8,060 bytes. The data from variable-length columns, such as
(n)varchar(max), (n)varbinary(max), xml, and others, may also be stored in-row in the
main row object when it fits into this limit.
What does LOB in LOB_DATA allocation unit stands for?
LOB stands for LARGE OBJECTS
What data is stored in LOB_DATA allocation unit?
Variable-
length data that exceeds 8,000 bytes is stored on LOB_DATA allocation unit data pages
(LOB stands for large objects). Otherwise, the data is stored in ROW_OVERFLOW_DATA
allocation unit pages.
What are pages and extents in sql server?
The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n.
Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.
Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents
Are there pages in a sql server transaction log file?
Log files do not contain pages; they contain a series of log records.
How are data rows inserted in a sql server page? Serially or randomly?
Data rows are put on the page serially, starting immediately after the header.
What is a row offset table?
A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.
What are extents in sql server?
Extents are the basic unit in which space is managed. An extent is eight physically contiguous
pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
SQL Server has two types of extents:
Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.
Can uniform extents be shared by multiple objects?
No. Uniform extents have pages from the same object.
