June 23, 2012 at 2:56 pm
Intent locks improve performance by putting a lock that could be escalated on a table or row. How does it improve performance?
Does a database typically have as many primary keys as there are tables?
What is the benefit of user-defined data types? I don't see how a user-defined data type would be any different from the data type that is used in its declaration (e.g., CREATE TYPE schema.NewType FROM varchar(15) --this would be the same as varchar(15) ).
Can a schema span multiple databases?
June 23, 2012 at 3:19 pm
Ted_Kert (6/23/2012)
Intent locks improve performance by putting a lock that could be escalated on a table or row. How does it improve performance?
They increase the chance that it will be able to get the escalated lock when it needs it (or much faster if not right away) by blocking other tasks from locking the object exclusively after it gets the intent lock.
Ted_Kert (6/23/2012)
Does a database typically have as many primary keys as there are tables?
Opinions vary, but best practice is to always have a primary key unless you come up with a compelling reason not to. This BP is not followed as frequently as it should be.
Ted_Kert (6/23/2012)
What is the benefit of user-defined data types? I don't see how a user-defined data type would be any different from the data type that is used in its declaration (e.g., CREATE TYPE schema.NewType FROM varchar(15) --this would be the same as varchar(15) ).
It's for simplicity and to make it easier if you ever need to change the data type of commonly used values. For example, SQL has a built-in user defined data type called "sysname" which maps to nvarchar(128). It's simpler to just use sysname for any system objects. Plus If I create a user-defined data type to use in lots of places, and I need to update the definition, then in the code, I only need to update the script that creates the user-defined data type rather than dozens of scripts to create tables.
Ted_Kert (6/23/2012)
Can a schema span multiple databases?
No, schemas are contained within a database. Even if the schemas are named the same in different databases, they are not the same.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply