General Questions:
Q: If you want to know the value of the key prior to your insert statement, how can you use identity?
You could insert a “place-holder” row (i.e. a row that uses only defaults and/or just basic information so that you can get the @@identity of the row) and then come back later to update the data values. However, be very cautious with this approach. If you insert a lot of NULL values and have a very narrow row inserted then when you come back to update it (and therefore widen the variable width columns, if any) you can end up creating a lot of fragmentation. To minimize this, make sure to use default values (i.e. DEFAULT constraints) to pre-allocate space and reduce fragmentation.
Q: Can you give me a brief definition of a doubly-linked list?
Speaking directly to the “leaf-level” of an index the doubly linked list refers to the pages that contain the index data – in indexed order. Since there’s likely to be more than one row per page and since an index implies order; the pages are “linked” to provide that order logically.
The NIST (National Institute of Standards) also has a series of “data structures” definitions and doubly-linked list is here.
And for a bit more about SQL internals – SQL Server 2000 (actually 7.0 and higher) uses 8K pages. Each page has a 96 byte header – [a very small] part of which is used to store this previous-page and next-page page pointers. As for how many rows SQL Server will store; that depends on the width of the row. You can divide 8096 by your average row size to get an idea of how many rows you will be storing and you can use DBCC SHOWCONTIG (‘tablename’) WITH TABLERESULTS to see information columns labeled MinimumRecordSize, MaximumRecordSize, and AverageRecordSize for more specific table-related values. There is a maximum limit of 8060 bytes for a single inserted row.
Q: Well, this is a live meeting question and I should have reminded you all! But – for future reference… How do I see the demo in full-screen mode?
Press Control + H when the webcast begins a demo.
Q: Could you please give an example of a foreign key constraint?
A foreign key constraint is used to enforce referential integrity between two columns of the same or different tables. For example, you might create two tables: Employees and Departments. Because each employee can only be in one department, you can enforce this relationship with keys. First, make the DepartmentID (of the Departments table) a Primary Key and then make the DepartmentID column of the Employee Table reference Departments. Here’s a small snippet of pseudo code.
There are lots of good references out there on database design and constraints and I’ve heard good things about Database Design for Mere Mortals by Mike Hernandez. And, here’s an article on MSDN titled: Implementing Referential Integrity and Cascading Actions by Itzik Ben-Gan.
Q: Why does SQL Server allocate up the all of the memory? Even if I stop the service in short time it’s the same?
Since SQL Server is typically run as a dedicated service in production environments, SQL Server typically allocates memory to have it available when it needs the memory. If it detects memory pressure SQL Server will release those pages. If you’d like to configure memory for a specific max server memory and/or min server memory, you can check out the books online as well as this MSDN article titled: Inside SQL Server 2000’s Memory Management Facilities by