Articles Index
Importing and Exporting SQL Server Data from the Command Line with bcp
The bulk copy (bcp) command of Microsoft SQL Server provides you with the ability to insert large numbers of records directly from the command line. In addition to being a great tool for command-line aficionados, bcp is a powerful tool for those seeking to insert data into a SQL Server database from within a batch file or other programmatic method.
SQL Server Stored Procedures
Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.
Stored Procedures vs. User Defined Functions in Microsoft SQL Server
SQL Server user-defined functions and stored procedures offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by...
Using TRY…CATCH to Handle SQL Server Errors
The TRY…CATCH statement in Transact-SQL allows you to detect and handle error conditions gracefully within your database applications. This statement is the cornerstone of SQL Server error handling and is an extremely important part of developing robust database applications.
Pattern Matching in SQL Server Queries
You may often need to create a SQL Server query that performs inexact pattern matching through the use of wildcard characters. The use of wildcards allows you to find data that fits a certain pattern, rather than specifying it exactly. For example, you can use the wildcard "C%" to match any string beginning with a capital C.
SQL Server Constraints
SQL Server constraints allow you to enforce rules in your database. These rules may affect business logic, database integrity and/or table structures. Each one plays an important role in your database architecture.
SQL Server Recovery Models
SQL Server provides three different recovery models that allow you to specify the way SQL Server manages log files and prepares your enterprise for a disaster. Each of these models represents a different approach to balancing the tradeoff between conserving disk space and providing for granular disaster recovery options.
NOT NULL Constraints in Microsoft SQL Server
NOT NULL constraints in Microsoft SQL Server allow you to specify that a column may not contain NULL values. When you create a new NOT NULL constraint on a database column, SQL Server checks the column’s current contents for any NULL values. If the column currently contains NULL values, the constraint creation fails. Otherwise, SQL Server adds the NOT NULL constraint and any future INSERT or UPDATE commands that would cause the existence of a NULL value fail.
UNIQUE Constraints in Microsoft SQL Server
UNIQUE constraints allow SQL Server administrators to specify that a column may not contain duplicate values. When you create a new UNIQUE constraint, SQL Server checks the column in question to determine whether it contains any duplicate values. If the table contains preexisting duplicates, the constraint creation command fails. Similarly, once you have a UNIQUE constraint on a column, attempts to add or modify data that would cause duplicates to exist also fail.
DEFAULT Constraints in Microsoft SQL Server
DEFAULT constraints allow you to specify a value that the database will use to populate fields that are left blank in the input source.
CHECK Constraints in Microsoft SQL Server 2008
CHECK constraints allow you to limit the types of data that users may insert in a database. They go beyond data types and allow you to define the specific values that may be included in a column.
Free and Low Cost SQL Server Options
SQL Server is an enterprise-class relational database, but that doesn't mean it has to blow your budget. In this article, we take a look at two low cost options that let you get up and running with SQL Server 2008 for free or under $50.
Microsoft SQL Server 2008: Choosing the Correct Edition
SQL Server 2008 is the latest release in Microsoft’s enterprise relational database platform series. In this substantial upgrade, they've packed the new database engine full of new features, but fortunately it doesn’t pack any additional punch in your wallet: SQL Server 2008 is available at the same price points used by SQL Server 2005.
Choosing a SQL Server Authentication Mode
Microsoft SQL Server 2008 offers administrators two choices of performing user authentication: Windows authentication mode and mixed authentication mode. Making the proper choice affects both the security and maintenance of your organization’s databases.
Date and Time Data Types in SQL Server
The date and time SQL Server data types are used to store calendar information. They include the datetime, smalldatetime and timestamp variables.
Transaction Log Shrinking and Truncation in SQL Server
The transaction log plays a critical role in a SQL Server database: it maintains an ongoing record of database activity crucial for the restoration of recent data in the event of a disaster. However, this benefit comes at a cost: the transaction log can consume a substantial amount of space in an active database. SQL Server provides two actions designed to counterbalance these large space requirements: transaction log truncation and log file shrinking.
SQL Server Backup Types and Scopes
One of the major advantages that enterprise-class databases offer over their desktop counterparts is a robust backup and recovery feature set. Microsoft SQL Server provides database administrators with the ability to customize a database backup and recovery plan to the business and technical requirements of an organization.
Partitioning a SQL Server Database Table
Looking to optimize the performance of your SQL Server database? If your database contains very large tables, you may benefit from partitioning those tables onto separate filegroups. This technology, introduced in SQL Server 2005, allows you to spread data onto different physical disks, leveraging the concurrent performance of those disks to optimize query performance.
SQL Server Transaction Isolation Models
Normally, it's best to allow SQL Server to enforce isolation between transactions in its default manner; after all, isolation is one of the basic tenets of the ACID model. However, sometimes business requirements force database administrators to stray from the default behavior and adopt a less rigid approach to transaction isolation. To assist in such cases, SQL Server offers five different transaction isolation models.
Other SQL Server Data Types
Other SQL Server data types allow for the storage of unique identifiers, cursors, tables and XML
Binary Data Types in SQL Server
Binary data types allow you to store any type of binary data, including entire files of up to 2GB. They include binary, varbinary, image and bit.
Character String Data Types in SQL Server
Character string data types are used to store text values in Microsoft SQL Server databases.
Exact Numeric Data Types in SQL Server
The exact numeric data types are the most common SQL Server data types used to store numeric information. They include integers (int), big integers (bigint), tiny integers (tinyint), small integers (smallint), decimal and numeric values.
Approximate Numeric Data Types in SQL Server
The approximate numeric data types are not as commonly used as other SQL Server data types used to store numeric information. The exact numeric data types provide more precision and are more commonly used.
Microsoft SQL Server Data Types
Selecting appropriate data types is one of the most important considerations when designing a SQL Server database. Choices you make in the database design phase may have a significant impact on the efficiency, performance and storage requirements of your database down the road.
1 | 2