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 ;

Check for Duplicate Records in SQL Server

SELECT email,COUNT(email) AS NumOccurrencesFROM usersGROUP BY emailHAVING ( COUNT(email) > 1 )

Date Functions

CURRENT_DATE ( ) Returns the current date.
CURRENT_TIME () Returns the current local time.
CURRENT_TIMESTAMP () Returns the current local date and local time as a timestamp value.
CURDATE ( ) Returns the current date.
CURTIME ( ) Returns the current local time.
DAYNAME (date_exp) Returns a character string containing the data source/specific name of the day (for example, Sunday through Saturday).
DAYOFMONTH (date_exp) Returns the day of the month based on the month field in date_exp as an integer value in the range of 1-31.
DAYOFWEEK (date_exp) Returns the day of the week based on the week field in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.
DAYOFYEAR(date_exp) Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.
EXTRACT (extract-field FROM extract-source) Returns the extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression.
HOUR (time_exp) Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.
MINUTE (time_exp) Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.
MONTH (date_exp) Returns the month based on the month field in date_exp as an integer value in the range of 1-12.
MONTHNAME(date_exp) Returns a character string containing the data source/specific name of the month (for example, January through December). Currently only supports English locale.
NOW ( ) Returns current date and time as a timestamp value.
QUARTER(date_exp) Returns the quarter in date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.
SECOND (time_exp) Returns the second based on the second field in time_exp as an integer value in the range of 0-59.

Difference between two dates in T-SQL

for day datediff(days,[column_name1],[column_name2])
for month datediff(monts,[column_name1],[column_name2])
for year datediff(year,[column_name1],[column_name2])

Index Optimization tips

• Every index increases the time in takes to perform INSERTS, UPDATESand DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
• Keep your indexes as narrow as possible. This reduces the size ofthe index and reduces the number of reads required to read the index.
• Try to create indexes on columns that have integer values rather than character values.
• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the left most of the key.
• If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
• Create surrogate integer primary key (identity for example) if yourtable will not have many insert operations.
• Clustered indexes are more preferable than nonclustered, if you needto select by a range of values or you need to sort results set withGROUP BY or ORDER BY.
• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
• You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables inyour database may need indexes. This trace will show which tables arebeing scanned by queries instead of using an index.
• You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.

SQL Optimization Tips

• Use views and stored procedures instead of heavy-duty queries.This can reduce network traffic, because your client will send toserver only stored procedure or view name (perhaps with someparameters) instead of large heavy-duty queries text. This can be usedto facilitate permission management also, because you can restrictuser access to table columns they should not see.
• Try to use constraints instead of triggers, whenever possible.Constraints are much more efficient than triggers and can boostperformance. So, you should use constraints instead of triggers,whenever possible.
• Use table variables instead of temporary tables.Table variables require less locking and logging resources thantemporary tables, so table variables should be used whenever possible.The table variables are available in SQL Server 2000 only.
• Try to use UNION ALL statement instead of UNION, whenever possible.The UNION ALL statement is much faster than UNION, because UNION ALLstatement does not look for duplicate rows, and UNION statement doeslook for duplicate rows, whether or not they exist.
• Try to avoid using the DISTINCT clause, whenever possible.Because using the DISTINCT clause will result in some performancedegradation, you should use this clause only when it is necessary.
• Try to avoid using SQL Server cursors, whenever possible.SQL Server cursors can result in some performance degradation incomparison with select statements. Try to use correlated sub-query orderived tables, if you need to perform row-by-row operations.
• Try to avoid the HAVING clause, whenever possible.The HAVING clause is used to restrict the result set returned by theGROUP BY clause. When you use GROUP BY with the HAVING clause, theGROUP BY clause divides the rows into sets of grouped rows andaggregates their values, and then the HAVING clause eliminatesundesired aggregated groups. In many cases, you can write your selectstatement so, that it will contain only WHERE and GROUP BY clauseswithout HAVING clause. This can improve the performance of your query.
• If you need to return the total table's row count, you can usealternative way instead of SELECT COUNT(*) statement.Because SELECT COUNT(*) statement make a full table scan to return thetotal table's row count, it can take very many time for the largetable. There is another way to determine the total row count in atable. You can use sysindexes system table, in this case. There isROWS column in the sysindexes table. This column contains the totalrow count for each table in your database. So, you can use thefollowing select statement instead of SELECT COUNT(*): SELECT rowsFROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So,you can improve the speed of such queries in several times.
• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.This can reduce network traffic, because your client will not receivethe message indicating the number of rows affected by a T-SQL statement.
• Try to restrict the queries result set by using the WHERE clause.This can results in good performance benefits, because SQL Server willreturn to client only particular rows, not all rows from the table(s).This can reduce network traffic and boost the overall performance ofthe query.
• Use the select statements with TOP keyword or the SET ROWCOUNTstatement, if you need to return only the first n rows.This can improve performance of your queries, because the smallerresult set will be returned. This can also reduce the traffic betweenthe server and the clients.
• Try to restrict the queries result set by returning only theparticular columns from the table, not all table's columns.This can results in good performance benefits, because SQL Server willreturn to client only particular columns, not all table's columns.This can reduce network traffic and boost the overall performance ofthe query.
1.Indexes
2.avoid more number of triggers on the table
3.unnecessary complicated joins
4.correct use of Group by clause with the select list
5 In worst cases Denormalization

Stored procedures name that are scheduled in a sql job

1. open the Sql server enterprise manager.
2. click on the management folder.
3. and select jobs.
4. right click on the jobs option and click new jobs.
5. type the name of the job.
6. select the category name (database name).
7. select the owner name.
8. type the description.
9. click on the step1 tab page.
10. and click on the new button.
11. write the step name like 'step1'.
12. select the type like 'Transact-sql script(TSQL)'.12. select the database name.
13. in command textbox type your uspname like exec usp_uspname.
14. click on the schedule tab page.
15. and click on the new schedule button.
16. type the name like 'abc'.
17. click on the recurring option button.
18. and click on the change button.
19. and click on the daily option button and also click on the occurs every option and select the time duration.
20. and after that click on the ok button.

Remove Special Character from XML string using SQL Server

http://helpindotnet.wordpress.com/

Relations in SQL

There are 3 types of relations between tables – One-To-Many, Many-To-Many and One-To-One.
The relation One-To-Many is the most common of the 3 types. In One-To-Many relation a row in one of the tables can have many matching rows in the second table, but a row the second table can match only one row in the first table.
In our example, each manufacturer (a row in the Manufacturer table) produces several different computer models (several rows in the Product table), but each particular product (a row in the Product table) has only one manufacturer (a row in the Manufacturer table).
The second type is the Many-To-Many relation. In this relation many rows from the first table can match many rows in the second and the other way around. To define this type of relation you need a third table whose primary key is composed of the 2 foreign keys from the other 2 table. To clarify this relation lets review the following example. We have a Article table (ArticleID is primary key) and Category (CategoryID is primary key) table.Every article published in the Article table can belong to multiple categories. To accommodate that, we create a new table called ArticleCategory, which has only 2 columns – ArticleID and CategoryID (these 2 columns form the primary key for this table). This new table called sometimes junction table defines the Many-To-Many relationship between the 2 main tables. One article can belong to multiple categories, and every category may contain more than one article.
In the One-To-One relation each row in the first table may match only one row in the second and the other way around. This relationship is very uncommon simply because if you have this type of relation you may as well keep all the info in one single table.
By dividing the data into 2 tables we successfully removed the redundant manufacturer details from the initial Product table adding an integer column referencing the new Manufacturer table instead.
The process of removing redundant data by creating relations between tables is known as Normalization. Normalization process uses formal methods to design the database in interrelated tables.

Creating a Job in SQL

This topic describes how to use stored procedures to create a Microsoft SQL Server Agent job.
To create a SQL Server Agent job Execute sp_add_job to create a job.

• Execute sp_add_jobstep to create one or more job steps.
• Execute sp_add_schedule to create a schedule.
• Execute sp_attach_schedule to attach a schedule to the job.
• Execute sp_add_jobserver to set the server for the job.

Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.

A job in SQL Server Agent consists of Job steps, an owner to provide the security context for the job, and one or more schedules for executing the job.
Steps for creating a new job is as follows:
1. Create a new job, give it a name, database context and add owner
2. Add one or more job steps to job
3. Specify a schedule to run the job (Optionally)

To create a new job, you need to expand SQL Server Agent node within the Object Explorere in SSMS
Right Click on Jobs node and choose new job. The New Job window opens. In this you can define various properties like Name, Category, Owner, Description.
The Job name can be upto 64 characters long. The Job name should clearly define purpose of the Job.
You can assign Job Category to group jobs together based on the types of action they perform. You can use the built-in type job categories that ship with SQL Server or you can create your own job categories.
A descriptive text enables to enter additional details about the job. If certain business rules govern a job, you should specify them in job description to facilitate job-maintenance actions.
On the New job window, in the general tab you specify the job owner, which defines user or group who manage the jobs. The job owner must be member of the sysadmin role to modify the job. If the user is not member of sysadmin role, you need to ensure the job owner should have acces to proxy accounts necessary to execute the step within job.

Create new Job Steps
The high level steps for defining a job are:
1. Create a new job step, specifying name and type.
2. Define the command you want to execute.
3. Define logging and notification Services.
After clicking new, you need to define the job step's properties. For each job step, you nedd to define a name which can be 64 characters. You need to specify the type for each job step. the most common type of job step is Transact-SQl. With this type, you define database context in which to run the T-SQl batch you specify. You can define a simple T-SQl statement to execute a job step but mostly we specify a call to Stored Procedure.Depending on the job step you select, you get configuration options. You can get these options by clicking on Advanced Page in New Job Step window. You can specify actions to be taken when the step completes successfully or when job step fails.

Advantage of Asp.net Web form Controls

1. An ASP.NET Web Form first gets compiled into Intermediate Language (IL) and and then to the machine language. Not only the server side code gets compiled but every control and element in the page gets compiled. This enables the ASP.NET Web Form to be in any language that is compatible with Common Language Runtime (CLR) engine.
2. The server side code getting translated to IL is that the user can use the application in any browser. In addition, you can reduce the processors and server’s load when you compile the ASP.NET Web Forms in ASP.NET, thereby, increasing the user’s productivity time. Thus ASP.NET allows you to create Web Forms that are platform independent, language independent, and browser independent.
3. The entire page in ASP.NET is compiled when it is requested for the first time and when you make subsequent requests the page is not compiled but shown directly in the browser.
4. Even though ASP.NET web form on the server can contain client side script such as javascript or Jscript.

Hope this article helps you

Techzone