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

The Very Basic Syntax of Razor

If you have worked in ASP.NET MVC, you know that the default view engine (or templating engine) is Razor, much like the .aspx/.ascx/.master file templates in ASP.NET Web Forms.  One thing I like about Razor is that you can use C# or VB.NET as the programming language to code in Razor.  All you need to do is learn how to use the Razor syntax.

So here I present to you the very basic syntax you need to know about Razor.  In the code below I am using C#, my preferred language.  You can view the generated HTML page here.

<div>
    
<h1>Razor Demo Using C#</h1>
    
@*  
   Razor comments can be one line or multiple lines.
   Unlike HTML comments, Razor comments are not rendered to the page.
*@
    
@{
   @* You can add Razor comments inside the Razor code block such as this. *@
}
    
@{
   // But why use Razor comments when you can use C# comments.
   /*
    * C# multi-line comment
    */
}
    
@*  
   Razor code starts with @ character.
   It can be a single statement block, an inline expression,
    or a multi-statement block.
   Once you start your code with @, all of the .NET framework,
    ASP.NET, and all the C# features are available to you.
*@
    
@* An example of a Razor single statement block: *@
@{ var myGreeting = "<Hello, World!>"; } 
    
@* 
   Below is an example of a Razor inline expression.
   Note that the output from server code is automatically HTML-encoded.
    < and > characters in the variable myGreeting will automatically
    be encoded to &lt; and &gt; so it can be displayed properly
    in the browser.  You can check the page source to verify this.
*@
<p>The value of myGreeting is: @myGreeting</p>
    
@* 
   A Razor inline expression can be multi-token if enclosed in ()
    as in example below.
   Note that () can also be used to explicitly declare a Razor
    inline expression.
*@
<p>@("The value of myGreeting is: " + myGreeting)</p>
    
@* Here is an example of a Razor multi-statement block *@
@{
    var myGreeting2 = "Hello, Universe!";
    var myDate = DateTime.Today.ToString("MMMM dd, yyyy");   
}
    
<p>@myGreeting2 Today is @myDate.</p>
    
@*
    You will notice in the previous examples of Razor code blocks, 
     be they single or multi-statement blocks, are enclosed in {}.
     They don't have to be always enclosed in {}.
     Take for example the if statement below.  
     Since it is essentially a single statement, 
     it can follow the @ character immediately.
     The same goes with for, foreach, switch, etc.
*@
    
@{ var myMsg = ""; }
    
@if (IsPost) 
{
    myMsg = "This is a postback!";
}
else
{
    myMsg = "This is not a postback.";
}
 
<p>@myMsg<p>
    
@*
    You can also mix text and markup in the code block.
*@
    
@if (true)
{
    // Mixing markup in code is as easy as putting in the matching HTML tags.
    <p>The value of IsPost is: @IsPost</p>
    
    // You can use @: or <text> to render plain text.
    // If you check the page source, these plain texts are not enclosed in
    //  any HTML tags.
    @:This is plain text.
    <br />
    <text>Another plain text.</text>
    <br />
    <br />
}
    
</div>

Additional Resources: