Welcome to SQL Server Resources! Deals and savings on SQL Server and SQL Server products for sale!

SQL Server FAQ

Transact-SQL Optimization Tips

•Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure 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.

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

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

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

•Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.

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

•Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by 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 it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

•If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is 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 So, you can improve the speed of such queries in several times.

•Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

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

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

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

(ArticlesBase SC #1205713)

Recover SQL Server Database File

Microsoft SQL Server is the most widely used database application in small as well as large organizations because of its stability, effectiveness, reliability, robustness, consistency, and security features. The sql repair software stores the information in SQL database file (.MDF), which like other computer files is also susceptible to damage or corruption. There can be many reasons for .MDF file corruption like abrupt system shut down, hard disk failure, cache problem, invalid database file header, internal program errors, etc.

Whatever be the reason for .MDF file damage, it leaves you in strenuous and dreadful situation. A large amount of data comprising client’s information, inventory details, etc. is no more accessible. Most of the software applications that communicate with .MDF file for data retrieval and update are not able to properly work. Damage of the .MDF file may halt the working of whole organization. And if the data stored on .MDF file is not recovered soon or the data gets permanently lost than it can result in a big loss for the organization. But, you do not need to worry if come across such a horrific incident.

Today, recovering data from the damaged .MDF file is not a big deal. Lots of SQL database recovery programs are available in the market that can help you recover data within minutes. These programs perform quick SQL recovery and consistently recover the data stored in damaged .MDF file. You can easily find an efficient, cost-effective, and fast SQL recovery program by spending some time on search engines like Google, Yahoo, and MSN. Most of the software organizations that deal in SQL recovery programs provide such software for free trial so that you can verify the software quality before purchasing.

However, selecting good SQL Repair software totally depends on you. Before purchasing the software, you should examine its trial version for data consistency and integrity. If the software recovers the data but does not maintain data consistency and data integrity then the software is not worthy. You should check that whether the software supports the SQL server version in which the damaged .MDF file was created or not. If the software does not support the required version then go for another SQL database recovery software. Once you find the suitable and cost-effective SQL recovery software, purchase it, and get all of your data back.

(ArticlesBase SC #1620463)

Difference Between Microsoft Access and Microsoft SQL Server

Microsoft Access is a relational database management system (RDBMS) that is packaged with the Microsoft office to run under the Microsoft Windows operating system. Microsoft Access Database is a Visual Basic based application that permits SQL statements to be included in associated macros and VB scripts. Access Databases includes a Jet database engine, joint with a graphical interface, to offer easiness of use in building front-end database applications. Microsoft Access Software is a structured environment, used by beginner programmers, with little or no experience, to handle relatively small database functions. It is also a useful tool, for advanced developers, to supply rapid application development.

Access databases do not scale well for big databases or multiple-access, network environments. The underlying Jet Database is a file-sharing database that performs all processing at the client level. Microsoft Access is not designed for high volume multi-user environments that allow multiple clients to read, write and execute other operations on shared files at the same time.

Microsoft SQL Server is a server level RDBMS that incorporates the industry standard, SQL Server originated from the enterprise level, Sybase SQL Server, developed for mainframe operating systems, such as Unix and VAX VMS. The client-server interaction of SQL Server is more robust than Microsoft Access databases and is capable to handle high volume multi-user environments. SQL Server is one of Microsoft's BackOffice servers, designed to handle client/server applications using the industry standard and ANSI compliant SQL. SQL Server is scalable for multiprocessing. It has the ability to reliably perform database functions and data warehousing. SQL Server can be configured to effectively and efficiently accommodate multiple users, 24 X 7, and is ideal for complex and demanding applications such as Web hosting, e-commerce, real time transactions and messaging systems.

The client-server configuration allows for the manipulation of large, multiple databases along with the capability to perform multiple and continuous data warehousing functions. It's uptime and reliability reduces the chance of database corruptions, due to failed read and write operations, as well as data access conflicts. Microsoft SQL Server is, often at times, confused with MySQL. MySQL is also a server level SQL-based RDBMS which is developed by a Swedish firm and it is distributed freely under general public license.

(ArticlesBase SC #2277380)

SQL Server - Retrieving a Range of Rows With Multiple Nonclustered Indexes on the Table

Take the following query:

SELECT * FROM accounts
WHERE
BalanceRemaining BETWEEN 100 AND 200
AND
CustomerNum BETWEEN 1000 AND 1200

If no relevant indexes exist on the table, a table scan would be performed. If a nonclustered index exists on the BalanceRemaining column, the query optimizer might use that index. This would depend on the number of records returned. If too large, the index would be ignored. If a nonclustered index exists on the CustomerNum column, it might be used by the query optimiser if numbers are no too large..

If the query optimiser chooses to use one of the indexes, then SQL Server would process the range specified in the where clause by issuing a data page request for each pointer (assuming there is no clustered index on the table, so we are dealing with Row ID's).

As each row is returned, the remaining criteria would be applied to the row. You could say the data has been filtered. The problem with this type of technique is that it's inefficient. If we have a non-clustered index on the BalanceRemaining column and the query optimiser decides to use that index to perform the above query. The index may have, for example, 200 leaf level index key values that meet the BalanceRemaining range, and 200 data page requests will be performed. When SQL Server applies the CustomerNum filter, most of the data rows from the result set could be eliminated

The nonclustered index has been used index to retrieve a row set, most of which were discarded. Retrieving data pages is a very expensive operation in terms of resources and data returned..

However, if a second nonclustered index is created on column CustomerNum, the query optimiser will use both of the indexes in a lot of cases in the optimiser plan. The result of the query that have a BalanceRemaining between 100 and 200 and the set of accounts that have a customer number between 1,000 and 1,200 is the set intersection of the set of accounts.

(ArticlesBase SC #1588144)

The Skills Needed in SQL Server Jobs

Experienced professionals with SQL server jobs would be the first to tell new graduates that they are entering a tough job market. SQL servers, after all, form the backbone of database technology for small and medium-sized businesses throughout the United Kingdom. With SQL server technology, companies could not keep track of sales, inventory, or contact information for clients. The necessity for skilful operators of SQL servers means that new graduates are entering a pressure-filled field.

However, SQL server jobs do not need to frighten new graduates into other fields. On the contrary, once a graduate is able to get a foothold into an IT department with their highly specialised skill, the job becomes much easier and less stressful. The only remaining stress for SQL server professionals is from fellow IT workers and new graduates, who are competing with one another for better pay and higher responsibility.
SQL server jobs require a set of specific skills in order to ensure a professional’s success. One skill that is a prerequisite for most IT professionals, and specifically to SQL server professionals, is an attention to detail. Those working with these database servers need to be keenly focused on codes, programs, and snags that may cause damage to the entire server. As well, they need to be focused on the daily issues that may arise by misuse of the server by office workers.

In addition to a keen attention to detail, SQL server professionals need to be curious about technology. Database professionals, like other IT workers, are required to work around the clock on their specific project. However, a company may have the need for additional hands for an IT project like installing new computers or updating software for a specific department. Curiosity in SQL server professionals can lead to greater connections throughout the office place and more exposure to managers, who may look favourably on this assistance during evaluations.

SQL server professionals need to be entirely committed to their job. IT graduates and young professionals who do not feel that they can work on database issues everyday for years on end need to look elsewhere. The aforementioned curiosity and attention to detail are stoked by a commitment to doing the best job possible in SQL server jobs. In order to succeed in SQL server jobs over the long term, a professional needs to develop new ways to keep themselves focused on the task at hand. Only truly committed SQL server workers can keep this effort going throughout their career.

(ArticlesBase SC #280241)