As SQL Server continues to evolve, newbies and sometimes even the most knowledgeable DBAs could use a little help. Whether you want to learn SQL Server basics or simply get a refresher, we've got what you're looking for. We've compiled a tutorial for SQL Server beginners on topics from security to performance. This reference provides the SQL Server basic help you need in areas such as how to configure tempdb, use SQL Profiler, grant permissions, understand temporary tables vs. table variables and much more.
WHAT YOU'LL FIND IN THIS TUTORIAL:
|Features - What's the difference?||Return to Table of Contents|
Differences between varchar and nvarchar in SQL Server
The broad range of data types in SQL Server can sometimes throw people through a loop, especially when data types like varchar and nvarchar seem to be highly interchangeable. Learn how these two data types are stored differently, how to mix and match them and what changes were made to varchar and nvarchar in SQL Serve 2005r.
Clustered and non-clustered indexes in SQL Server
Rules for using a clustered index vs. a non-clustered index in SQL Server are tough to decipher. Discover the fundamentals to consider when making your choice, along with an overview of tradeoffs and proper index implementation.
Temporary tables in SQL Server vs. table variables
Once you've written your T-SQL code, it's time to execute it. If you are going to use a table to store data temporarily, find out about your options while learning the pros and cons of using temporary tables vs. table variables in SQL Server.
Stored procedures vs. functions
Sometimes, the debate between using stored procedures and functions is irrelevant, as both can often accomplish the same tasks. However, there is a fundamental difference between the two - stored procedures are designed to return their output to the application.
Stored procedures vs. dynamic SQL: When should you use each?
Stored procedures and dynamic SQL each have their place in SQL Server. You must consider the parameters used, the tables being queried, the number of databases and so forth. There is often a definitive answer when deciding to use either stored procedures or dynamic SQL.
|Performance tuning basics||Return to Table of Contents|
Update SQL Server table statistics for performance kick
There is plenty to learn about using auto and manual update commands and avoiding false statistics -- two of many techniques that will fine-tune SQL Server statistics and boost performance.
Configuring SQL Server memory settings
Configuring SQL Server memory settings is crucial. Find out configuration recommendations for optimal RAM amounts, how to enable AWE, the best number of gigabytes for maximum server memory and the differences between 32- and 64-bit platforms.
Tricks to increase SQL Server query performance
Discover several tricks to increase SQL Server query performance, such as avoiding system table locking by knowing when to use the CREATE TABLE vs. SELECT INTO command. You'll also find a stored procedure method that improves query response time, as opposed to linked server queries that can unexpectedly slow down system performance.
Configure SQL Server 2005 tempdb for performance
Processing once reserved for the SQL Server 2000 transaction logs has been moved in SQL Server 2005, making it essential that you properly configure your SQL Server 2005 tempdb database for performance.
SQL Server tempdb best practices increase performance
Making a few adjustments to the database settings can quickly and efficiently increase the performance of your SQL Server tempdb. Discover some best practices for tempdb, including physical file settings, storage array configurations, statistics update options and how to index temporary tables.
|Native SQL Server tools||Return to Table of Contents|
SQL Profiler: Features and setup in SQL Server 2005
SQL Profiler can be considered the best native SQL Server resource because, for one thing, it understands micro-level processing on any SQL Server. Get an introduction to SQL Profilerand its features and setup processes.
SQL Server PerfMon
This screencast series offers tips on how to use SQL Server PerfMon counters to track Windows memory, disk IO and the buffer manager. Find out the maximum numbers to watch for when using SQL Server Performance Monitor in your system.
Tricks for using the SQL Server Index Tuning Wizard
Gathering tricks for using the SQL Server Index Tuning Wizard can improve overall performance in SQL Server. Figuring out how to optimize the Tuning Wizard in conjunction with SQL Profiler will be a big help in your server environment.
SQL Server 2000 Query Analyzer: 10 tricks for simple querying
Even though Microsoft excluded this tool from SQL Server 2005, many improvements can be made to the raw performance of your SQL Server statements, as well as to your job performance as a DBA or developer, by simply using these tricks for simple querying in SQL Server 2000's Query Analyzer.
SQL Server Management Studio client tool enhancements
Although using Query Analyzer or Enterprise Manager can be reassuring to a long-time DBA, many upgrades point towards it being time to step out of your comfort zone and learn about the features and enhancements in SQL Server Management Studio.
Tracking query execution with SQL Server 2005 Profiler
When you are capturing and analyzing data about single transactions on your SQL Server, the best native tool is SQL Server Profiler. Discover how to analyze your Profiler results in SQL Server 2005 and track for query performance improvements.
|Backup and recovery||Return to Table of Contents|
Selecting a SQL Server recovery model
Your SQL Server offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. These determine how much data loss is acceptable in case of a failure, along with what types of backup and restore functions are allowed. Find out which option will suit you best when you are attempting to select a SQL Server recovery model.
Tips for scheduling and testing SQL Server backups
These tips for scheduling and testing SQL Server backups will help guide you on the path to a successful restore. Also, find out how to schedule backups in SQL Server via the SQL Server Agent and in SQL Server Express using Windows Task Scheduler.
Best practices for SQL Server backup maintenance
Removing backup history from the msdb database and reducing its size are just two of many best practices for improving SQL Server performance with backup maintenance.
Restore basics: How to restore using T-SQL commands
SQL Server Enterprise Manager may be a quick and easy way to run backups and restores, but T-SQL commands offer much greater flexibility. Find out how to script your SQL Server backups or restores with T-SQL.
Restoring a database from another SQL Server
Restoring a database from another SQL Server can be relatively simple, but other aspects, such as matching up the logins and users again, are not.
|Security||Return to Table of Contents|
Granting permissions in SQL Server 2005
New security features in SQL Server 2005 make it easier to manage and grant SQL Server permissions on a more granular basis. Get an an overview of user-schema separation and granular server permissions, as well as a new function to help you identify which permissions are available to particular users.
Working with schemas in SQL Server 2005
If you're managing too many databases with too many objects, find out how schemas in SQL Server 2005 can help you get your database organized and assign object permissions easily.
Secure SQL Server from SQL injection attacks
Any Web application using dynamic SQL is at risk for a SQL injection attack, one of the most common security risks for Internet-facing SQL Server databases. With that in mind, learn how to secure your SQL Server from SQL injection attacks.
SQL Server password management: Six risky assumptions
Thorough password testing and securing SQL Server installations beyond the main database server are two major steps towards preserving SQL Server security. No password system is invincible, so find out even more assumptions to avoid when improving SQL Server password management.
Basic SQL Server security principles you can't afford to miss
Protecting your SQL Server databases is sometimes as simple as practicing basic SQL Server security principles. Keep in mind several often-overlooked security weaknesses, such as least privilege, delegated administration, separation of duties and DBAs unnecessarily using admin-level accounts for day-to-day tasks.
|Development||Return to Table of Contents|
Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005
Understanding and working with date/time data types in SQL Server can be complicated. Learn the basics of working with DATETIME and SMALLDATETIME in SQL Server 2005, along with an overview of TIMESTAMP, a data type often confused with these two primary date/time data types.
T-SQL performance problems and solution
SQL Server performance problems don't stem entirely from database issues. Poorly written T-SQL code can also impact your SQL Server. Rather than jumping to conclusions about memory and CPU utilization, learn about common T-SQL performance problems and solutions.
Using MAX data types in SQL Server
If you are looking for a data type capable of handling large amounts of data, the MAX data type in SQL Server can handle 2GB of data while remaining compatible with all intrinsic SQL Server string functions.
Optimize T-SQL data types in SQL Server
Data types can seem inconsequential in the grand scheme of things, but they have a huge impact on SQL Server performance as the system grows. Find out how they can affect your I/O, RAM and CPU, and ensure that you've optimized T-SQL data types in your SQL Server environment.
SQL Server stored procedures tutorial: Write, tune and get examples
SQL Server stored procedures, sometimes called the work horse of the database, provide an important layer of security between the user interface and database. However, the process of grouping T-SQL statements to create stored procedures can be complex. Learn the basics for writing stored procedures, along with methods for tuning them
|SQL Server Integration Services||Return to Table of Contents|
Maintain DTS packages in SQL Server 2005
What if you've migrated to SQL Server 2005 and want to hold off upgrading your DTS packages to SSIS? In this tip, you'll see how to edit, maintain and develop DTS packages in SQL Server.
SQL Server Integration Services programming basics
SSIS is more than a tool to move data around in SQL Server 2005. It creates a system where the data sets that you import and the packages you create are programmatic objects that can talk to one another, rather than existing as static data. Learn more of the basics of SQL Server Integration Services programming.
Migrating SQL Server 2000 DTS packages to SSIS
Migrating SQL Server 2000 DTS packages to SSIS is important, but SSIS is more than just a replacement for DTS packages. The scale of changes is vast and this SQL Server 2005 ETL platform is far more powerful.
Open SSIS packages without validation using these SQL properties
When opening a SQL Server Integration Services (SSIS) package in SSIS Designer, by default SSIS validates data sources used by the various components. You can choose to override this behavior -- primarily for time saving purposes -- with two workarounds.
Export SQL Server data to an Excel file using SSIS and Visual Studio
Here are seven steps for exporting SQL Server 2005 data to a MS Excel spreadsheet with SSIS. They cover such topics as how to design an SSIS package using Visual Studio and how to export data from a SQL Server table to Excel.
|Return to Table of Contents|
Bonus: SQL and SQL Server Tutorial and Reference Guide
These Back to Basics SQL tips from SQL Server MVP Denny Cherry provide a great starting point for learning SQL or brushing up your skills.
Is there a SQL Server basic you'd like to see our experts cover? Let us know.
This was first published in November 2008