 | |||
|
|
||
| |||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| FAQ: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1. When should I use a cluster index vs. a non-cluster index? Check out my indexing guide on The art and science of SQL Server indexing. You can also check out the following resources:
Return to SQL Server comparison features FAQs 2. What is the difference between nvarchar and varchar in SQL Server 2000?
SQL Server provides both datatypes to store character information. For
the most part the two datatypes are identical in how you would work with
them within SQL Server or from an application. The difference is that
nvarchar is used to store unicode data, which is used to store
multilingual data in your database tables. Other languages have an
extended set of character codes that need to be saved and this datatype
allows for this extension. If your database will not be storing
multilingual data you should use the varchar datatype instead. The
reason for this is that nvarchar takes twice as much space as varchar,
this is because of the need to store the extended character codes for
other languages.
Return to SQL Server comparison features FAQs 3. Can you explain the differences between DTS and SSIS? Can you explain the differences between Data Transformation Services (DTS) and SQL Server Integration Services (SSIS)? SSIS offers so much more than DTS. Rather than view it as the next version of DTS, I view it instead as version 1 of a new product. Explaining the differences between SQL Server 2000's DTS and SQL Server 2005's Integration Services requires either a book or an entire dedicated Web site. In fact, I just purchased Professional SQL Server 2005 Integration Services by Wrox and it looks great. I've also got some great examples and tons of information including many sample packages on sqlis.com. Finally, you can also find webcasts, articles and blogs by the Microsoft SSIS development/management team here.
Return to SQL Server comparison features FAQs 4. How do CLR stored procedures and functions perform vs. those in T-SQL? Here is the general performance rule when comparing T-SQL routines to equivalent CLR routines: Test both configurations with your data on your servers and figure out which one is better. That said, many people have run performance tests and the general consensus is that T-SQL will always perform better for standard CRUD (Create, Read, Update, Delete) operations, whereas CLR code will perform better for complex math, string manipulation and other tasks that go beyond data access. SQL Server MVP Gustavo Larriera has compiled the following list of useful links with more information on this topic:
—Adam Machanic, SQL Server 2005 Expert Return to SQL Server comparison features FAQs 5. When should you use active/active and active/passive clustering? I am looking at clustering. My only concern is whether to go active/active or active/passive. I understand active/passive would basically mean that one machine won't do anything. But going active/active increases the complexity of the solution. We'd then have SQL clustering a/a, san, hba, etc. What would you suggest? I am moving from four servers with an independent database running on dual/quad 1GHZ server with 2GB of ram. The application server will remain, but the database will be consolidated on a single quad 3GHZ Xeon with 4GB of memory. I would recommend documenting the business requirements and then researching all of the SQL Server high availability options in the market from Microsoft and other vendors. Over the last 12 months many new products have been introduced which may provide you with the solution to meet your business needs. I would also research the options available with SQL Server 2000 64 bit edition for increased memory usage. Over the next 12 months, many more solutions are going to be introduced with SQL Server 2005. I would also spend the time and money to thoroughly test the different options and become a pro with the solution you select, so that when a disaster strikes you can keep the SQL Server running that supports the business. Good luck!
Return to SQL Server comparison features FAQs 6. What are DDL triggers -- are they different from normal (DML) triggers? SQL Server has supported triggers for several versions. These "normal" triggers are known as DML (Data Manipulation Language) triggers, and fire when data modifications occur as the result of INSERT, UPDATE, or DELETE statements. SQL Server 2005 adds a new type of trigger, DDL (Data Definition Language) triggers. These triggers fire when database or server metadata modifications occur as the result of CREATE, ALTER, or DROP statements. For instance, a DDL trigger can be created that will fire every time a user is added to a database, or every time a login is added to the server. There are a few important distinctions that must be drawn between DDL triggers and DML triggers. First of all, DML triggers are created on tables, at the database level. DDL triggers, on the other hand, can be created at either the database or the server level. This allows DDL triggers to be created to capture server events such as ALTER LOGIN or ALTER DATABASE, in addition to database-level events such as ALTER TABLE or ALTER PROCEDURE. The second major distinction is that only AFTER DDL triggers are supported. SQL Server 2000 supports INSTEAD OF DML triggers that fire instead of the data modification instead of afterwards. This is especially important because one of the major use cases for DDL triggers is restricting certain operations; for instance, you might have a DDL trigger that rolls back any ALTER TABLE operations for certain tables. But because the trigger fires after the operation completes, the entire operation will have to be rolled back—a potentially expensive process. This is something to watch out for, but in the end it's a small price for the functionality afforded by DDL triggers.
The final difference is that DDL triggers, unlike DML triggers, do not make use of the inserted and deleted virtual tables. Instead, you'll use the EVENTDATA function to get information about what event caused the trigger to fire. For more information about EVENTDATA, consult SQL Server 2005 Books Online.
Return to SQL Server comparison features FAQs 7. What are the benefits of upgrading to SQL Server 2005? At what point is it highly recommended that one upgrade from SQL Server 2000 to 2005? So many upgrades aren't necessary unless you are using certain technologies. What does 2005 do that 2000 can't? There is probably no easy or clear answer to this, but I can help you with some factors you'll want to consider. First of all, there's the issue of cost. SQL Server 2005 may be more expensive than SQL Server 2000 for your organization to deploy, depending on which features you require. For a breakdown of the current cost and features structure (subject to change before release!), you should review this document from Microsoft's website. Keep in mind that some of the more advanced features, such as partitioning, are not supported unless you purchase the Enterprise Edition. If you're currently deployed on Standard Edition and require these features, you'll have to factor that in. In addition to cost for the software itself, you should consider cost to your organization. Do your DBAs and developers require training in order to take advantage of the new features? Will migration be seamless, or do you need to install and test the new software before upgrading? And what return on investment will the new features give you? Of course, you should also consider whether you want to upgrade at all. SQL Server 2000 will continue to be supported by Microsoft as a mainstream product until at least 2007 -- and extended support will last until at least 2012. So if the features of SQL Server 2000 are serving your organization well, you may not need to upgrade in the near future. I certainly wouldn't recommend that anyone upgrade simply for the sake of upgrading. Just because SQL Server 2005 is released does not make SQL Server 2000 an obsolete product. That said, SQL Server 2005 has a lot of very interesting new
features -- and an immediate upgrade will be tempting! Keep your eyes
on the Tips section of this site; there will be a tip posted shortly that points out some good resources for getting started with learning
your way around the new system.
Return to SQL Server comparison features FAQs 8. Is there a good alternative to a T-SQL debugger? I recently read your response to the Query Analyzer having been replaced by the new SQL Server Management Studio. As it does not include a T-SQL debugger, what do you recommend as an alternative? Unfortunately, you are correct: SQL Server Management Studio does not include the T-SQL debugger that was present in SQL Server Query Analyzer. Microsoft's solution is that developers wishing to debug T-SQL should use Visual Studio 2005, which does include a T-SQL debugger.
Personally, I am not a fan of step-debugging T-SQL at all. I find it a cumbersome process. The lack of visibility into temporary tables and table variables makes it less-than-ideal for most debugging scenarios I've encountered.
Return to SQL Server comparison features FAQs 9. What are the differences and advantages of using SQL or T-SQL? Could you provide some detailed information about SQL vs. T-SQL? What are the differences and advantages of using one over the other? SQL is the Structured Query Language, the ANSI/ISO Standard database language. SQL Server's implementation of the language is called Transact-SQL (T-SQL). T-SQL is based primarily on the version of the ISO standard released in 1992, with bits added from the 1999 standard. In addition, various proprietary enhancements have been made by Microsoft.
There are a number of differences between Standard SQL and T-SQL -- too many to name here. And, yes, there are advantages to using the proprietary extensions if you are working with SQL Server. Due to the nature of many of SQL Server's features, you simply cannot harness its full power without using some nonstandard commands. If you'd like to find out whether your SQL conforms to the standard, you can use the SET FIPS_FLAGGER command.
Return to SQL Server comparison features FAQs 10. Could you outline some of the pros and cons of data warehousing? There are a number of good books on DW. You can read anything by Ralph Kimball (kimballuniversity.com), a DW guru. Maybe it would help you to categorize the issues, rather then pro/cons as each potential "pro" can be "con" depending on your point of view. The factors affecting the DW discussion are a combination of business and technology issues. Business people will want reports with a certain level of quality and timeliness. The infrastructure folks will want security and scalability and the other "abilities". The question for me is really, AS cube or no cube- relational reporting as it is called. Many times depending on the reporting need, a DW will be good enough. The case for a cube in my opinion is tied to aggregation and time analysis. In the end the decision to use DW comes back to what are the reporting requirements.
Return to SQL Server comparison features FAQs
|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
 |  |  | |  | |  | |  | |  | |  | |  | |  | |
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
