Tutorial: Learn SQL Server basics from A-Z

 WHAT YOU'LL FIND IN THIS TUTORIAL:

  [IMAGE] Features - What's the difference?
  [IMAGE] Performance tuning basics
  [IMAGE] Native SQL Server tools
  [IMAGE] Backup and recovery
  [IMAGE] Security
 &...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Database Modeling and Design Optimizing SQL Server indexes –- even when they're not your indexes Top tips and tricks for SQL Server database development Managing the development lifecycle with Visual Studio Team System 2008 A first look at Visual Studio Team System 2008 Database Edition Testing transaction log autogrowth behavior in SQL Server Top 10 SQL Server Tips of 2008 Tutorial: SQL Server indexing tips to improve performance SQL Server database design disasters: How it all starts Can you shrink your SQL Server database to death? Physical data storage in SQL Server 2005 and 2008 Microsoft SQL Server Performance Monitoring and Tuning SQL sprawl: Why is SQL Server Express installed everywhere? Top 10 SQL Server tips of 2009 Top 5 SQL Server DBA tasks that are a waste of time SQL Server Mailbag: Asymmetric encryption, log shipping issues Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary binary tree  (SearchSQLServer.com) block  (SearchSQLServer.com) data structure  (SearchSQLServer.com) DDBMS  (SearchSQLServer.com) entity-relationship model  (SearchSQLServer.com) initial extent  (SearchSQLServer.com) primary key  (SearchSQLServer.com) segment  (SearchSQLServer.com) tablespace  (SearchSQLServer.com) view  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

nbsp;[IMAGE] Development
  [IMAGE] SQL Server Integration Services

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE]  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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

  [IMAGE] 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.

MEMBER FEEDBACK

Is there a SQL Server basic you'd like to see our experts cover? Let us know.