SQL Server Foreign Keys and Indexes

This is another one of those cases that just beats it into your head to stop making assumptions.

My first delve into RDBMS’s was MySQL. When you create a Foreign Key in MySQL, it will create an Index for you. Unfortunately, I assumed that SQL Server would do the same. Then about a year ago, I was researching ways to speed up some of the queries in a customer’s application when I came across a thread on StackOverflow. What did I find? I was making the wrong assumption and you have to create both the Foreign Key and Index in SQL Server.

 

Free SQL 2008 R2 Ebook

Microsoft Press has released a free ebook entitled Introducing Microsoft SQL Server 2008 R2.

PART I Database Administration

CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2 Multi-Server Administration 21
CHAPTER 3 Data-Tier Applications 41
CHAPTER 4 High Availability and Virtualization Enhancements 63
CHAPTER 5 Consolidation and Monitoring 85

PART II Business Intelligence Development

CHAPTER 6 Scalable Data Warehousing 109
CHAPTER 7 Master Data Services 125
CHAPTER 8 Complex Event Processing with StreamInsight 145
CHAPTER 9 Reporting Services Enhancements 165
CHAPTER 10 Self-Service Analysis with PowerPivot 189

More information:
http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

 

T-SQL String Padding Functions

Some time ago I found myself needing to format the data within a dataset. I thought about writing the code for it but then realized that a SQL implementation would probably be more efficient. I wrote two functions fPadLeft and fPadRight to allow a varchar(MAX) to be padded with any character (in my case it was zeros).

Here’s the fPadRight function:

CREATE FUNCTION fPadRight
(
  @OrigString VARCHAR(MAX) = NULL,
  @PadLength INT = 0,
  @PadChar CHAR(1) = ''
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @Result VARCHAR(MAX);
  DECLARE @OrigLength INT;

  SET @OrigLength = LEN(@OrigString);

  IF (@OrigLength >= @PadLength)
  BEGIN
    SET @Result = @OrigString
  END
  ELSE
  BEGIN
    SET @Result =  @OrigString + REPLICATE(@PadChar, @PadLength - @OrigLength);
  END

  RETURN @Result
END

The entire scripts, including if exists logic, are included in the following release:

SQL Padding Function Scripts (.zip)