Computer Tips

 
Visit us often.  Computer tips updated daily.  Click here to--> "Tell a friend" so they can get updated computer tips, too.  Please visit our clients, as they support the computer tips page.

If you would like to submit a tip send us an email with your tip to info@businesswebsitelinks.com.
  ______________________________________________________________

Get quick SQL Server 2005 reference information from any computer (SQL 2005)

• If you’re trying to get SQL Server 2005 information from any computer, you may find that the maze of various documentation-related links on the Microsoft website has—over time—gotten a bit confusing and time-consuming to navigate.
• To quickly get the documentation online, go to http://msdn.microsoft.com, but don’t click on the SQL Server link. This link takes you to a page that can keep you updated on new developments, but it can also take you around in loops looking for what you want. For general reference go here:
  • 1. Click on the Library link at the top.
  • 2. In the expandable tree that appears on the left, click on Servers and Enterprise Development.
  • 3. Under this, expand the nodes in the tree as follows: SQL Server | SQL Server 2005 | Product Documentation | SQL Server 2005 Product Documentation | SQL Server 2005 Books Online.
• On the other hand, you’re probably aware that the biggest problem with using MSDN is that the Search button searches information from all MSDN, not just reference pages from the SQL Server 2005 documentation.
  The good news is, Microsoft has recently instituted a scoped search feature that lets you search only for applicable articles. You can find this page here:
  • http://search.live.com/macros/sql_server_user_education/booksonline

What to do when SQL Server 2005 won’t accept remote connections

• When connecting to SQL Server from a remote computer, you may get an error message that contains the following sentence:
  • "When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections"
• This problem occurs because the Developer and Express editions of SQL Server 2005 don’t allow remote connections unless you specifically enable them to do so. In theory, if you’re using SQL Server on your development machine, you usually don’t have to connect remotely—and of course, allowing remote connections does increase your security risk. Nevertheless, if you have to connect remotely, you’ll want to know the secret to be able to do so.
• To allow remote connections:
  • 1.Go to Start | Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration.
  • 2.In the Surface Area Configuration window, click Surface Area Configuration for Services and Connections.
  • 3.If Database Engine isn’t expanded, expand it.
  • 4.Click on Remote Connections.
  • 5.Select Local and Remote Connections.
  • 6.Select the appropriate protocol.
  • 7.Restart the database. (If you choose, you can do this right in the Surface Area Configuration for Services and Connections utility.)
• Note: For security reasons, you may want to use only the most restrictive options in step 6. However, although you’d think that selecting TCP/IP alone would be the best option, certain programs, such as Microsoft Visual Studio, may actually try to connect using named pipes instead.
• These steps are sufficient to connect from a remote computer, as long as you can use the database server’s IP address in your connection string.
  However, sometimes you may also need to have the SQL Server Browser service running—and may even have to create an exception for this service in the firewall. The SQL Server Browser service exposes all database instance names and other connection information—which, unfortunately, may make your machine wide open to attack. Therefore, avoid running this service unless you absolutely have to.

Remember the ON clause when dropping triggers (SQL Server 2005)

• If you try to drop a database-wide or server-wide DDL or logon trigger with DROP TRIGGER trigger_name, SQL Server will be unable to find it. Remember to use the appropriate ON clause, as in:
  • drop TRIGGER bad_trigger
    ON ALL SERVER

Never hit the wrong database again in the Query window

• It’s easy to do. You create an object and later wonder why it’s not there. This can happen if you’re in Query Analyzer or the Query window in Microsoft SQL Server Management Studio and forget to select the correct database in the dropdown box. In some cases, you may have even selected the correct database, but if you’re working with SQL Server Express, it may connect you to the wrong database when you reconnect.
  To avoid this problem, get into the habit of starting your query pages with a Use statement, such as:
  • Use my_db
• Those two words may actually take less time to type than it takes to use the mouse and select the my_db database from the dropdown.
• Best of all, if you open a SQL file that you saved previously—or if the Query window reconnects you to the wrong database—the Use statement at the top of the page will ensure that you’re executing your SQL statements against the correct database.

Don’t let fancy CLR debugging slow you down

• There are a number of tools you can use for debugging CLR integration code in Visual Studio 2005, such as the test.sql page and a special CLR debugger. Unfortunately, each new tool comes with its own quirks and setup headaches. Our recommendation is to avoid using these fancy tools unless you need to troubleshoot why something that works in Visual Studio doesn’t work when you deploy it to SQL Server.
• Instead of relying on complex debugging setups, write your modules and classes in a regular project in Visual Studio and debug them there. Make sure your SQL Server stored procedures and functions are very simple; they only need to be a few lines. In those lines, you can call procedures in your modules and classes, where you do the serious work. Architecting your code this way makes code reuse much easier—as well as keeping you from having to debug more tools just to debug your code.

Develop more flexible, maintainable code by dynamically generating T-SQL

• One of the main techniques for achieving flexibility in any application is dynamic code generation. This can exist in many forms (server-side generation of HTML in a web application is one obvious example). We discussed what dynamic code generation may mean for an application using .NET and Microsoft SQL Server
• Read More About This Tip Click Here > SQL Server Tips

Beware views within views within views

• Views provide an abstraction layer that can simplify queries and make code more manageable. But if you aren't careful, too many nested views can negatively impact query performance. Views include overhead such as columns and joins that aren't needed for your specific query. This problem gets worse with nested views. Of course, when readability and ease of reuse are paramount, using views - even views within views - is perfectly fine. But when every second of performance is critical, consider tweaking your queries for more specialized usage, even at the cost of reusability.

Maintain local data integrity by identifying constraints in the DataTable

• A well-designed database will enforce primary and foreign key constraints, but why waste network bandwith trying to perform inserts, updates, or deletes in the database that will fail? We'll show you how to enforce and handle constraints in .NET to avoid passing any bad data onto the server.
• To keep your DataTable data as healthy as your database data, we'll:
  • Discuss why constraints are as important in your DataTable as they are in your database.
  • Help you create primary, unique, and foreign key constraints in a DataTable.
  • Show you how to alter a constraint's behavior to better support the rules defined on your database server.
  • Read More About This Tip Click Here > SQL Server Tips

Prevent mammoth numerical errors by forcing SQL Server to treat values as decimals

• What's 2 divided by 3? Anyone for 0 as the answer? Unfortunately, that's what you may get if you're not fully aware of how SQL Server treats numbers in queries.
• For example, try running the following query:
  • SELECT 2/3
• And the winner is ...
• Zero. The reason SQL Server returns 0 should be pretty obvious in this example. Both 2 and 3 are integers; therefore, SQL Server assumes the result should be an integer. Hence, it rounds down to the nearest integer, which is 0.
• Unfortunately, figuring out that this issue was the culprit may not be so easy with a more complicated expression inside a complex application. Instead, your end result may be something quite bizarre. Worse still, nobody may even question the critically wrong result.
• A quick and dirty way to fix this problem is to force SQL Server to think in decimals by adding trailing zeros, like this:
  • SELECT 2.0/3.0
• In fact, you only have to do this with either one of the two operands, and SQL Server will still return the same value: .666666. (You could call this math problem "the Devil's division.") Add several more zeros after the decimal, and SQL Server will reserve more memory and thus return more sixes after the decimal.
• One advantage of adding trailing zeros is that it lets you avoid bogging down your queries with too many queries. When generating SQL, your application code can add the zeros automatically. (Just make sure it never adds zeros when there's no decimal!)
• Of course, the alternative is to use the CAST() or CONVERT() functions to convert to data types such as decimal, float, or real, like so:
  • SELECT cast(2 as decimal)
    /cast(3 as decimal)
• Curiously, when you use the CAST() function for both numbers, SQL Server returns .6666666666666666666, whereas using it for only one of them still gives you just 6 sixes. So just be aware that SQL Server calculates precision a little differently depending on whether you use trailing zeros or functions.
   

Avoid PCI sanctions and keep transaction fees low with these compliance tips

• If your database and other components in your network don't meet PCI DSS 1.1 requirements, your company may miss out on lower credit card rates, face hefty fines, or even be barred from accepting credit card transactions. We'll summarize what you need to know about these requirements, and how to get your company compliant quickly.
• To help you deal effectively with PCI DSS requirements, we'll:
  • Provide a clear view of the business context of PCI compliance requirements you have to meet and how these effect database management.
  • Show how to convince others in your organization of the need for compliance if you're not getting the resources and support you need to meet key deadlines.
  • Explain how to become compliant even when some requirements seem impossible to meet.
  • Read More About This Tip Click Here > SQL Server Tips

Tip: Connect to SQL Server without the client tools using Visual Studio

• If you generally connect to the database with the Enterprise Manager, the Query Analyzer, or other client tools that come when you install SQL Server, you may be at loss for what to do if you have an urgent need to connect and the only machine available doesn't have these tools.
• Fortunately, if the machine has Microsoft Visual Studio installed, you still have a pretty nice interface for working with SQL Server.
• To connect to the database:
  • 1. Navigate to View | Server Explorer.
  • 2. In the Server Explorer window, right-click on Data Connections.
  • 3. From the shortcut menu that appears, select Add Connection.
  • 4. In the Data Connection dialog box that appears, enter the server name and connection information.
  • 5. Click the Test Connection button to make sure that you were able to connect, and click OK to add the connection.
• You can add objects to the database by right-clicking on the names of various object types in the tree view that appears in the Server Explorer.
• To execute a query against the database, you can simply create a new view and close it without saving.

Tip: Get the database login information pronto from the application code (ASP.NET)

• So, you're asked to fix a database problem for a web application. The trouble is that nobody can tell you a valid database username and password - or even where the database is! You could spend hours searching the application code and tracking down everyone in your company who you think might know.
• If the application happens to be written in ASP.NET, there's a good chance that all this information is in a file at the top level of the application's web directory called web.config; the database connection string may even look something like this:
  • <add key="MyApp.DataAccess.ConnectionString"
    value="Provider=SQLOLEDB;server=34.329.169.201;uid=doodle1;
    pwd=$%^ytr;database=larry;Connection Reset=FALSE" />
• That's terrible for security, of course, but since someone else wrote the program anyway, at least it's nice that you're able to find the information and get on with your assignment.
• If the application is in ASP classic, try looking in global.asa. Otherwise, search the source code for phrases such as conn, password, uid, provider, SQLOLEDB, and so forth.

Tip: If the correct fix doesn't work, look at the permissions

• Sometimes the most time-consuming part of fixing a bug occurs after you've figured out how to fix it. Let's say, for example, that you pinpoint the cause of a problem to a line of code that calls a certain stored procedure. To avoid causing other parts of the application to fail, you decide not to disturb the existing procedure. Instead, you create a new one for the specific line that doesn't work.
• You implement the fix and then test it out. The behavior is exactly the same as before. You're tempted to tear your hair out!
• A common reason for this sort of scenario is forgetting to set the appropriate user permissions to the object you created. You can manage these permissions by exploring the properties of your object in the Enterprise Manager or SQL Server Management Studio. Generally, examining the permissions for other similar database objects will tell you which user account the application is using.
• So repeat ten times over: If you created an object, set the permissions! Remembering this simple principle may save you hours of frustration.

Tip: If the correct fix doesn't work, look at the permissions

• Sometimes the most time-consuming part of fixing a bug occurs after you've figured out how to fix it. Let's say, for example, that you pinpoint the cause of a problem to a line of code that calls a certain stored procedure. To avoid causing other parts of the application to fail, you decide not to disturb the existing procedure. Instead, you create a new one for the specific line that doesn't work.
• You implement the fix and then test it out. The behavior is exactly the same as before. You're tempted to tear your hair out!
• A common reason for this sort of scenario is forgetting to set the appropriate user permissions to the object you created. You can manage these permissions by exploring the properties of your object in the Enterprise Manager or SQL Server Management Studio. Generally, examining the permissions for other similar database objects will tell you which user account the application is using.
• So repeat ten times over: If you created an object, set the permissions! Remembering this simple principle may save you hours of frustration.

Tip: Watch out for imprecise floating point values in your SQL Server data

• Two SQL Server data types that are often used to represent numeric values with a fractional (that is, decimal) component--float and real--can get you into trouble if you aren’t paying attention. In SQL Server, floating point values are approximate--and shouldn’t be used if you need absolute precision. Surprised? Don’t feel bad--many DBA’s are unaware of this, but a simple experiment proves the point.
• Try running the following query and you’ll see what we’re talking about:
  • DECLARE @TestValue float -- or real
    SET @TestValue = 6.9
  •  
  • SELECT TestValue = @TestValue
• And you won’t see the result you might expect (6.9). Rather, you see 6.9000000000000004 for float or 6.9000001 for real. (Remember that real is just a synonym for float(24)).
• Why does this occur? According to SQL BOL, floating point data is approximate by design and “not all values in the data type range can be precisely represented.” So what do you do when you need precise fractional values? Use decimal instead.

Tip: Programmatically determine which named instance you’re connected to (SQL Server 2000/2005)

• If you’re using SQL Server named instances, you may wonder how to determine--from inside a stored procedure, for example--which instances you’re connected to at any given time. Actually, there are two ways, and both are quite simple.
• First, you can use the system-supplied global variable @@SERVERNAME. Go ahead and connect to a named instance, then try the following statement:
  • SELECT InstanceName = @@SERVERNAME
• As you’ll observe, the result consists of the physical server name, followed by a backslash, followed by the name of the instance. It’s also a simple matter to parse out just the instance name, using the available T-SQL string functions, if you need to.
• The other, and essentially equivalent, method is to use the built-in SERVERPROPERTY function. Here’s an example, which should produce the same results as our previous demonstration:
  • SELECT InstanceName = SERVERPROPERTY('ServerName')
• For more information on these two options, see the appropriate entries in SQL Server Books Online.

Tip: Temp tables sometimes trump table variables (SQL Server 2000)

• Table variables are one of the great innovations included with SQL Server 2000. You can use them almost anywhere you’d use a temp table, but with fewer locking issues and better memory management. Notice we said almost anywhere. Unfortunately, there’s one common scenario where table variables just aren’t allowed.
• How often do you find yourself using temporary tables to store the resultset from a stored procedure, as shown at the below, so that you can filter it, manipulate it, or join it to other tables? Odds are, pretty often.
• Piping the results of a stored procedure call into a temporary table
  -- The right way!
  • CREATE TABLE #MostExpensiveProducts (
    TenMostExpensiveProducts nvarchar(100),
    UnitPrice money)
  • INSERT INTO #MostExpensiveProducts
    EXEC [Ten Most Expensive Products]
  • SELECT * FROM #MostExpensiveProducts
    WHERE UnitPrice > 150
  • -- The wrong way!
  • DECLARE @MostExpensiveProducts table (
    TenMostExpensiveProducts nvarchar(100),
    UnitPrice money)
  • INSERT INTO @MostExpensiveProducts
    EXEC [Ten Most Expensive Products]
• But you can’t replace the temporary table in this scenario with a table variable. Just try getting as far as the code at the bottom of the code, and SQL Server throws up an immediate and impassible roadblock. And that’s not all: You also can’t use a table variable in the similar syntax, SELECT * INTO @MyTableVariable FROM MyPermanentTable. It’s a bummer, of course, but you may as well learn to live with it! Table variables just aren’t the perfect solution every time.

Tip: Authenticate digital signatures with PGP

• Electronic documents and email messages are becoming a commonplace way to conduct business transactions, but it’s important to be able to verify that the author of a document or message is really the person he or she claims to be.
• You can use digital signatures to verify identity. This is easy to do with programs such as Pretty Good Privacy (PGP). PGP is based on a public/private key pair; you sign the document by encrypting it with your private key, to which only you have access. The recipient uses your public key to decrypt it. Note that this doesn’t provide data confidentiality because the public key is available to everyone. It does, however, ensure that it was really you who signed it, because no one but you has the private key that’s paired with that public key.
• PGP is available in both freeware and commercial versions. You can get the commercial version at www.pgp.com or download the free version for Windows XP at www.pgpi.org/products/pgp/versions/freeware/winxp/8.0/.
• There are also versions for earlier Windows operating systems, UNIX, Mac, and even MS-DOS, OS/2, and Palm OS.

Tip: Don’t let date-related queries miss important records

• You may think that querying the records between two dates is a simple process. But if you forget to consider both the date and time values, you may not get the results you want. And because such data omissions usually don’t cause software errors, they may never get fixed, and nobody will ever know what caused, say, someone’s medical records or banking transactions to go missing.
• For instance, consider the following query:
  • SELECT *
  • FROM dbo.tblMeetings
  • WHERE MeetingTime
  • BETWEEN '10/11/2006' AND '10/12/2006'
• Although at first glance this query appears as if it will return records for 10/11 and 10/12, it actually only returns records for 10/11.
• SQL Server defaults to 0:00:00 AM because the query doesn’t specify a time. In other words, the BETWEEN clause evaluates to this:
  • BETWEEN '10/11/2006 00:00 AM' AND '10/12/2006 00:00 AM'
• As a result, the query returns only those meetings that fall on October 11 because 00:00:00 AM is the beginning of October 12, not the end. By not specifying a time, your query eliminates an entire day from the results.
• You can avoid the above error by explicitly entering a time value. For instance, the following query includes all records for the two days:
  • SELECT *
  • FROM dbo.tblMeetings
  • WHERE MeetingTime
  • BETWEEN '10/11/2006 00:00 AM'
  • AND '10/12/2006 11:59:59.998 PM'
• A few important things to notice:
  • * BETWEEN is inclusive, so the query returns records that exactly match 10/11/2006 00:00 AM and 10/12/2006 11:59:59.998 PM (as well as everything in between).
  • * Just specifying 11:59 PM may not be good enough! You’ll miss records marked 11:59:01, for example. The data loss may so intermittent that the debugging team take days to locate the source of the problem.
  • * 11:59:59.999 PM evaluates to 12:00 AM the next day, so 11:59:59.998 PM is the highest possible time value for a day.
  • To avoid messing with hours, minutes, seconds, and fractions of seconds, you can consider the following somewhat cleaner alternative:
    • WHERE MeetingTime
    • >= '10/11/2006 00:00 AM'
    • AND MeetingTime
    • <'10/13/2006 00:00 AM'

Tip: Protect the physical security of your network

• When it comes to protecting your servers and the data they hold, physical security is the first line of defense--but one that’s often overlooked. No matter how many firewalls you deploy to keep out internet intruders or how many access controls and authentication schemes you implement to prevent intrusions from internal computers, your servers are vulnerable if just anyone can walk into the room and sit down at the machine with an administrative account already conveniently logged in.
• Believe it or not, that’s the case for many small (and some not so small) businesses. When assessing your network’s security, be sure to take a look at the physical security (or lack thereof) surrounding your critical servers.
• Servers should always be located in a locked room or closet. Only authorized personnel should have access. Perhaps you think you have your servers locked up tight, but who has access to the key to that lock? If it’s kept on a hook or in an unlocked desk drawer nearby, the answer is “just about anybody.” What about janitorial staff? How hard would it be for a determined business competitor or saboteur to pose as a cleaning person and obtain physical access to the machines?
• Even if the door is locked, locks can be breached. When the servers are left alone, you should log off or “lock” the machine before you go. Don’t depend on screensaver password protection that doesn’t kick in for several minutes--that may be long enough for someone to sneak in and gain access.
• Consider the arrangement of servers and especially monitors and keyboards within the room. What about those nice glass windows that allow people outside the room to see the screen display or observe the passwords as they’re typed? Rearrange components so this doesn’t happen.
• The servers aren’t the only network devices that you need to protect. A hub or switch that’s not locked up can make it possible for an intruder to surreptitiously plug a laptop computer into the network.
• Even the cables are vulnerable. With the right equipment, a hacker can intercept the data signals traveling on the wire. Be sure you consider all these factors when taking stock of your network’s physical security.

Tip: Quickly discover the meaning of an error number

• Sometimes a client or software program may report a SQL Server error number without the description. For example, you may see a reference to Msg 109, or some other cryptic number.
• To determine the error, simply query the sysmessages table in the master database. For example, to find out what Msg 109 means, issue the following query:
  • SELECT *
  • FROM master.dbo.sysmessages
  • WHERE error = 109

Tip: Force SQL Server to twiddle its thumbs (7.0/2000)

• There are certain scenarios where you want SQL Server to sit idly, doing nothing, for a prescribed interval or until a prescribed time. For example, your T-SQL batch might implement some sort of asynchronous process which requires the rest of the code to wait a certain amount of time for that process to finish. Or, you may find that introducing small delays help you to deal with intermittent locking problems. In any case, Transact-SQL makes it possible to wait both a certain *amount* of time as well as to wait *until* a certain time.
• To cause your SQL Server to wait until a particular interval has elapsed, use the WAITFOR DELAY statement. For example, to cause SQL Server to wait until five seconds have passed, you'd use WAITFOR DELAY '00:00:05'. To wait for an hour, you'd use WAITFOR DELAY '01:00:00'.
• To wait until a particular *time* of day, use the WAITFOR TIME statement. It works in basically the same way. WAITFOR TIME '09:00' suspends your code until 9:00 AM. Similarly, WAITFOR TIME '23:59:59:100' causes SQL Server to wait until one tenth of one second before midnight.
• Also, a caveat: Try to avoid using the WAITFOR statement inside a transaction, unless it's a very, very short interval. The WAITFOR process holds all locks until the interval has elapsed; and if your interval turns out to be lengthy, your server performance will suffer accordingly.

Tip: Returning a variable number of rows in SQL Server (7.0/2000)

• We all know how useful the TOP clause is for returning only the top n rows from a query. For example, to return just the top 3 employees in the Northwind database, you could use:
  • -- This works (of course)
  • SELECT TOP 3 *
  • FROM Employees
• But what if the number of rows you want to return needs to be dynamic, varying based on some other conditions. You might try something like this, without success:
  • -- This does *not* work
  • DECLARE @Rows int
  • SET @Rows = 3
  •  
  • SELECT TOP @Rows *
  • FROM Employees
• So, how can you address this challenge? The answer is to remember the ANSI SQL standard SET ROWCOUNT statement, for which the TOP clause is a Microsoft-specific alternative. The following code accomplishes the desired results:
  • -- But this *does* work
  • DECLARE @Rows int
  • SET @Rows = 3
  •  
  • SET ROWCOUNT @Rows
  • SELECT *
  • FROM Employees
  • SET ROWCOUNT 0

Tip: Another method for monitoring for specific events or conditions in SQL Server (7.0/2000)

• In a previous tip, we talked about how you can use the WAITFOR statement as an event handler. Another method is to enter a (deliberately) endless WHILE loop, then check for your event condition inside the loop, breaking out of it when the condition is met. Here's how it works. Suppose you want your code to continue only when new Orders records come into your database. You might use a loop like this:
  • WHILE 1 = 1
  • BEGIN
  • -- Always true, so we'll stay here until ...
  • IF (SELECT COUNT(*) FROM Orders (NOLOCK)
  • WHERE DATEDIFF(dd, OrderDate, GETDATE()) = 0) > 0
  • BREAK -- Exit the otherwise endless loop
  • END
• We'll only ever get to this point once there are records that satisfy the conditions
  • SELECT * FROM Orders
  • WHERE DATEDIFF(dd, OrderDate, GETDATE()) = 0
• If you execute this code, your batch will sit idly monitoring for the specified conditions, only breaking out when they're true. With this code running in one Query Analyzer window, open another and insert a new row:
  • INSERT INTO Orders (OrderDate) VALUES (GETDATE())
• Now, switch back to your original window, and within a moment or two, you'll see that you escape the loop and your code continues as desired. Notice that we provided the NOLOCK table hint to speed up the repeated query.

Tip: Identify your SQL Server applications (SQL Server 2000)

• With ADO and ADO.NET, you can specify an application name by including it as one of the properties in the SQL connection string, like this:
  • ...;Application Name=MyDbApp;...
• When you do, you get the benefit of being able to see this value clearly shown in SQL Profiler traces. You can even perform filtering in your trace to narrow the information you have to examine to only what's relevant to your own application.

Tip: Habla espanol? Parlez-vous francais, SQL Server? (SQL Server 2000)

• It's easy to investigate the languages that SQL Server supports. Just execute the system stored procedure sp_helplanguage without any parameters for a complete list of supported languages. The results will include the language id, its format for representing dates, which day of the week comes first in the local culture, and--perhaps most interestingly--the names of the language and its months and days of the week *in* each language.
• You can also pass the name of a particular language as a parameter of the procedure to return only the details of that particular language. This can be either name or alias column from the master list of languages (that is, the list returned by the procedure without arguments). For example, both of the following are equivalent ways to ask for SQL Server's information about Danish:
  • EXEC sp_helplanguage danish
  • EXEC sp_helplanguage dansk

Tip: Change the first day of the week for SQL Server (SQL Server 2000)

• You've probably seen those business calendars that show Monday as the first day of the week, right? But SQL Server assumes Sunday is the first day of the week, by default. Is it possible to change this to fit your own business needs--such as resetting the first day to Monday? Yes, it's not only possible, but it's quite easy. You can use the SET DATEFIRST system configuration function to do this. To set Monday as the first day of the week for the purposes of SQL Server date manipulations, just use this statement:
  • SET DATEFIRST 1 -- Monday; Use 2 for Tuesday, 3 for Wednesday, etc.
• Then, you can also check this value programmatically by using the built-in @@DATEFIRST variable. Once you've changed the internal first day of the week, SQL Server uses this value for all its day-of-the-week date calculations. For example, try out the following code (on a Monday):
  • SET DATEFIRST 1 -- Monday (which is today)
  • SELECT Today = DATEPART(dw, GETDATE()) -- Returns 1
  •  
  • SET DATEFIRST 7 -- Back to Sunday, the default (but today is still Monday)
  • SELECT Today = DATEPART(dw, GETDATE()) -- Returns 2

Tip: What else does @@VERSION show you about your SQL Server? (SQL Server 2000)

• In our last tip, we talked about how you could determine whether your SQL Server is a 32- or 64-bit installation using information returned in the @@VERSION global variable. But that's not all you can learn from this very useful built-in feature. Take a look at this sample:
  • SELECT VersionInfo = @@VERSION
• Returns:
  • Microsoft SQL Server 2000 - 8.00.780 (Intel X86)
  • Mar 3 2003 10:28:28
  • Copyright (c) 1988-2003 Microsoft Corporation
  • Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
• We added the carriage returns for readability. As you can see, you get the version of SQL Server, the processor, the current server date and time, the SQL Server edition (standard, developer, enterprise, etc.), and detailed version information for the operating system SQL Server is running on. All highly valuable information!

Tip: Determine programmatically whether your SQL Server is 32-bit or 64-bit (SQL Server 2000)

• There may be times when you need to ascertain whether the SQL Server executing your code is a 32-bit or 64-bit installation. Fortunately, this isn't difficult to do. The built-in global variable, @@version, actually returns this information--if you know what you're looking for. For 32-bit installations, the key is to find "Intel X86" in the version information, while for a 64-bit installation, you're looking for "Intel IA-64." The following code snippet (which you could easily adapt into a user-defined function) demonstrates the technique:
  • SELECT AddressSize =
  • CASE
  • WHEN CHARINDEX('Intel X86', @@VERSION) <> 0 THEN '32-bit'
  • WHEN CHARINDEX('Intel IA-64', @@VERSION) <> 0 THEN '64-bit'
  • ELSE 'Unknown'
  • END

Tip: What to do if you can't see running DTS packages on SQL Servers from Management Studio (SQL Server 2005)

• With SQL Server 2005, the ability to monitor, start, and stop DTS packages on remote SQL Servers is now controlled by a new Windows service. The DTS Service exposes all the functionality required to use Management Studio to kick off, interrupt, or monitor running packages, but it's disabled by default. In that state, you won't be able to access remote servers' DTS packages. To turn it on, locate the DTS Service in the Services applet, right-click on the service, and choose Properties. In the Properties dialog box, you can start the service--and if you like, you can set it to start automatically every time you restart your computer.

Tip: SQL Server 2005 brings WMI data and events to your DTS packages (SQL Server 2005)

• Data Transformation Services (DTS) has been completely redesigned from the ground up for SQL Server 2005. Along the way, you'll acquire a number of new types of DTS tasks. Two of the most interesting allow you to tap into the power of Windows Management Instrumentation (WMI). The WMI Data task gives you access to the current values of WMI properties throughout its hundreds of classes. For instance, your DTS packages now has a way to determine at run-time that SQL Server is, in fact, running, or that a D:\ drive exists on the target computer, or that it has sufficient disk space for a table dump.
• The WMI Event task is similar, except that it works by leveraging system events that WMI exposes. For instance, a task can idle, transferring control to the next DTS task only when memory usage falls below a certain threshold or when a needed file has actually been created on a local drive. These two new task types can bring dramatic new avenues of control and responsiveness to your DTS packages.

Tip: When to use CLR integration for stored procedures in SQL Server 2005: Part 3 (SQL Server 2005)

• In previous tips, we've examined some of the differences between CLR-based and T-SQL-based stored procedures, with a particular focus on performance. So far, we've identified two common scenarios. In one, where you're primarily carrying out one or more straightforward DML operations, we decided that T-SQL code was preferable. In the other, where you need to perform one or more actions for each row in a result set, we suggested that .NET-based code was the better choice. But what if you have a mixed scenario, where your procedure needs to accomplish several tasks that fall into both categories? A good choice here, and if it's possible, is to separate the logic such that a T-SQL procedure carries out the basic DML, and then a CLR-based procedure calls that T-SQL procedure to derive its working data, and continues with .NET code to carry out its more intensive activies. The compromise can squeeze precious performance out of your database application.

Tip: When to use CLR integration for stored procedures in SQL Server 2005: Part 2 (SQL Server 2005)

• We're looking at some of the differences between implementing stored procedure code in T-SQL versus .NET in SQL Server 2005. Here's another situation where we can make a pretty clear determination between the two. Imagine your procedure retrieves a result set and then carries out some action or set of actions for each row in it. In versions of SQL Server prior to 2005, you'd normally consider a cursor for this kind of functionality. However, as you know, cursors bring notoriously bad performance. With CLR integration, you have a much better alternative. Implement a forward-only, read-only result set using .NET code and carry out the necessary activities using a CLR-based procedure. This will generally outperform the equivalent T-SQL.

Tip: When to use CLR integration for stored procedures in SQL Server 2005: Part 1 (SQL Server 2005)

• As you've probably been hearing more and more, SQL Server 2005 (formerly code-named Yukon) provides the ability to write stored procedures in .NET languages, such as Visual Basic .NET or C# in addition to T-SQL. But, as with most competing solutions, there are pros and cons to using one approach or the other in a given situation. In our next several tips, we'll offer a few insights into choosing between a purely T-SQL implementation versus a .NET CLR-based approach.
• For today, if the logic behind your stored procedure consists mainly of fairly direct and simple DML statements (INSERT, SELECT, UPDATE, and similar operations), you'd probably do better to develop the code using T-SQL. The reason is that CLR-based implementations require additional overhead, as they must communicate back and forth between the CLR and SQL Server, marshalling data between them. This overhead can easily add up to much more than the processor time required for straightforward DML.

Tip: Performance tips: Consider a covering index for frequent queries (SQL Server 7/2000)

• If you have a particular query that you run more often than just about any other, and the columns in its SELECT and WHERE clauses don't tend to change from execution to execution, consider creating a covering index on those columns. A covering index is one that contains all the required columns in a single index. The benefit here is that all the necessary columns can be returned by the index alone without requiring another lookup in the base table. And because it's an index, these values are already in the correct order for the most effective searching and sorting possible.
• Use the CREATE INDEX statement to do this. For example, in the Northwind database, you might use something like this:
  • CREATE NONCLUSTERED INDEX ix_CustomersCovering ON dbo.Customers
  • (
  • CustomerID,
  • CompanyName,
  • ContactName,
  • ContactTitle,
  • Country
  • ) ON [PRIMARY]
  • GO
• You'll want to test your performance to make sure you're getting the results you want. Try to limit the number of columns you cover to the smallest reasonable set your applications actually need. The wider an index gets, the more expensive INSERT and UPDATE statements can become.

Tip: Performance tips: Watch out for implicit data type conversions (SQL Server 7/2000)

• Many times, your queries may contain implicit conversions between data types, even without you realizing it. One of the most common causes is conversion to and from Unicode. For instance, the Customers table in the Northwind database defines its CompanyName column (among others) as Unicode, because of the multilingual nature of the data it contains. But how often have you written a query like this?
  • SELECT *
  • FROM NorthWind.dbo.Customers
  • WHERE CompanyName = 'Frankenversand'
• While it works perfectly, SQL Server actually has to make a conversion to Unicode for the CompanyName. Sometimes this results in a table scan even when perfectly valid indexes could be used. The proper way to write this query, and to save yourself valuable processing cycles, is like this:
  • SELECT *
  • FROM NorthWind.dbo.Customers
  • WHERE CompanyName = N'Frankenversand'
• Bear in mind that local variables can play into this scenario as well. For each individual query, the savings may be fairly small, but as your traffic scales out, the savings can really add up to better performance.

Tip: Performance tips: Make sure you define your constraints (SQL Server 7/2000) 

• Unique constraints and check constraints are often overlooked during database design. Oftentimes, DBAs don't think about them until it's too late and the tables in question already contain a lot of data. By that time, it may be difficult or even impossible to sufficiently massage the data to allow for the creation of effective constraints. But having these constraints in place from the very beginning can improve the performance of your queries. This is because the rules embodied by such constraints provides more information to the query optimizer. For example, check constraints can indicate whether an index is needed to find a resultset, while a unique constraint can give the optimizer hints about the *kind* of results you expect.
• Here's a more concrete example. The Northwind database contains a table called Order Details. That table defines a check constraint requiring that the Quantity column contain a number greater than 0. So, when you run a query like this:
  • SELECT * FROM [Order Details] WHERE Quantity = 0
• The query optimizer knows that SQL Server doesn't even have to *look* at the data to return 0 rows, because the constraint makes this impossible. Therefore, the query can return immediately, with no physical reads at all (which you can verify by examining the statistics of this query).

Tip: Sorting stored procedures by Create Date in Enterprise Manager may not work! (SQL Server 2000)

• Here's a puzzler that's just come to our attention: Open up Enterprise Manager (EM), drill down to a database on your SQL Server, and select the Tables node. Now, in the right panel, try sorting the list of tables in ascending and descending order. Works just fine, right? But now select the Stored Procedures node and try the same thing. Look closely. If your system is like ours, it simply doesn't work at all! Despite the fact that EM claims to be sorting by the Create Date column, we noticed that the dates are all out of order! And the same is true for Views. And User Defined Functions. And Rules. In fact, the only collection we saw sorting correctly was Tables. And it gets weirder: Continue clicking the column header and you'll notice that the sort order is *different* every time! EM is basically just randomizing the list (or at least, it looks random to those of us whose native language isn't binary).
• Why is this? As yet, we have no answer to this mystery--if anybody reading this *does* know, please drop us a line at info@businesswebsitelinks.com.
• In the meantime, if you need to sort your stored procedures by their creation date, there's another way: using Query Analyzer. The following query will accomplish the task:
  • SELECT *
  • FROM sysobjects
  • WHERE xtype = 'P' /* Change the xtype for other database
• objects */
  • ORDER BY create DESC

Tip: Store the results of a SQL query in a global DTS variable (SQL Server 2000)

• Did you know that you can funnel all the rows of a query resultset into a global DTS variable? This can be beneficial in that subsequent tasks in your DTS package can process the data in many different ways, working on the variable in memory. The flip-side of this, of course, is that you want to be careful about storing *huge* resultsets in such variables. But given that warning, this technique can still be quite valuable. Here's how to do it.
• Create an Execute SQL Task in the DTS designer. Select a SQL connection and enter a T-SQL query of some sort--for example, a connection to the Northwind database and a SELECT * FROM Employees will do nicely. Then, click the Parameters button at the bottom of the Execute SQL Task Properties dialog box. Click Create Global Variables. In the Global Variables dialog box, enter a name for the variable, such as Employees, and leave the type as <other>. Then, click OK. Back in the Parameter Mapping dialog box, now select the Output Parameters tab. Under Output Parameter Type, select Rowset. Then, choose the Employees global variable in the dropdown list. Click OK twice and you're all set. You can now work with the results of your query, using the global variable, in any downstream DTS tasks.

Tip: Start SQL Server 2000 from the command line to troubleshoot start-up problems (SQL Server 2000)

• There may be occasions where SQL Server becomes configured in such a way that it's unable to start at all. For example, you may have written a start-up procedure that fails under certain circumstances, preventing SQL Server itself from starting. Hopefully, this will never happen to you, but if it does, you can start SQL Server from the command line with minimal configuration (including bypassing start-up procedures). Simply invoke the sqlservr.exe application from a command prompt window with the -f switch. This switch tells SQL Server to use minimal configuration settings. You can then run sp_configure to disable the offending procedure. Two other switches you may find useful are the following:
  • -m, which starts SQL Server is single-user mode
  • -c, which starts SQL Server independently of the MSSQLSERVER service.

Tip: Use the SQLDIAG utility for a snapshot of your SQL Server's health (SQL Server 7.0/2000)

• An often overlooked command-line utility that comes with SQL Server, SQLDIAG, can help you to troubleshoot problems or just keep an eye on your server's health.  It gathers error log information, registry settings, lock information, details about running processes and extended stored procedures, input buffers, head blockers, and much more.
• It lives in the ...\MSSQL\Binn folder.  You can invoke it with a number of command-line options (see Books Online for details).  But at a minimum, you'll generally pass the -U and -P switches, which allow you to specify the User ID and Password to connect with, respectively.

Tip: Watch your server resources in data-driven .NET applications (SQL Server 7.0/2000)

• Practically every time you turn around, you see a new article or technique about the richness and power of the a DataSet object or the DataGrid server control.  But you should bear in mind that, while these objects can certainly bring an incredible wealth of features and flexibility to your .NET applications, they come at a cost:  Performance.  The simple factr of the matter is that ASP.NET server controls take time and resources to instantiate and use.  Likewise, the DataSet object brings with it considerable overhead, too.  Now, we aren't advising you against using these valuable objects; rather, consider the needs of your application.  If your only task is to retrieve and display read-only data, you're making a mistake using a DataSet.  Use a DataReader instead.  Likewise, if you don't need the full complement of interactive features that comes with the DataGrid, consider a more lightweight server control, or don't use a server control at all.  The lesson to learn is not to take on the overhead for features and functions that you don't really need.  this can make a substantial difference in the performance of your applications, especially under load.

TO VISIT BUSINESS WEBSITE LINKS' INTERNET DIRECTORY
CLICK HERE---->INTERNET DIRECTORY ONLINE.COM