Obtaining Identity Column Values in SQL Server
When you enter a new record into a table that contains an identity column, the identity value will be set with the next available value. Your database design often requires you to obtain that value, so you need to be able to find it.
Re-generating SQL Server Logins, Part 2
In the second article on re-generating SQL Server logins, Claire Hsu writes about server-level and database-level role assignments, securables and how to generate logins to include securable granting and denying.
SQL Server Service Broker – Internal Activation
SQL Server Service Broker (SSBS) is a new architecture that allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message-based applications within the database engine itself.
Top 10 Transact-SQL Statements a SQL Server DBA Should Know
Microsoft SQL Server is a feature rich database management system product, with an enormous number of T-SQL commands. With each feature supporting its own list of commands, it can be difficult to remember them all. MAK shares his top 10 T-SQL statements that a DBA should know.
Column-Level Encryption in SQL Server
Beginning with SQL Server 2005, column-level encryption and decryption capabilities were made available within the database, providing a solution for situations where one-off types of data need to be secured beyond your existing authorization, authentication or firewall settings. This article provides an overview and example of securing a column using native SQL Server cryptography functions.
SQL Server: Natural Key Verses Surrogate Key
When designing a database to support applications you need to consider how you are going to handle primary keys. This article explores natural and surrogate keys, and discusses the pros and cons of each, allowing you to determine what makes the best sense in your environment when you are designing your databases.
Re-generating SQL Server Logins
SQL Server stores all login information on security catalog system tables. By querying the system tables, SQL statements can be re-generated to recover logins, including password, default schema/database, server/database role assignments, and object level permissions. A comprehensive permission report can also be produced by combining information from the system metadata.
SSIS Script Task and Microsoft Office Automation
While using Microsoft Office Excel to store a variety of semi-structured data tends to be more economical and convenient than SQL Server, it lacks data mining and data warehousing capabilities. This article explores importing spreadsheets onto SQL Server and taking advantage of Office Automation through managed code running inside a Script Task.
Unit Testing SQL Server Objects in Visual Studio 2010
Money, time and effort can be saved by finding bugs sooner rather than later. Database unit testing has not enjoyed the same level of unit testing that application development has; however, an increase in tools has begun to change that. Deanna Dicken provides a look at database unit testing development within Visual Studio 2010.
Writing SSBS Applications Across Instances - Getting Environments Ready
SQL Server Service Broker (SSBS) allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself. Arshad Ali shows you how to write an SSBS application when the Initiator and Target are in two different databases on two different SQL Server instances.
Data Archiving, Purging and Retrieval Methods for Enterprises
Enterprise growth leads to information explosion and vice versa. With various types of archiving, purging and retrieval methods available, the question becomes, 'What are the advantages/disadvantages in archiving and purging data.' Read on to learn the pros and cons of the different methods.
Accessing Excel Via ADO.NET Using SSIS Script Task
SSIS offers a variety of ways to access Excel-based data. The most popular ones are straightforward to implement, but have limited flexibility. If you find their feature set too restrictive, consider leveraging functionality built into the Microsoft OLE DB provider.
Business Intelligence Terminology 101
Turning data into information and information into knowledge for the purposes of insight and fact-based decision support is the purpose of business intelligence. When delving into business intelligence for the first time, there are some important terms to understand. Deanna Dicken walks through some of the basic terminology needed to get you started.
SQL Server 
