SQL Server (user) instances and AutoAttach DB

Developers often need to attach databases on demand and then start using them immediately. However before developers can start working with DB's they need to first attach the DB, then create a SQL login and then add that login to the DB users list. with SQL Express this is no longer a requirement.

With SQL Express we can use Auto Attach DB's and User instances.
SQL 2005 Express and Full editions both support autoAttachment of DB's, however User instancing is only supported with SQL Express.

To enable AutoAttachment of DB's use : AttachDbFilename=db.mdf in connection string.
When you use "AttachDbFilename=|DataDirectory|db.mdf" the connection string automatically looks for the DB in the AppData folder of the Web Application.

However there is a limitation that if the user is not a administrator, he would still not be able to AutoAttach the DB. we can solve this by using "User instances".
User instance is another SQL server instance and runs under the credentials of the currently logged in user, thus no need to give the user administrative privileges.
==================================================
User Instances is a feature that makes SQL Server 2005 Express different from other SQL Server editions. Before I explain User Instances, you need to understand that a SQL Server instance is essentially an in-memory occurrence of the sqlservr.exe executable program. Different SQL Server editions support different numbers of instances. For example, the SQL Server 2005 Enterprise Edition supports 50 instances, and the SQL Server 2005 Standard, Workgroup, and Express editions each support 16 instances. Each instance runs separately and has its own set of databases that aren't shared by any other instance. Client applications connect to each instance by using the instance name.

Typically, the first SQL Server instance you install becomes the "default" instance. The default instance uses the name of the computer on which it's installed. You can assign a name to subsequent instance installations, so they're called "named" instances. During the installation process, you can assign any name to a named instance. Client applications that want to connect to an instance use the \ convention. For example, if the default instance name is SQLServer1 and the instance name is MyInstance, the client application would connect to the named instance by using the server name SQLServer1\MyInstance.

As with the other SQL Server editions, SQL Server Express supports the default instance and named instances, but SQL Server Express uses SQLExpress as the default instance name rather than the name of the computer system.

In addition to regular SQL Server instances, SQL Server Express also supports User Instances. User instances are similar to named instances, but SQL Server Express creates user instances dynamically, and these instances have different limitations. When you install SQL Server Express, you have the option of enabling User Instances. By default, User Instances aren't enabled. After installation, you can enter the sp_configure command in SQL Server Management Studio Express (SSMSE) or the sqlcmd tool by using the following syntax:

sp_configure 'user instances enabled','1'

To disable User Instance support, replace 1 with a 0 in the sp_configure command.

User Instances were designed to make deploying databases along with applications easier. User Instances let users create a database instance on demand even if they don't have administrative rights. To utilize User Instances, the application's connection string needs to use the attachdbfilename and user instance keywords as follows:

Data Source=.\SQLExpress;integrated security=true;
attachdbfilename=\MyDatabase.mdf;user instance=true;"

When an application opens a connection to a SQL Server Express database in which User Instances are enabled and the application uses the attachdbfilename and user instance keywords, SQL Server Express copies the master and msdb databases to the user's directory. SQL Server Express starts a new instance of the sqlserver.exe program and SQL Server Express attaches the database named in the attachdbfilename keyword to the new instance.

Unlike common SQL Server instances, SQL Server Express User Instances have some limitations. User Instances don't allow network connections, only local connections. As you might expect with the network-access restriction, User Instances don't support replication or distributed queries to remote databases. In addition, Windows integrated authentication is required. For more information about SQL Server Express and User Instances you can read the Microsoft article "SQL Server 2005 Express Edition User Instances" at
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp

SQL Tips

Difference between truncate and delete

• delete operations are logged and thus can be restored using transaction log backups (if not using Simple recovery model - the default)
• truncate operations are not logged, thus cannot be rolled back.
• delete can be used with WHERE clause to selectively delete rows
• truncate cannot selectively delete rows.
• truncate reseeds the identity column , delete does not reseed
• truncate cannot be used on tables with foreign key constraints
• since truncate is not logged, it does not activate triggers, delete does activate triggers
  

=====================================================
Difference between temp table and table variables:

• table variables are not logged, i.e. they don't use transaction logs, thus cannot participate in transactions
• indexes cannot be created on table variables
• foreign key constraints cannot be created either on table variables or temp tables
• table variables cannot be nested, i.e. they cannot be reused within the nested subquery, whereas temp tables can be accessed from within nested subqueries
• SP with table variables require fewer recompilation than SP with temp tables.
• table variables dont make use of parallelism since they reside in memory, temp table do make use of multiple processors since they reside in tempdb.
• table variables used for small amounts of data since they reside in memory.
  

=====================================================
Types of Data Integrity
Entity Integrity: Primary Key and Unique key constraints are used to maintain entity integrity
Domain Integrity : Check and Not NULL constraints are used to maintain domain integrity
Referential integrity : Foreign key constraints are used to maintain referential integrity
=====================================================
Local Temp tables & variables : exist for the duration of session, thus not shared by different client sessions. If Dynamic sql creates the local temp table then it falls out of scope as soon as it exits the EXEC statement used to execute the dynamic SQL.
Global Temp tables & variables : shared by all client sessions and are destroyed when the last client session exits
===========================================================
Types of UDFs:
Scalar valued udf : returns a single value, can be used anywhere an expression is used, just like subqueries.
table valued udf : returns a table.
2 types of table valued udf's :

• inline table valued udf : uses a single statement to return a table
• multi-statement table valued udf : multiple statements are issued in the udf before returning the table

UDF's can be used within select/having/where clauses and table valued udf's return rowsets/table and can be used in joins.
Cannot use temp tables in UDF's, only table variables.
UDFs are analogous to views that accept input parameters
================================================
Indexed Views are views that use disk space to store data.
Created with SCHEMABINDING option
have restrictions on the columns that the Indexed view can contain
have restrictions on the columns that can be used within the index created for the view
represent views that have a unique clustered index
Must have ansi_nulls, quoted_identifier, arithabort etc session options set to ON
If using standard edition than cannot be used implicitly by queries, must use the NOEXPAND optimizer HINT or reference with view with the VIEW name.
If using Group by then count_big(*) must be used
Must not be used in OLTP environments, suitable for less frequently updated base tables.

============================================================
ANSI_NULLS : default OFF, when ON then returns 0 rows when col = null or col <> null used even though the column has both null and not null values.
Thus we need to insure that we use the col is null, col is not null syntax for comparisons

ANSI_WARNINGS : default OFF: when ON then shows warnings when one of the following situations arises:
sum, max, min, avg used against a column that contains nulls
Divide by Zero exception occurs
Overflow exception occurs (string may be truncated)

QUOTED_IDENTIFIER : default OFF : when ON then allows object identifiers to use double quotes for distinguishing from reserved words and allows string literals to use single quotes

Indexed views need all the above sessions options to be set to ON

Concat_NULL_YIELDS NULL : default OFF

ANSI_DEFAULTS : default OFF: When ON then sets the above and the following session options to ON:
ANSI_PADDING

aspnet_regsql with SQLExpress database

Creating a new DB in VisualStudio.NET 2005 is as simple as "Select APP_DATA node -> Add New Item -> Sql Database" and wah-lah you have a new aspnet.mdf file located in your APP_DATA folder. However, when you run the tool ASPNET_REGSQL.exe in Wizard Mode (E.g. using the "-W" switch) there is no way to specify a SQLEXPRESS attached database - it only seems to support SQL Server 2005 (and earlier) database servers.

So, after several attempts, I finally figured-out the "right" way to do this:

aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\MyProject\APP_DATA\aspnetdb.mdf"

This will connect to the local SQLEXPRESS engine and attach the MDF file passed in the "-d" switch then create the appropriate objects in the DB.
================================================================

aspnet_regsql.exe -S server -d database -E -A all

While this concept still applies for SQL Server 2005 Express Edition, it can be a little harder to get the server and database names right. What database server is SQL Server Express installed on? And what's the database name for a .MDF file in the App_Data folder?

Assuming you are working on an ASP.NET application locally, the server name will be: localhost\SQLExpress

The database name is (and here's it can get a bit tricky), is the path to the MDF file when it was created. So, say that you have an ASP.NET application created in the classroom lab at C:\Labs\Website\App_Data\MessageBoard.mdf. The name of the database is C:\Labs\Website\App_Data\MessageBoard.mdf, meaning you could install the membership services from the command-line using:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Labs\Website\App_Data\MessageBoard.mdf” -E -A all

Now, imagine that you zip up your files onto a USB keychain drive, go home, and copy your project files to C:\Home\Website. Now, if you wanted to create the services, you'd think you'd just type in:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Home\Website\App_Data\MessageBoard.mdf” -E -A all

Ah, but the database name is C:\Labs\Website\App_Data\MessageBoard.mdf. Eep. So when you run the above command the database can't be found and cryptic error messages abound. Essentially, it can't find the database C:\Home\Website\App_Data\MessageBoard.mdf so it tries to create a database file in the default directory (%PROGRAM FILES%\Microsoft SQL Server\MSSQL.1\DATA) with the filename C:\Home\Website\App_Data\MessageBoard.mdf. This, of course, causes problems since that's not a valid filename. Ick.

So how do we fix this? There are a couple optios. The easiest is probably to download the (free) SQL Server 2005 Management Studio Express program and attach the database file. Then, from the Properties pane you can see the database name. You can then use this with aspnet_regsql.exe. (You could also rename the database at this point...)

If you want to be 3l33t you can use sqlcmd, attach the database (sp_attach_db) giving it a friendly name, which you can then use to run the aspnet_regsql.exe command line program against. Something like:

sqlcmd -S localhost\SQLExpress -Q “EXEC sp_attach_db 'Foobar', N'pathToDBfile'”

And then:

aspnet_regsql.exe -S localhost\SQLExpress -d Foobar -E -A all