http://www.sqlservercentral.com/Content tagged T-SQL, SQL Server 7, 2000 posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)In this article Randy Dyness shows you how to avoid temp tables whenever to maximize the performance of your queries. http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/2006/06/02http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/When automating administrative actions for SQL Server, there are a number of ways to handle the workflow. Longtime SQL Server DBA Roy Carlson brings us a technique for using a text file as input to the standard SQL Server tools. http://www.sqlservercentral.com/articles/Miscellaneous/2801/2008/01/14http://www.sqlservercentral.com/articles/Miscellaneous/2801/New Author! Deadlocks aren't the most common problem but they happen they can really degrade performance. Shiv works through how to nail down what is causing the deadlock. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/2007/04/06http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/When automating administrative actions for SQL Server, there are a number of ways to handle the workflow. Longtime SQL Server DBA Roy Carlson brings us a technique for using a text file as input to the standard SQL Server tools. http://www.sqlservercentral.com/articles/Miscellaneous/2801/2008/01/14http://www.sqlservercentral.com/articles/Miscellaneous/2801/New author Jambu Krishnamurthy brings us a look at table variables and how they differ from temporary tables in SQL Server 2000. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2691/2006/11/21http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2691/Attaching and detaching databases is old hat these days right? Do you know how to reattach a database that has more than 16 files? Or do you know what happens if you try to reattach a database that had two log files but one is missing/deleted? And even if you know the answer to that - do you know how to fix it without restoring from backup? Maybe it's not ALL old hat just yet! http://www.sqlservercentral.com/articles/Administering/attachanddetachagain/656/2006/06/23http://www.sqlservercentral.com/articles/Administering/attachanddetachagain/656/In this article Randy Dyness shows you how to avoid temp tables whenever to maximize the performance of your queries. http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/2006/06/02http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/One of the major problems in the database field is when people store sensitive data unencrypted into SQL Server. This article shows you one of the most basic ways to encrypt data to the casual viewer. http://www.sqlservercentral.com/articles/Security/encryptfunction/372/2006/05/29http://www.sqlservercentral.com/articles/Security/encryptfunction/372/In the final installment of his series, Michael Coles examines a few other functions in his SQL Server 2000 toolkit to help you with other functions, like reading a directory on your server. Read the series and send some feedback to make this THE premier toolset for SQL Server 2000 DBAs. http://www.sqlservercentral.com/articles/Administering/sql2000dbatoolkitpart4/2364/2006/05/04http://www.sqlservercentral.com/articles/Administering/sql2000dbatoolkitpart4/2364/Joe discusses why he prefers command line tools over GUI, then launches into a how-to about querying sysperfinfo to resolve performance issues. Couple nice sample queries included! http://www.sqlservercentral.com/articles/Performance+Tuning/troubleshootingsqlserverwiththesysperfinfotable/987/2006/04/21http://www.sqlservercentral.com/articles/Performance+Tuning/troubleshootingsqlserverwiththesysperfinfotable/987/Part 2 of Steve Jones' series on beginning SQL Server from the perspective of a system administrator or someone not used to working with SQL Server. If you've been designated the new administrator, take a look at this series for some help in coming up to speed on this product. http://www.sqlservercentral.com/articles/Administering/beginningsqlserverlimityourqueries/1458/2006/04/14http://www.sqlservercentral.com/articles/Administering/beginningsqlserverlimityourqueries/1458/Performance tuning is an ongoing battle in SQL Server, but having a little knowledge up front when designing an application can greatly reduce the efforts. Do you know which performs better: stored procedures or triggers? There aren't many places where the two are interchangeable, but knowing the impacts of each might change the way you build an application. Read about this analysis by Vijaya Kumar. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/2006/03/31http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/One thing that often amazes me is that many SQL Server developers do not actually measure the performance of their queries. Many of them thought that you needed external tools, more or less complicated, to run against your server, and they did not have the time or inclination to learn and try these. This article will describe a couple of much easier methods of measuring performance of queries. http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/2005/12/23http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/How many of you use Execution Plans to tune your queries? Do you understand the impact of different indexes? Mr. Vijayakumar looks at his experiments with different types of indexes and their effects on the execution plan use. A great article for those of you that want to learn more about how you can tune your server for better performance. http://www.sqlservercentral.com/articles/Administering/executionplans/1345/2005/10/07http://www.sqlservercentral.com/articles/Administering/executionplans/1345/Do you know the difference between a login and a user? What's the best way to add them; Enterprise Manager, T-SQL, or SQL-DMO? In this beginner level article Andy demonstrates how to use all three methods to add logins and users and offers his view of which is the best technique. http://www.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/2005/09/30http://www.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/DTS is an incredible package for moving data in the SQL Server world. One feature that is missing, however, is the ability to send files using FTP to a remote server. This article looks at a technique for sending files via FTP. http://www.sqlservercentral.com/articles/Administering/pushftp/473/2005/07/01http://www.sqlservercentral.com/articles/Administering/pushftp/473/Performing routine database maintenance such as reindexing is important for keeping your databases running at peak performance. When you use INSERT, UPDATE, and DELETE statements to modify table data, indexes can become fragmented. Index fragmentation can happen when the logical sequence of pages is disrupted or when an index page contains less than its maximum amount of data, creating a gap in the data page or index. As indexes become fragmented, you get inefficient data reads when accessing tables and slower database performance. http://www.sqlservercentral.com/redirect/articles/1772/2005/03/10http://www.sqlservercentral.com/redirect/articles/1772/Do you use the maintenance plans or hate them? Wish they would do more? Curious about how they work under the hood? Cmon, you gotta read this one! Trust us, it's not another "how-to" article! Well, maybe just a little bit! http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/2005/03/04http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/Xp_sendmail is a great utility that SQL Server provides in order to e-mail messages directly from SQL Server, by using a valid MAPI profile. However, its syntax is rather stringent. In this article by Kunal Das, he shows you how to send messages to a list of recipients generated by a query dynamically in T-SQL. http://www.sqlservercentral.com/articles/Advanced+Querying/xp_sendmail/369/2005/02/18http://www.sqlservercentral.com/articles/Advanced+Querying/xp_sendmail/369/Mindy explores the metadata stored in SQL 2000 to show you how to produce a simple and useful data dictionary! http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/2005/02/04http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/In this article, Brian Kelley continues his series on Query Analyzer for SQL Server 2000. Query Analyzer offers a highly configurable integrated development environment (IDE). Some of the areas Brian looks at are how to modify the fonts and colors, set connection settings, choose scripting options, and customizing file and result set options. Learn how to make the most of the IDE and make it work for you. http://www.sqlservercentral.com/articles/Administering/qa2k_3/740/2005/01/07http://www.sqlservercentral.com/articles/Administering/qa2k_3/740/Brian Kelley continues his series on getting the most out of SQL Server 2000's Query Analyzer. In this article he looks at the Object Browser and the Transact-SQL Debugger, new features in the 2000 Edition which can reduce development and troubleshooting time for DBAs and database developers. http://www.sqlservercentral.com/articles/Administering/qa2k_2/622/2004/12/31http://www.sqlservercentral.com/articles/Administering/qa2k_2/622/So you open up the tool a hundred times a day. In this article by Brian Kelley, he shows you how to get the most out of some of the known and unknown features. http://www.sqlservercentral.com/articles/Administering/qa2k_1/614/2004/12/23http://www.sqlservercentral.com/articles/Administering/qa2k_1/614/One of the biggest performance gains built into SQL Server is the stored procedure. In this article by Brian Kelley, he shows you how to fully utilize, debug and monitor the caching of such objects. http://www.sqlservercentral.com/articles/Administering/procedurecache/591/2004/11/19http://www.sqlservercentral.com/articles/Administering/procedurecache/591/Performance tuning is an ongoing battle in SQL Server, but having a little knowledge up front when designing an application can greatly reduce the efforts. Do you know which performs better: stored procedures or triggers? There aren't many places where the two are interchangeable, but knowing the impacts of each might change the way you build an application. Read about this analysis by Vijaya Kumar. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/2006/03/31http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/A new article from MSDN magazine on stopping SQL Injection attacks by examining how the SQL can be exploited. It's a good basic article for developers as well as DBAs who might have to review code.http://www.sqlservercentral.com/redirect/articles/1470/2004/08/20http://www.sqlservercentral.com/redirect/articles/1470/Part 2 of Steve Jones' series on beginning SQL Server from the perspective of a system administrator or someone not used to working with SQL Server. If you've been designated the new administrator, take a look at this series for some help in coming up to speed on this product. http://www.sqlservercentral.com/articles/Administering/beginningsqlserverlimityourqueries/1458/2006/04/14http://www.sqlservercentral.com/articles/Administering/beginningsqlserverlimityourqueries/1458/SQL Server showplan is a great tool for analyzing queries and determining whether or not more performance tuning needs to be done. However working with showplan can be confusing and it takes some practice to learn the ins and outs of what is occurring. Joseph Sack looks at showplan and seven of the main things that he is aware of when looking at the results. If you've rarely or never used showplan, this is a great article to get started in learning how an experienced DBA views the output. http://www.sqlservercentral.com/articles/Performance+Tuning/sevenshowplanredflags/1425/2004/07/15http://www.sqlservercentral.com/articles/Performance+Tuning/sevenshowplanredflags/1425/In this follow up Andy looks into some of the options available when you're dealing with situations where you're joining a case sensitive column to a case insensitive column. We think after reading this you'll be ready to just say no! http://www.sqlservercentral.com/articles/Administering/casesensitivityinselectspart2/1412/2004/06/22http://www.sqlservercentral.com/articles/Administering/casesensitivityinselectspart2/1412/See what a reader had to say about Part 1, make sure you've read Part 2, then feel the pain as the author describes a lookup table that should have had unique values and doesn't. http://www.sqlservercentral.com/articles/Administering/casesensitivityinselectspart3/1388/2004/06/15http://www.sqlservercentral.com/articles/Administering/casesensitivityinselectspart3/1388/