Value type Vs Reference Type

Value Type

1. It stores value directly.
2. It consists of two categories, Structs, Enumerations
3. It cannot assign to a null value.
4. Each instance of value type separate memory is allocated.
5. Stored in stack

Reference Type

1. It stores reference to value.
2. It consists of Class, interfaces, delegates.
3. It can assign a null value.
4. It utilizes the same memory location
5. Stored in heap, by doing this it allows object to be allocated or deallocated in random order at the same time it has an overhead of a memory manager and garbage collector to remove the objects that has been not used for long time.

Techno Zone

Creating batch files in SQL

Using SQLCMD to execute script files easily in different environments
Step 1: Lets create few dummy script files for demo purpose.File1: 01TableCreation.sqlCreate table tblTest(Sno int identity,FName varchar(20))GoFile2: 02InsertRecords.sqlset nocount onInsert into tblTest (Fname) values ('alpha')Insert into tblTest (Fname) values ('beta')File3: 03StoredProcedures.sqlCreate proc usp_GetAllTblTestasSelect sno, fname from tblTestgo
Step 2: Create a batch file and call these .sql files in order.File4: DBInstallationScripts.batsqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\01TableCreation.sql"sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\02InsertRecords.sql"sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\03StoredProcedures.sql"
Step 3: Execute the batch fileFrom the command prompt (Start >> Run >> Cmd) do the following:c:> DBInstallationScripts DBUserName DBPassword DBServerName DatabasenamePlease note we are passing the database username, password, servername and the database to which we need to connect from the batch file. That way it would replace %1, %2, %3 and %4 in the batch file while executing.
Same set of scripts can be executed in different environments like 'Development', 'Testing', 'Production' etc., with ease.

Virtual Table

· Views are called virtual tables because the result set of a view is us not usually saved in the database.
· The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time.
· What is stored in the database is a SELECT statement.
· The result set of the SELECT statement forms the virtual table returned by the view.
· A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.
· A view is used to do any or all of these functions:
Ø Restrict a user to specific rows in a table.
Ø Restrict a user to specific columns.
Ø Join columns from multiple tables so that they look like a single table.
Ø Aggregate information instead of supplying details.

Indexes

An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value. In a database, an index allows the database program to find data in a table without scanning the entire table.Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key. For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C. The performance benefits of indexes, however, do come with a cost. Tables with indexes require more storage space in the database. Also, commands that insert, update, or delete data can take longer and require more processing time to maintain the indexes.
When you design and create indexes, you should ensure that the performance benefits outweigh the extra cost in storage space and processing resources.

Cursors, How to use them and when to use

· A cursor is a mechanism you can use to fetch rows one at a time.
· Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.
· When writing code for a transaction where the result set includes several rows of data, you may declare and use a cursor.
· For example, if you write code that includes a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, by using the FETCH statement, you can retrieve one row at a time from the result set.
The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:
· Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
· Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
· Use the OPEN statement to execute the SELECT statement and populate the cursor.
· Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
· When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is de-allocated, you must issue a DECLARE statement to rebuild the cursor.
Disadvantages of cursors
· Each time a row is fetched from the cursor, it results in a network roundtrip; where as a normal SELECT query makes only one roundtrip, however large the result set is.
· Cursors are also costly because they require more resources and temporary storage (results in more IO operations).
· Further, there are restrictions on the SELECT statements that can be used with some types of cursors.

@@DATEFIRST function in SQL Server

@@DATEFIRST in SQL Server will returns the current value, for the session, of SET DATEFIRST.SET DATEFIRST indicates the specified first day of each week.
The U.S. English default is 7, Sunday.
Language settings affect date information. In the following example, the language is first set to italian.
SELECT @@DATEFIRST returns 1.
The language is then set to us_english.
SELECT @@DATEFIRST returns 7.

Get the size of the database

SELECT table_schema "Database", sum( data_length + index_length ) / 1024 / 1024 "Size (MB)", sum( data_free )/ 1024 / 1024 "Free (MB)" FROM information_schema.TABLES GROUP BY table_schema ;