SQL Server: Schemas, DELETE vs. TRUNCATE, Generate INSERTs, and NOT IN vs. NOT EXISTS

Schemas

Data Organization Using Schemas best explains with clear examples what schema in SQL Server is.  Important things to note are:

  • Schemas were first introduced in SQL Server 2005.
  • dbo in pre SQL Server 2005 was used as the default database owner but now it is used as the default schema.
  • Schema is analogous to namespace (as in namespace in C#), or a container used to store database objects.
  • Schema owner can be a Windows domain login, Windows local login, SQL Server login, Windows group, database role, server role, or application role.
  • Schemas are simpler to manage in terms of permissions and security.
  • Lastly, schemas can provide logical boundaries without the need to create multiple physical databases.
  • Additional readings:

 

DELETE vs. TRUNCATE

Sometimes deleting so many rows in a SQL Server table takes up a loooong time.  If rolling back data does not matter, use TRUNCATE instead.

TRUNCATE TABLE dbo.MyTable

 

Generate INSERTs

I used to use a script I got from the Internet that generates INSERT statements from a table so I can repopulate the table next time with same data, or populate a similar table in another database for example.  Recently I found another alternative in SQL Server Management Studio with the Generate Scripts… database task.  Just right-click on the database where the table you want to generate INSERTs from is, go to Tasks, and click Generate Scripts….  Go over the steps and make sure on the scripting options, by clicking the Advanced button, that you select Data only for the Types of data to script under the General options.  This will generate the INSERT statements.

 

NOT IN vs. NOT EXISTS

SELECT ProductID
FROM Products
WHERE ProductID NOT IN
(
	SELECT ProductID
	FROM OrderDetails
)

-- versus

SELECT ProductID
FROM Products p
WHERE NOT EXISTS
(
   	SELECT 1
	FROM OrderDetails od
	WHERE od.ProductID = p.ProductID
)

 

I am not talking about the performance difference between these two SQL operations but rather the functional difference between them.  It might be tempting to say they are the same but it is not.  NOT IN will not behave as one expects when the column used for comparison in the subquery contains NULL values (OrderDetails.ProductID in the example above).  The article NOT EXISTS vs NOT IN explains why.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s