NotesMSSQL - Notes on Microsoft SQL Server

NotesMSSQL
===========

General / FAQ
Starting the server
Login to server
Stopping the server
Converting CSV to MSSQL
Visual Studio Database Project
Views and Stored Procedures
Transact SQL
Transact SQL Data Types vs DB Datatypes
Transact SQL functions
Checking if table, stored procedures, synonyms, etc EXISTS
sys.object constants
Configuring Permissions
Deleting tables, DB, etc
Timing
Bulk Insert Selected Column
How to use Merge in SQLserver 2008
Installing SQL Server 2008 with Visual Studio 2008
How to use SQL Server 2008 Projects in Visual Studio 2008
Link to other servers
Using SQL Server Import and Export Wizard (SSIS)
Backup and Restore Database with MS SQL Server Management Studio
Difference Between nVarChar vs VarChar and nChar vs Char
Unicode
Using Variables / Parameters in Stored Procedures
Dynamic SQL
Partitioned Tables
Using SQL Server Management Studio
SQL Server Indexes



General / FAQ
===============
Where is the actual data stored?
    :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Reference in MSDN:
T-Sql reference
    Enterprise Servers and Development - SQL Server - SQL Server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Language Reference
    MSDN Library - Servers and Enterprise Development - SQL Server - SQL Server 2008 - Product Documentation - SQL Server 2008 Book Online - Database Engine - Technical Reference - Transact-SQL Reference
Sql Server Project
    Development Tools and Languages - Visual Studio 2005 - Visual Studio - .Net Framework Programming in Visual ... - Accessing Data - Creating SQL Server 2005 Objects in ...
Run a Data Generation Plan to generate data
http://msdn.microsoft.com/en-us/library/dd193262.aspx
    Development Tools and Languages - Visual Studio 2005 - Visual Studio Team System - Team Edition for Database Professionals - Generating Data with Data Generator - Data Generation Plans
    Development Tools and Languages - Visual Studio 2008 - Visual Studio Team System - Database Edition - Managing Changes to Datatabse and D.. - Verifying Existing Database code - Generate Test Data for Databases by ....
Database Tuning
    Enterprise Servers and Development - SQL Server - SQL Server 2005 Documentation - SQL Server 2005 Tutorials - SQL Server Tools Tutorials - Database Engine Tuning Advisor Tutorial
Export / Import Data - SQL Server Integration Services (SSIS)
    Enterprise Servers and Development - SQL Server - SQL server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Overview - SQL Server Integration Services (SSIS)  
Export / Import Data - SQL Server Import and Export Wizard
    Enterprise Servers and Development - SQL Server - SQL server 2005 Documentation - SQL Server 2005 Books Online - SQL Server Integration Services (SSIS) - Designing and Creating Integration Services Packages - Creating Packagaes using the SQL server Import and Export Wizard.




Starting the server
====================

- net start server; or
- osql /Usa -P



Login to server
================
- use SQL Server Enterprise Manager
- use SQL Query Analyser
osql /U [login_id] /P [password] /S [servername]

Stopping the server
====================
SQL Server Enterprise Manager
SQL Server Service Manager
SHUTDOWN statement
net stop mssqlserver
Control Panel
CTRL+C


Converting CSV to MSSQL
=========================
1. Assume the CSV data file is available and it has first row as headings for the table.
2. Open the CSV file with MS Access 2007. Open up the table of data.
3. In MS Access, click Database Tools - SQL Server. This opens up an upsizing wizard.
4. In the wizard, select Create New DB - click Next.
5. In the wizard, choose DB server, Use Trusted Connection, enter a name for the new SQL DB, click Next.
6. In the wizard, select the Tables to be exported - click Next.
7. In the wizard, select the following: Indexes, Validation Rules, Defaults,
   Table Relationships,  Use DRI, click Next.
7. In the wizard, select No Application Changes - click Next. Click Finish. The DB gets converted into MSSQL.

Note that the Table Structure and Data would be migrated to MSSQL in the above procedure.
BUT, for very large datasets, when there are incompatibilities with the data types, sometimes the
migration will fail to migrate data but the structure will have been created.

The additional steps below is for the situation where the Table Structure has been migrated
successfully to MSSQL, but not the data. Assume that the original data is still in the CSV file. The
do the following.

1. Create a new Visual Studio Project: Add New Project - Database Projects - Microsoft SQL Server
    - SQL-CLR - C# SQL Server Project.
2. In the project created above, in the Solutions Explorer, go to its folder called Test Scripts,
   and create a new Test Script.
3. The test script eg. foo.sql can be any kind of script and can be run interactively.
4. To create a script to copy data from the CSV file to SQL DB, write the following script and execute:

BULK
INSERT

FROM 'file.cxv'
WITH
(
FIRSTROW = 2,     -- starts importing data in row 2
--LASTROW = 40,   -- imports until row 40
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

5. If there are any import errors, then consider modifying the data type of the table.
Example if a column is type int, then in the data file, the data may be !#NUM. One way to deal with
this is to change the column data type to nvarchar.


Visual Studio Database Project
================================
1. In Solution Explorer of VS.Net, do Add New Project.
2. Choose Microsoft Database Project - SQL Server 2005 Wizard
   Give this DB project name: DB_A
3. Select:
   Organize my project by Schema / Object type
   Default schema: dbo
   Include Schema name in the file name
   Enable SQL CLR
4. Select:
   select everything except "Numeric round abort" - this is the default
   Database Collation: SQL_Latin1_General_CP1_CS_AS
5. Import existing schema - this is where you choose your existing database.
   Import the DB called: DB_B
   Also select: Script the column collation .....
6. Select:
   Build output path: .\sql\
   IMPORTANT - Target Database name: DB_B  
      this should be the name of the actual DB that is being pointed to.
   Block incremental deployment ....
   Click Finish.

Views and Stored Procedures
=================
Stored Procedures belong to the database itself. VS.Net allow the creation of stored
procedures through the IDE via the Database project.

1. From the Solution Explorer, go to the DB project, navigate the tree:
   Schema Objects - Schemas - dbo - Stored Procedures.
2. Click Add New Item - Programmability - Procedure.
3. A stored procedure template is created and ready to be edited.

A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL
statements that execute as a batch. Views are queried like tables and do not accept parameters.

Stored procedures are more complex than views. Stored procedures can have both input
and output parameters and can contain statements to control the flow of the code,
such as IF and WHILE statements. It is good programming practice to use stored procedures for
all repetitive actions in the database.

---View-
CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;
GO
-- Testing the View
SELECT * FROM vw_Names;
GO

--- Stored Proc
CREATE PROCEDURE pr_Names @VarPrice money
   AS
   BEGIN
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @varPrice;
   END
GO
--- Testing Stored Proc
EXECUTE pr_Names 10.00;
GO


To run Stored Procedures for Views from MS VS.Net, just highlight the SQL command and
right click Execute SQL!



Transact SQL
============
Ref: MSDN - Enterprise and Servers Development - SQL Server - SQL Server 2005 Documentation -
     SQL Server Books Online - SQL Server Database Engine - Designing and Creating Databases

SET NOCOUNT ON        instructs SQL Server not to count the rows in the result set

NOLOCK                is a SQL hint to not issue a shared lock, not honor exclusive locks, and
                      maybe permit a dirty read. In the delete procedure, the row count is left
                      on (by default) and the number of rows deleted is returned

IF blahCondition
   blah
   blah
ELSE
   blah
END;                 Semicolon need at the end

------ Example
USE master;
GO

--Delete the TestData database if it exists.
IF EXISTS(SELECT * from sys.databases WHERE name='TestData')
BEGIN
    DROP DATABASE TestData;
END

--Create a new database called TestData.
CREATE DATABASE TestData;

USE TestData
GO

CREATE TABLE dbo.Products
   (ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription text NULL)
GO



-- Standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO

-- Changing the order of the columns
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')
GO

UPDATE dbo.Products
    SET ProductName = 'Flat Head Screwdriver'
    WHERE ProductID = 50
GO

-- Returns only two of the records in the table
SELECT ProductID, ProductName, Price, ProductDescription
    FROM dbo.Products
    WHERE ProductID < 60
GO

-- Returns ProductName and the Price including a 7% tax
-- Provides the name CustomerPays for the calculated column
SELECT ProductName, Price * 1.07 AS CustomerPays
    FROM dbo.Products
GO


Passing Arguments into Stored Procedures
- In the example below, schm and Tsource arguments have default values assigned.
-------
Create PROCEDURE [dbo].[testPERD_XPOS]  (
     @tblName sysname,
     @schm sysname = 'dbo',
     @Tsource char(50) = NULL



Transact SQL Data Types vs DB Datatypes
=========================================
datetime - DT_DBTIMESTAMP
money - DT_CY



Transact SQL functions
=========================
-- To get current database name
Select db_name()

-- To check compatibility level DB
exec sp_dbcmptlevel 'RatingsHistoryDB'

Result: 100 means version 10.0
Result: 90 means version 9.0


-- QUOTENAME(, '')
If quote character is omitted, then square brackets is used.
Example:
    SELECT QUOTENAME('abc[]def')
    result is: [abc[]]def]

-- RTRIM
Returns a character string after truncating all trailing blanks

-- EXEC
To execute a stored procedure from a Query window (not from within Stored Procedure).
   exec @arg1=blah, @arg2=bleh

-- To find rows of all tables
SELECT     [TableName] = so.name,     [RowCount] = MAX(si.rows)
FROM     sysobjects so,     sysindexes si
WHERE     so.xtype = 'U'     AND     si.id = OBJECT_ID(so.name)
GROUP BY     so.name
ORDER BY     2 DESC

-- To find size (in bytes) of a table
exec sp_spaceused


-- To reduce size of database
Using SQL Server Management Studio, right click on DB - Tasks - Shrink - Files | Database.




Checking if table, stored procedures, synonyms, etc EXISTS
==============================================================

To check that a table exists:  
   IF OBJECT_ID ('AdventureWorks.dbo.AWBuildVersion','U') IS NOT NULL
      Print 'Table Exists'
   ELSE
      Print 'Table Does Not Exists'

Note the type of object 'U' represents a Table. For more object type codes, see section on: "sys.object constants"



sys.object constants
======================
       principal_id  -- int -- ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.
Is NULL if there is no alternate individual owner.
Is NULL if the object type is one of the following:
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
TA = Assembly (CLR-integration) trigger
TR = SQL trigger
UQ = UNIQUE constraint


     type -- char(2) -- Object type:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

    type_desc -- nvarchar(60) -- Description of the object type:
AGGREGATE_FUNCTION
CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
FOREIGN_KEY_CONSTRAINT
SQL_SCALAR_FUNCTION
CLR_SCALAR_FUNCTION
CLR_TABLE_VALUED_FUNCTION
SQL_INLINE_TABLE_VALUED_FUNCTION
INTERNAL_TABLE
SQL_STORED_PROCEDURE
CLR_STORED_PROCEDURE
PLAN_GUIDE
PRIMARY_KEY_CONSTRAINT
RULE
REPLICATION_FILTER_PROCEDURE
SYSTEM_TABLE
SYNONYM
SERVICE_QUEUE
CLR_TRIGGER
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
TABLE_TYPE
USER_TABLE
UNIQUE_CONSTRAINT
VIEW
EXTENDED_STORED_PROCEDURE




Configuring Permissions
=========================

-- give permission to access instance of SQL DB
CREATE LOGIN [computer_name\Mary]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = [TestData];
GO

-- give permission to access TestData DB
USE [TestData];
GO
CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO

-- give permission to access Stored Proc
GRANT EXECUTE ON pr_Names TO Mary;
GO


Deleting tables, DB, etc
==========================
USE TestData;
GO

--Use the REVOKE statement to remove execute permission for Mary on the stored procedure:
REVOKE EXECUTE ON pr_Names FROM Mary;
GO

-- Use the DROP statement to remove permission for Mary to access the TestData database:
DROP USER Mary;
GO

--
Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005:
DROP LOGIN [\Mary];
GO

-- Use the DROP statement to remove the store procedure pr_Names:
DROP PROC pr_Names;
GO

-- Use the DROP statement to remove the view vw_Names:
DROP View vw_Names;
GO

-- Use the DELETE statement to remove all rows from the Products table:
DELETE FROM Products;
GO

-- Use the DROP statement to remove the Products table:
DROP Table Products;
GO

-- You cannot remove the TestData database while you are in the database; therefore, first switch context to another database, and then use the DROP statement to remove the TestData database:
USE MASTER;
GO
DROP DATABASE TestData;
GO



Timing
========


Bulk Insert Selected Column
============================
--CREATE VIEW testView
--AS
--SELECT exposure_class
--FROM ASB_IPRE_CommProperty
BULK INSERT testView
FROM 'H:\workCBA\myVS2005\StressTest\TestRiskRatedPFConsole\Data\testjunk.csv'
WITH
(
--FIRSTROW = 2,
----LASTROW = 10,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO


How to use Merge in SQLserver 2008
====================================
This is a description of how to use the SQL Server 2008 feature - "Merge ... Using".
The operation here involves 2 Tables.
Tsrc = Source table where the information is downloaded from.
Trgt = Local table, typically our working version of the table.

The real life situation / scenario is that from time to time, Tsrc will be changed. At different times, we need to update our local table

Trgt such that it reflects the source table (Tsrc) but also require to keep the records of old data.


The process involve a merge between the local and the source tables and the results are kept in the local table.
- The source table is unchanged.
- The local table has extra columns RUN_I and Clatest.
- Column RUN_I keeps track of the runs each time we perform a download and merge from the source database.
- Column Clatest keeps track of whether the record is the latest record or has been modified.


Define the two tables as follows:

CREATE TABLE [dbo].[Tsrc](
[C1] [int] NOT NULL,
[C2] [nchar](3) NULL,
[C3] [date] NOT NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[Trgt](
[RUN_I] [int] NOT NULL,  -- a run id
[C1] [int] NOT NULL,
[C2] [nchar](3) NULL,
[C3] [date] NOT NULL,
[Clatest] [int] NOT NULL      --  is 0 if record is old, is 1 if record is the new/latest.
) ON [PRIMARY]



The Merge process actually occurs in a temporary table-space facilitated by a VIEW. The view can be defined as follows:


CREATE VIEW [dbo].[VTemp]
AS
SELECT     RUN_I, C1, C2, C3, Clatest
FROM         dbo.Trgt
WHERE     (Clatest = 1)


There is also a temporary table called UpdateRecords which are created from the Merge process. This table contain the meta-data about

which records have changed from the Merge process.


The description below is based on the complete Insert Into .. Select .. Merge operations.
The results from the merge operation, which return multiple records, are inserted into the local table Trgt.

Overall Description:
1. Get desired data from source table. Lines 5-13
2. Merge results from step 1 with local table. Lines 4-26. In this process there are three conditions and one post processing.
3. Within the merge operation, the 3 conditions are: i) when there is a match between source and local table conditions; ii) when record

is in source but not in local iii) when record is in local but not in source. Within each of these conditions, there are operations that

perform a change to the local table.
4. At the end of the merge operation, as a post process, meta-data can be outputted as a temporary table (eg UpdateRecords table).
5. From the temporary table of the Merge operation, select the records which have been replaced. Lines 2-28.
6. From step 6, insert the results back into the local table Trgt. Line 1.


Detailed Description: From the innnermost loop ......
Lines 8-10:  Downloads records from Source table, with relevant conditions.
Lines 5-13:  The downloaded source data is aliased as src(C1,C2,C3), to be used as the source of the Merge operation
Line  14:    Specify conditions for merge.
Lines 15-17: Columns C1, C3 are info to identify records between source and local tables. When identified, then if the C2 info is

different between source and local tables, or either one is null, then update C2 info and Run_I identifier.
Lines 18-21: When records in source but not in local, then put data from source to local.
Lines 22-24: When records in local but not in source, then mark the record as obsolete by setting Clatest = 0.
Line  4:     This merging process does not merged with the local table, but rather it merges with the sub-table of the local table, where

the entries are the most current ones, ie Clatest = 1.
Line  26:    Outputs the merge process including special value $action which has values 'UPDATE', 'INSERT', etc. And also Deleted and

Inserted values.
Lines 04-26: The complete merge process. At the end of this, both view and its real data in the local table Trgt, would have been

changed. It also outputs meta data including inserted information and deleted information.
Eg. Deleted.RUN_I AS prevRunId, Deleted.C1, Deleted.C2 AS prevCQC, Deleted.C3, 0 AS prevClatest, Inserted.Clatest AS newClatest

Lines 27:    The results from the merge process, acting like a temporary table for the Select command.
Lines 02-28: Selection of META results from the merge process to extract information of updated records. Note in the actual merge process

of lines 04-26, for the case when a row is being updated, the Trgt table and view will hold the updated record only, the previous record

is not kept. Example
    Before merge: {RUN_I, C1, C2, C3, Clatest} = {23,  1, 2, 3,  1}   -> old
    After merge:  {RUN_I, C1, C2, C3, Clatest} = {24,  1, 5, 3,  1}   -> new
There are no extra rows added to keep track of the old record. However at the end of the merge process, there are Meta-fields available

which keep track of the old record as: Deleted.RUN_I, Deleted.C1, Deleted.C2, Deleted.C3, Deleted.Clatest. In this example:
    {Deleted.RUN_I, Deleted.C1, Deleted.C2, Deleted.C3, Deleted.Clatest} = {23,  1, 2, 3,  1}
In Line 26, these Meta-fields are selective extracted into the temporary table called UpdateRecords. Also a new field called prevClatest

is created with values 0 to indicate old results.
Finally, in line 2, the select statement chooses {prevRunId, C1, prevCQC, C3, prevClatest}, in the example this is:
        {23, 1, 2, 3, 0}
And ultimately in Line 1, the results from Line 2 which represent replaced records, are added back into the local table Trgt.

Lines 01-28: Add records to local table Trgt, those records selected by Lines 02-28 which are in fact old records which have been updated

and use prevClatest into the Clatest column.





01 INSERT INTO Trgt
02 SELECT prevRunId, C1, prevCQC, C3, prevClatest
03 FROM (
04 MERGE VTemp AS trgt
05     USING (SELECT crr.C1, crr.C2, crr.C3
06 FROM Tsrc AS crr
07 INNER JOIN (
08 SELECT DISTINCT C1
09 FROM Tsrc
10 WHERE C1>2 AND C2 in ('A','A0')
11 ) as c
12 ON c.C1 = crr.C1
13 ) AS src (C1, C2, C3)
14 ON trgt.C1 = src.C1 AND trgt.C3 = src.C3
15 WHEN MATCHED AND ((trgt.C2 != src.C2) OR (ISNULL(trgt.C2,'') != ISNULL(src.C2,'')))
16 -- record exists for given client and rating date, but rating value has changed so update record
17 THEN UPDATE SET C2 = src.C2, RUN_I = @RUN_I
18 WHEN NOT MATCHED BY TARGET
19 -- records in source that are not in target, insert new records in target
20 THEN INSERT (RUN_I, C1, C2, C3, Clatest)
21 VALUES (@RUN_I, src.C1, src.C2, src.C3, 1)
22 WHEN NOT MATCHED BY SOURCE
23 -- records in target that are no longer in source, expire target records
24 THEN UPDATE SET Clatest = 0
25 -- record exists for given client and rating date, but rating value has changed so insert the previous record with old rating
26 OUTPUT $action, Deleted.RUN_I AS prevRunId, Deleted.C1, Deleted.C2 AS prevCQC, Deleted.C3, 0 AS prevClatest,

Inserted.Clatest AS newClatest)
27 AS UpdatedRecords (Action, prevRunId, C1, prevCQC, C3, prevClatest, newClatest)
28 WHERE Action = 'UPDATE' AND newClatest = 1



Installing SQL Server 2008 with Visual Studio 2008
====================================================
Visual Studio 2008 Team Suite
Visual Studio 2008 Team Suite SP1
SQL Server 2008
Microsoft® Visual Studio Team System 2008 Database Edition GDR R2
(http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en#filelist)
Visual Studio 2008 Team Explorer



Showstoppers during installation:

Message: Setup could not verify the integrity of the file Update.inf. Make sure the Cryptographic service is running on this computer.
Solution: First solution works in: http://support.microsoft.com/kb/822798

Message: The following error has occurred: Error 1316.A network error occurred while attempting to read from the file
............\SSCERuntime-enu.msi
Solution: see http://support.microsoft.com/kb/970876


Message: The following error has occurred: Upgrade Failed due to the following Error.The error code is :-2147467259.Message:Unspecified error
Solution:
If Visual Studio is installed to a non-default location on X64 machine and X64 BIDS are installed, the SQL Server setup fails.
This failure does not apply when setup runs on X86 machines or on X64 machines when the BIDS are installed in WOW mode (X86 on X64).
Solution:
See https://connect.microsoft.com/SQLServer/feedback/details/363017/sql-server-2008-rtm-upgrade-failed-due-to-the-following-error-the-error-code-is-2147467259-message-unspecified-error




How to use SQL Server 2008 Projects in Visual Studio 2008
===========================================================
Prerequisites: see section "Installing SQL Server 2008 with Visual Studio 2008"

There can be various reasons why we need to create a SQL Server 2008 project as a Visual Studio 2008 project. The reason in this case is so that we can create an image of the DB to deploy to other systems. Hence the initial step would be to create a DB itself in the DB server. Then we create the following Visual Studio - Sql Server project. Having this Visual Studio - Sql Server project enables us to link to the actual DB and deploy any changes to the real DB or to a new DB.

This section explains the process to create the Visual Studio - Sql Server project.
1. In Visual Studio: File - New - Project.
2. In the Project dialog: Database Projects - SQL Server 2008 - SQL Server 2008 Wizard.
3. In the New Project Wizard,
i) Type of project: select "A database project to manage changes to a user-defined database.
ii) In the SQL Script File section, select "By type of object".

To Link the VS project to the actual Database:
1. Once the vS SQL Server project has been created, go to Solution Explorer and right click on the DB project.
2. Select "Import database objects and settings"
3. In the Import Database Wizard, choose the connection to the real DB and fill in any other options.
4. Press Start.

Now that the VS SQL Server project DB has been created, and has also linked to the actual real DB, we can build this VS DB project and re-create the DB in other DB servers. To do this, go to the VS SQL Server project and Build, then Deploy.



Link to other servers
======================
- List all databases in the server:
     exec sp_databases
- List the servers being connected to:
     select * from sys.servers
- List all tables in the database
     exec sp_tables
- To connect to a remote DB which is also MSSQL server
     exec sp_addlinkedserver @server='', @srvproduct='SQL Server'
Note that 'SQL Server' is a special fixed string - do not modify.

Ref:
http://blogs.techrepublic.com.com/datacenter/?p=133

If @srvproduct is not 'SQL Server', then @provider may be necessary. @provider may have the following values:
SQL Server - SQLNCLI
Oracle     - MSDAORA
Oracle, version 8 and later  - OraOLEDB.Oracle
Access/Jet and Excel         - Microsoft.Jet.OLEDB.4.0
ODBC data source - MSDASQL
IBM DB2 Database - DB2OLEDB

If the remote server cannot be accessed, then perhaps permissions need to be configured,
---- see master.dbo.sp_addlinkedsrvlogin

Linked Server - can be done from Management Studio, by:
- Server Objects - Linked Servers, right click to Add - New Linked Server


Using SQL Server to Import to data file
==========================================
This is for the transfer of large tables across databases.
1. Go to Start - Programs - Microsoft SQL Server 200x - Import and Export Data (32bit)
2. Follow instructions in the wizard to Select source.
3. In the "Choose a Destination" dialog, choose "Flat File Destination" in the "Destination" drop down list.
Then select the file name to save the data. Also check "Column names in the first data row".
4. In the "Specify Table Copy or Query" dialog, choose "Copy data from one or more tables or viewa".
5. In the "Configure Flat File Destination" dialog, choose the table to be copied. By clicking the "Edit Mappings" button, the columns of the table can be modified.
6. In the "Save and Run Package", select "Run Immediately" and "Save SSIS Package" and "File System".
7. In the "Save SSIS Package", select the destination to where the package is to be saved.
8. The table will be saved in two files: .dtsx is the SSIS package file, .txt or whatever name is given in Step 3 is the raw data file. 

WARNING - When importing to flat file , MSSQL - SSIS transforms the original data types and usually saves them into strings. When exporting these data back into the DB, they are still strings, but will be able to populate columns which have the original data type.

Using SQL Server to Export from data file
==========================================
This is for the transfer of large tables across databases.
1. Go to Start - Programs - Microsoft SQL Server 200x - Import and Export Data (32bit)
2. In the "Choose Data Source" dialog, choose "Flat File Source" in the "Source" drop down list.
Then select the file name. Also check "Column names in the first data row".
3. In the "Choose a Destination" dialog, selec the local DB server. Then also select the DB from the Database drop down list.
4. In the "Select Source Tables and Views", select the table needed.
5. Review the data mapping in the dialog.
6. In the "Save and Run Package", select "Run Immediately".

7. In the "Save SSIS Package", select the destination to where the package is to be saved.


Transfer or Copy Database using Backup and Restore with MS SQL Server Management Studio
=================================================================
This function allows the transfer of an entire Database (DB) at one go by using the backup and restoration facility of MSSMS.
1. Open up MS SQL Server Management Studio (MSSMS).
2. In the Object Explorer, navigate to the specific DB to be backed up. Right click on it, then select Tasks - Back Up...
3. In the Backup DB dialog in the General tab, ensure the following settings:
      Source - Database: name of database is correct
        - Backup type: Full
- Backup Component: Database
      Destination - Disk
               the backp file path will be displayed in the text box.
4. In the Backup DB dialog in the Options tab, there are various options that control the backup such as:
      Overwrite / Append backup file
 Reliability: verifying backup, continue on error
 Compression
5. Click OK to begin backup. The backup file is usually .bak
6. To copy over the backed up DB to a new server, open up MSSMS and connect to the new server.
7. In the Object Explorer, under the DB server, right click on the "Databases" and select "Restore Database".
8. In the Restore DB dialog, in the General tab, fill in the following:
      To Database: type in the name of the new DB
 Source To Restore: From Device, then click on the button to choose the backup DB file.
 In the list of DB to restore, put a tick in the DB to be restored.
9. In the Restore DB dialog, in the Options tab, fill in the following:
      Restore Options: choose from various options as needed.
 In the table "Restore the DB files as", under the "Restore As" column, manually edit the path to where the DB files should be created.
 Recovery State options: choose from various options as needed.
10. Click OK to begin the Transfer / Recovery of DB.



Difference Between nVarChar vs VarChar and nChar vs Char
===========================================================
The var or char with the letter n in front means Unicode character is allowed. N stands for 'National'.
Note the nchar, nvarchar takes at least twice as much storage than the non-n version.

Unicode
========
Unicode allows data to be stored in characters beyond ASCII characters. It allows letters from other languages.
1. Unicode data type are: nChar, nVarchar
2. Unicode string, eg.
      set @tmp = N'Select * from blah'


Using Variables / Parameters in Stored Procedures
===================================================
- Using database name as a variable
    DECLARE @Database VARCHAR(10)
    SET @Database = 'TWO'
    EXEC('USE  ' + @DATABASE)

- Using sp_executesql example:
use blah
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' + ' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
                   N'@RECORDCNT int out, @ORDERID int',
                   @RECCNT out,
                   @ORDID
print 'The number of records that have an OrderId' +  ' greater than ' + @ORDID + ' is ' + cast(@RECCNT as char(5))

In the example above, @ORDID -> @ORDERID as input, then @RECORDCNT -> @RECCNT as output.


Dynamic SQL
=============
Ref: "The Curse and Blessings of Dynamic SQL" http://www.sommarskog.se/dynamic_sql.html

- Safest way of using Dynamic SQL is through Stored Procedures in T-SQL, rather than Stored Procedures in C#, .Net, and rather than sending SQL statements to DB server.
- use sp_executesql rather than EXEC() in Stored Procedures
- Use dbo to prefix table names
- Use a @debug parameter in SP for easy debugging, eg.
       CREATE PROCEDURE blah @debug bit = 0 , @tblname sysname
  AS
     blah
 DECLARE @sql nvarchar(max)
 SET @sql = 'select * from dbo.' + QUOTENAME(@tblname)
 IF @debug = 1 PRINT @sql
- When table name is variable, eg @tableName, use QUOTENAME(@tableName).
  Note that QUOTENAME can only be used with sp_executesql, but not in EXEC(). The equivalent of using EXEC() is:
          EXEC('Select * from ' + @tblname)
- When passing variable tablename eg @tblname in the previous example, use the "sysname" data type.
- When using DB or Linked Servers, use SYNONYM
           CREATE SYNONYM otherDB FOR other.DB.table


Partitioned Tables
======================
??? See Books Online


Using SQL Server Management Studio (SSMS)
==========================================
The following applies to SSMS 2008

Tools in SMSS:
i) Activity Monitor - this allows you to monitor the performance of SQL Server. To Open the Activity Monitor:
   - In object explorer, right click on the server name of the DB, and choose Activity Monitor
   - In the Toolbar, click the icon that looks like a graph.


SQL Server Indexes
====================
Indexes are extra sets of information pointing to specified column data in a table. As its name suggests, it indexes a set of column data to provide something like pointers or addresses to the data. There are various types of indexes for different purposes that can be created.

Example: Select ProdID, ProdName, UnitPrice FROM Prod WHERE UnitPrice > 12.5

Non-Unique Index - In the example above, the non-unique index will sort the Unit Price, and produce an internal index that point the sorted UnitPrice to the original row position in the table.
       Eg. CREATE INDEX Idx_Price ON Prod (UnitPrice)

Unique Index - the column to be indexed need to be unique.
             - a Primary Key is automatically a unique index.  
       Eg. CREATE UNIQUE INDEX Idx_Price ON Prod (UnitPrice)

Clustered Index - instead of keeping an index to the indexed column, a clustered index RE-SORTS ALL columns based on the chosen                     column to be indexed
                - having a primary key automatically makes the table into a clustered index.
- if there is no primary key, then the table should be made into a clustered index based on a certain column.
- a table can have only one clustered index.
- Every table should have a clustered indexe for performance reason.
- a clustered index can be unique or non-unique.
       Eg. CREATE CLUSTERED INDEX Idx_Price ON Prod (UnitPrice)

Composite Index - where multiple columns are used as the index
                - can be clustered or non-clustered
- if a primary key is compose of two columns, then those two columns are also Composite Indexes.
       Eg. CREATE CLUSTERED INDEX Idx_Price_ProdName ON Prod (UnitPrice, ProdName)
      
When to use Indexes
- when Searching for records in queries with WHERE conditions, with SELECT, UPDATE or DELETE statements.
- when Sorting records, eg with ORDER BY keyword
- when Grouping records, eg with GROUP BY keyword
- when Covering queries use Composite Index. In the example below, apart from using Price as the index, the ProdName can also be used together as the index.
       Eg Select ProdName, Price FROM Prod ORDER BY Price
- when Matching complex search, useful for searches like below
       Eg SELECT * FROM Order WHERE OrderID = 1 AND ProdID = 2

When not to use Indexes
- when DB space is limited, because indexes take up extra spaces
- when queries modify data, eg with INSERT, UPDATE, DELETE statements; because the indexes need to be modified too which reduces performance.

Other Guidelines to use Indexes
- make indexes from columns with short data types, eg int, rather than long characters.
- columns where values are mostly distinct or unique.
- delete indexes which are not needed

-- to rename an index
     EXEC sp_rename 'Prod.IX_UnitPrice', 'IX_Price'

-- to delete an index
     DROP INDEX .

-- to see a list of all indexes created on a table
     EXEC sp_helpindex

-- to see the space used by Indexes
     EXEC sp_spaceused