Privacy Policy for thinkofdotnet.blogspot.in If you require any more information or have any questions about our privacy policy, please feel free to contact us by email at crazytodevelop@gmail.com. At thinkofdotnet.blogspot.in, the privacy of our visitors is of extreme importance to us. This privacy policy document outlines the types of personal information is received and collected by thinkofdotnet.blogspot.in and how it is used. Log Files Like many other Web sites, thinkofdotnet.blogspot.in makes use of log files. The information inside the log files includes internet protocol ( IP ) addresses, type of browser, Internet Service Provider ( ISP ), date/time stamp, referring/exit pages, and number of clicks to analyze trends, administer the site, track user’s movement around the site, and gather demographic information. IP addresses, and other such information are not linked to any information that is personally identifiable. Cookies and Web Beacons thinkofdotnet.blogspot.in does not use cookies. DoubleClick DART Cookie .:: Google, as a third party vendor, uses cookies to serve ads on thinkofdotnet.blogspot.in. .:: Google's use of the DART cookie enables it to serve ads to users based on their visit to thinkofdotnet.blogspot.in and other sites on the Internet. .:: Users may opt out of the use of the DART cookie by visiting the Google ad and content network privacy policy at the following URL - http://www.google.com/privacy_ads.html Some of our advertising partners may use cookies and web beacons on our site. Our advertising partners include .... Google Adsense These third-party ad servers or ad networks use technology to the advertisements and links that appear on thinkofdotnet.blogspot.in send directly to your browsers. They automatically receive your IP address when this occurs. Other technologies ( such as cookies, JavaScript, or Web Beacons ) may also be used by the third-party ad networks to measure the effectiveness of their advertisements and / or to personalize the advertising content that you see. thinkofdotnet.blogspot.in has no access to or control over these cookies that are used by third-party advertisers. You should consult the respective privacy policies of these third-party ad servers for more detailed information on their practices as well as for instructions about how to opt-out of certain practices. thinkofdotnet.blogspot.in's privacy policy does not apply to, and we cannot control the activities of, such other advertisers or web sites. If you wish to disable cookies, you may do so through your individual browser options. More detailed information about cookie management with specific web browsers can be found at the browsers' respective websites.

/*In this workbench, Salman Page provides a gentle introduction to the use of dates in SQL Server. In this new version of her article, it is brought up to date with the newer Datetime features in SQL Server 2005 and 2008. 

Using dates, and times in SQL Server: a workbench approach

This "workbench" on the use of dates and times in SQL Server is structured so it can be pasted in its entirety into the Query Analyser, SSMS or other GUI so that each example can be executed. (Opera works best for this, but the SQL file is included in the speech-bubble at the top of the article in case you hit problems) I'd like to encourage you to experiment. One never fails to come up with surprises; for example, I'd never, before writing this, considered using 'LIKE' when searching Date fields, or using the { t '2:40'} in a stored procedure as a literal date. Likewise, I always like to see as many examples as possible in any articles on SQL Server. There is nothing like it for getting ideas going. Formal descriptions are fine for those with strange extra lumps in their brains, but I'd prefer to see clear explanations peppered with examples! If I have any general advice, it is to use the strengths of the DATETIME, DATE, DATETIME2 and DATETIMEOFFSET data types and never attempt to bypass their use, by storing dates or times in any other formats such as varchars or integers. I've never come across a circumstance where such a practice has provided any lasting benefit.

Introduction

 
Three-tier or multi-tier architecture are often used when describing how clients connect to servers. But what does it all mean and do you need to make any changes to your existing infrastructure?
Let me try to explain this in non-technical terms (or as close to it I can get).

 1-Tier Architecture
We all know software packages like MS Access, MS Excel, QuickBooks and Peachtree just to name a few. They all have the same in common that they access files directly. This means that the file you want to work with must be accessible from a local or shared drive. This is the simplest of all the architectures but also the least secure. Since users have direct access to the files, they could accidentally move, modify or even worse delete the file by accident or on purpose.
There is also usually an issue when multiple users access the same file at the same time: In many cases only one can edit the file while others only have read-only access.
So 1-tier architecture is simple and cheap, but usually unsecured and data can easily be lost if you are not careful.


 2-Tier Architecture
This architecture is also called Client-Server architecture because of the two components: The client that runs the application and the server that handles the database back-end. When the client starts it establishes a connection to the server and communicates as needed with the server while running the client. The client computer usually can’t see the database directly and can only access the data by starting the client. This means that the data on the server is much more secure. Now users are unable to change or delete data unless they have specific user rights to do so.
The client-server solution also allows multiple users to access the database at the same time as long as they are accessing data in different parts of the database. One other huge benefit is that the server is processing data that allows the client to work on the presentation and business logic only. This mean that the client and the server is sharing the workload and by scaling the server to be more powerful than the client, you are usually able to load many clients to the server allowing more users to work on the system at the same time.



 3-Tier Architecture
This involves one more layer called the business logic tier, service tier or middle tier (layer). In the client-server solution the client was handling the business logic that makes the client “thick”. A thick client means that it requires heavy traffic with the server, thus making it difficult to use over slower network connections like Internet and Wireless (3G, Edge or Wi-Fi).
By introducing the middle layer, the client is only handling presentation logic. This means that only little communication is needed between the client and the middle tier making the client “thin” or “thinner”. An example of a thin client is an Internet browser that allows you to see and provide information fast and almost with no delay.
As more users access the system a three-tier solution is more scalable than the other solutions because you can add as many middle tiers (running on each own server) as needed to ensure good performance (N-tier or multiple-tier).
Security is also the best in the three-tier architecture because the middle layer protects the database tier.
There is one major drawback to the N-tier architecture and that is that the additional tiers increase the complexity and cost of the installation.
Take a look at the differences among these three.


Take a look at the differences among these three.

	1-Tier	2-Tier	Multi-Tier
Benefits	Very simpleInexpensive No server needed	Good securityMore scalable Faster execution	Exceptional securityFastest execution “Thin” client Very scalable
Issues	Poor securityMulti user issues	More costlyMore complex “Thick” client	Very costlyVery complex
Users	Usually 1 (or a few)	2-100	50-2000 (+)

In this article I’ll try to explain how you can create a stored procedure that deletes all other stored procedures from a database in Microsoft SQL Server.

If you filter its selection with a where clause and select only those records that that have type = ‘P’ (this is for procedures) then you can get the names of all the procedures. You can store the list of the procedure names in a temporary table and loop delete the procedures or you can use a cursor. I’ve used a cursor and deleted the procedures one by one using the EXEC function.

Here is the code.

Alter Procedure dbo.DeleteAllProcedures

As

      declare @procName varchar(500)

      declare cur cursor

            for select [name] from sys.objects where type = 'p'

      open cur

      fetch next from cur into @procName

      while @@fetch_status = 0

      begin

            if @procName <> 'DeleteAllProcedures'

                  exec('drop procedure ' + @procName)

                  fetch next from cur into @procName

      end

      close cur

      deallocate cur

Go

      Grant Execute On dbo.DeleteAllProcedures To Public

Go

Be very careful when calling this stored procedure. It’s really useful for special purposes and not for every day use.

• first generate the list of stored procedures to drop by inspecting the system catalog view:
  

  SELECT 'DROP PROCEDURE ' + p.NAME
  FROM sys.procedures p 

   This generates a list of DROP PROCEDURE statements in your SSMS output window.
  
• copy that list into a new query window, and possibly adapt it / change it and then execute it

No messy and slow cursors, gives you the ability to check and double-check your list of procedure to be dropped before you actually drop it

Here are fourteen little known tips that you can use to ensure your SQL Server Full-Text Search queries are performing in the most efficient manner possible.

1. Set the virtual memory size to at least 3 times the physical memory installed in the computer, and set the SQL Server "max server memory" server configuration option to half the virtual memory size setting (1.5 times the physical memory).
Because working with full-text search is very resource intensive, you should have enough physical and virtual memory.

2. Set the "Maximize Throughput for Network Applications" option.
This can increase full-text search performance as Windows NT will allocate more RAM to SQL Server than to its file cache.
To set this option, you can do the following:
1. Double-click the Network icon in Control Panel;
2. Click the Services tab;
3. Click Server to select it, and then click the Properties button;
4. Click Maximize Throughput for Network Applications, and then click OK;
5. Restart the computer.

3. Ensure full-text index population takes place during periods of low database access.
Because full-text index population takes some time, these updates should be scheduled during CPU idle time and slow production periods.

4. Assign a very large table (a table that has millions of rows) to its own full-text catalog.
This can improve performance and can be used to simplify administering and monitoring.

5. You can boost the resource usage for the full-text search service (increase the "System Resource Usage" option for the full-text search service).
Run SQL Server Enterprise Manager, expand a server group, and then expand a server. Then expand "Support Services", right-click the "Full-Text Search" and select "Properties". Finally, choose the "Performance" tab and increase the "System Resource Usage" option for the full-text search service.
Note: Don't set the "System Resource Usage" option to the "Dedicated" value (right border of the "System Resource Usage" slider bar) as it can negatively affect your SQL Server's performance.

6. Reduce the full-text unique key size.
To create a full-text index, the table to be indexed must have a unique index. Try to select a numeric column as the full-text unique key to increase the speed of full-text population. If the table to be indexed does not have a numeric unique index, consider creating a numeric unique index.

7. If you have several physical disks, create several Pagefile.sys files so that each Pagefile.sys file will be placed on its own physical disk.
Spreading paging files across multiple disk drives and controllers improves performance on most disk systems because multiple disks can process input/output requests concurrently.

8. If you use SQL Server 2000, consider using the Change Tracking with scheduled or background update index option versus Incremental Population.
The Change Tracking with scheduled propagation should be used when CPU and memory can be used at scheduled times and changes between the scheduled times are not significant.
The Change Tracking with background update index option should be used when CPU and memory are available and the value of an up-to-date index is high.

9. Consider using a full population when a large percentage of records were changed or added at once.

10. If you work with SQL Server 7.0, consider using an incremental population when there have not been a large percentage of records changed or added at once.
Using an incremental population instead of a full population decreases the population time and results in good performance benefits.

11. If you have several physical disks, place the database files separately from the full-text catalog files.
This can produce speed improvements for full-text queries as multiple disks can process input/output requests concurrently.

12. Upgrade to SQL Server 2000 in order to enhance full-text search performance, especially if you need to work with full-text search in clustered environment.
The full text search is not available in SQL Server 7.0 clustered environment.

13. If you work with SQL Server 2000, consider using the new top_n_by_rank parameter with CONTAINSTABLE or FREETEXTTABLE.
It can be used to restrict the number of rows returned. The top_n_by_rank parameter specifies that only the n-highest ranked matches, in descending order, will be returned.

14. Try to use the CONTAINS or FREETEXT predicates instead of the CONTAINSTABLE or FREETEXTTABLE rowset functions, whenever possible.
Because qualifying rows returned by the CONTAINSTABLE or FREETEXTTABLE rowset functions must be explicitly joined with the rows in the original SQL Server table, the queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates.

Here are twelve helpful tips for ensuring that you've constructed your SQL Server stored procedures to perform in the most efficient manner possible.

1. Use stored procedures instead of heavy-duty queries.
This can reduce network traffic as your client will send to the server only the stored procedure name (perhaps with some parameters) instead of all the text from a large heavy-duty query. Stored procedures can be used to enhance security and conceal underlying data objects as well. For example, you can give the users permission to execute the stored procedure to work with restricted sets of columns and data.

2. Include the SET NOCOUNT ON statement in your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
This can reduce network traffic due to the fact that your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

3. Call stored procedures using their fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name.
Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

4. Consider returning the integer value as a RETURN statement instead of returning an integer value as part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using the RETURN statement can boost performance because SQL Server will not create a recordset.

5. Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend using the prefix "sp_" in user-created stored procedure names as SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, followed by the stored procedure using dbo as the owner (if one is not specified).
When you have the stored procedure with the prefix "sp_" in a database other than master, the master database is always checked first. If the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

6. Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improves readability of your code when many parameters are used.
When you use the sp_executesql stored procedure to execute a Transact-SQL statement that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

7. Use the sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends using temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures in order to have a better chance of reusing the execution plans.

8. If you have a very large stored procedure, try to break down the stored procedure into several sub-procedures, and call them from a controlling stored procedure.
The stored procedure will be recompiled when any structural changes are made to a table or view referenced by the stored procedure (an ALTER TABLE statement, for example), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, there's a chance that only a single sub-procedure will be recompiled, while other sub-procedures will not.

9. Try to avoid using temporary tables inside your stored procedures.
Using temporary tables inside stored procedures reduce the chance to reuse the execution plan.

10. Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedures also reduce the chance to reuse the execution plan.

11. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used.

12. Use SQL Server Profiler to determine which stored procedures have been recompiled too often.
To check if a stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

Here are fourteen little known tips that you can use to ensure your Transact-SQL queries are performing in the most efficient manner possible.

1. Try to restrict the queries result set by using the WHERE clause.
This can result in a performance benefit, as SQL Server will return to the client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

2. Try to restrict the queries result set by returning only the particular columns from the table, not all the table's columns.
This can result in a performance benefit as well, because SQL Server will return to the client only particular columns, not all the table's columns. This can reduce network traffic and boost the overall performance of the query.

3. Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic as your client will send to the server only stored procedures or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.

4. Whenever possible, try to avoid using SQL Server cursors.
SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

5. If you need to return the total table's row count, you can use an alternative way instead of the SELECT COUNT(*) statement.
Because the SELECT COUNT(*) statement makes a full table scan to return the total table's row count, it can take an extremely long time for large tables. There is another way to determine the total row count in a table. In this case, you can use the sysindexes system table. There is a ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2
This way, you can improve the speed of such queries by several times. See this article for more details: Alternative way to get the table's row count.

6. Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost performance. So, whenever possible, you should use constraints instead of triggers.

7. Use table variables instead of temporary tables.
Table variables require fewer locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.

8. Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so that they will contain only WHERE and GROUP BY clauses without the HAVING clause. This can improve the performance of your query.

9. Whenever possible, try to avoid using the DISTINCT clause.
Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is absolutely necessary.

10. 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, as your client will not receive the message indicating the number of rows affected by a T-SQL statement.

11. Use select statements with the TOP keyword or the SET ROWCOUNT statement if you need to return only the first n rows.
This can improve performance of your queries, as a smaller result set will be returned. This can also reduce the traffic between the server and the clients.

12. Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
You can quickly get the n rows and can work with them when the query continues execution and produces its full result set.

13. Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, while the UNION statement does look for duplicate rows, whether they exist or not.

14. Do not use optimizer hints in your queries.
Because the SQL Server query optimizer is very clever, it is highly unlikely that you can optimize your query by using optimizer hints; more often than not, this will hurt performance.

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

• Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

• Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

• Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

• Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

• Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
• Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
• Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

Introduction:

<html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>check uncheck all checkboxes in gridview using jquery</title> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"> </script> <script type="text/javascript"> $(document).ready(function() { $('[id$=chkHeader]').click(function() { $("[id$=chkChild]").attr('checked', this.checked); }); $("[id$=chkChild]").click(function() { if ($('[id$=chkChild]').length == $('[id$=chkChild]:checked').length) { $('[id$=chkHeader]').attr("checked", "checked"); } else{ $('[id$=chkHeader]').removeAttr("checked"); } }); }); </script> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="gvUserInfo" runat="server"> <HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" /> <Columns> <asp:TemplateField> <HeaderTemplate> <asp:CheckBox ID="chkHeader" runat="server" /> </HeaderTemplate> <ItemTemplate> <asp:CheckBox ID="chkChild"  runat="server"/> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> </div> </form> </body> </html>

using System; using System.Data; using System.Data.SqlClient;

protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindGridview(); } } // This method is used to bind gridview from database protected void BindGridview() { SqlConnection con = new SqlConnection("Data Source=ARYAN-PC\SQLEXPRESS;Integrated Security=true;Initial Catalog=SalmanTempDB"); con.Open(); SqlCommand cmd = new SqlCommand("select TOP 10 UserName,LastName,Location from UserInformation", con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); gvUserInfo.DataSource = ds; gvUserInfo.DataBind(); }

Imports System.Data Imports System.Data.SqlClient Partial Class VBCode Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If Not IsPostBack Then BindGridview() End If End Sub ' This method is used to bind gridview from database Protected Sub BindGridview() Dim con As New SqlConnection("Data Source=ARYAN-PC\SQLEXPRESS;Integrated Security=true;Initial Catalog=SalmanTempDB") con.Open() Dim cmd As New SqlCommand("select TOP 10 UserName,LastName,Location from UserInformation", con) Dim da As New SqlDataAdapter(cmd) Dim ds As New DataSet() da.Fill(ds) gvUserInfo.DataSource = ds gvUserInfo.DataBind() End Sub End Class