SQL Server 2008 introduces a new method for generating a random number by using the CRYPT_GEN_RANDOM function. The arguments for this function include length (in bytes) and an optional seed value.
The following example demonstrates the generation of a 10 byte random varbinary value which I also convert to bigint:
SELECT CAST(CRYPT_GEN_RANDOM (10) as bigint)
– Returned -659868264676196655
SELECT CAST(CRYPT_GEN_RANDOM (10) as bigint)
– Returned -2688401773356061232
One more random value generation method for the toolbox.
Tags: SQL Server 2008
There are several methods you could use in SQL Server 2005 to determine when the SQL Server instance started up - however SQL Server 2008 introduces a quick and direct method by querying the sqlserver_start_time column from the sys.dm_os_sys_info DMV (example below):
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info
Tags: SQL Server 2008
Apress has provided Chapter 2 as a sample chapter. I asked for this chapter to be the sample chapter because there were two RC0 changes that I had requested in the editorial phase which were not put into the final version. So this is the “RC0 corrected version” of the chapter. Click here for the PDF link to Chapter 2. Thanks!
Tags: SQL Server 2008
Apress posted a few new sections from SQL Server 2008 Transact-SQL Recipes - so here are the download links:
Chapter 1 - SELECT pdf version (Update - this sample chapter was replaced with Chapter 2)
Chapter 2 - Perform, Capture, and Track Data Modifications (Corrected Version of Chapter 2)
Table of Contents pdf version
I’m also working on getting Chapter 2 as a free chapter - with the RC0 changes I originally submitted included in it - so stay tuned.
Tags: T-SQL
Per my promise in an earlier post, here is a more detailed content list for SQL Server 2008 Transact-SQL Recipes. ** Just a warning that this is a long post. ** Feel free to post comments if you have questions about the table of contents or topics listed.
CHAPTER 1 SELECT
The Basic SELECT Statement
Selecting Specific Columns from a Table
Selecting Every Column for Every Row
Selective Querying Using a Basic WHERE Clause
Using the WHERE Clause to Specify Rows Returned in the Result Set
Combining Search Conditions
Negating a Search Condition
Keeping Your WHERE Clause Unambiguous
Using Operators and Expressions
Using BETWEEN for Date Range Searches
Using Comparisons
Checking for NULL Values
Returning Rows Based on a List of Values
Using Wildcards with LIKE
Declaring and Assigning Values to Variables
Grouping Data
Using the GROUP BY Clause
Using GROUP BY ALL
Selectively Querying Grouped Data Using HAVING
Ordering Results
Using the ORDER BY Clause
Using the TOP Keyword with Ordered Results
SELECT Clause Techniques
Using DISTINCT to Remove Duplicate Values
Using DISTINCT in Aggregate Functions
Using Column Aliases
Using SELECT to Create a Script
Performing String Concatenation
Creating a Comma-Delimited List Using SELECT
Using the INTO Clause
Subqueries
Using Subqueries to Check for Matches
Querying from More Than One Data Source
Using INNER Joins
Using OUTER Joins
Using CROSS Joins
Referencing a Single Table Multiple Times in the Same Query
Using Derived Tables
Combining Result Sets with UNION
Using APPLY to Invoke a Table-Valued Function for Each Row
Using CROSS APPLY
Using OUTER APPLY
Advanced Techniques for Data Sources
Using the TABLESAMPLE to Return Random Rows
Using PIVOT to Convert Single Column Values into Multiple Columns
and Aggregate Data
Normalizing Data with UNPIVOT
Returning Distinct or Matching Rows Using EXCEPT and INTERSECT
Summarizing Data
Summarizing Data Using CUBE
Summarizing Data Using ROLLUP
Creating Custom Summaries Using Grouping Sets
Revealing Rows Generated by GROUPING
Advanced Group-Level Identification with GROUPING_ID
Common Table Expressions
Using a Non-Recursive Common Table Expression
Using a Recursive Common Table Expression
CHAPTER 2 Perform, Capture, and Track Data Modifications
INSERT
Inserting a Row into a Table
Inserting a Row Using Default Values
Explicitly Inserting a Value into an IDENTITY Column
Inserting a Row into a Table with a uniqueidentifier Column
Inserting Rows Using an INSERTSELECT Statement
Inserting Data from a Stored Procedure Call
Inserting Multiple Rows with VALUES
Using VALUES As a Table Source
UPDATE
Updating a Single Row
Updating Rows Based on a FROM and WHERE Clause
Updating Large Value Data Type Columns
Inserting or Updating an Image File Using OPENROWSET and BULK
Storing Unstructured Data on the File System While Maintaining
SQL Server Transactional Control
Assigning and Modifying Database Values “in Place”
DELETE
Deleting Rows
Truncating a Table
Advanced Data Modification Techniques
Chunking Data Modifications with TOP
Executing INSERTs, UPDATEs, and DELETEs in a Single Statement
Capturing and Tracking Data Modification Changes
Returning Rows Affected by a Data Modification Statement
Asynchronously Capturing Table Data Modifications
Querying All Changes from CDC Tables
Querying Net Changes from CDC Tables
Translating the CDC Update Mask
Working with LSN Boundaries
Disabling Change Data Capture from Tables and the Database
Tracking Net Data Changes with Minimal Disk Overhead
CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking
Transaction Control
Using Explicit Transactions
Displaying the Oldest Active Transaction with DBCC OPENTRAN
Querying Transaction Information by Session
Locking
Viewing Lock Activity
Controlling a Table’s Lock Escalation Behavior
Transaction, Locking, and Concurrency
Configuring a Session’s Transaction Locking Behavior
Blocking
Identifying and Resolving Blocking Issues
Configuring How Long a Statement Will Wait for a Lock
to Be Released
Deadlocking
Identifying Deadlocks with a Trace Flag
Setting Deadlock Priority
CHAPTER 4 Tables
Table Basics
Creating a Table
Adding a Column to an Existing Table
Changing an Existing Column Definition
Creating a Computed Column
Reducing Storage for Null Columns
Dropping a Table Column
Reporting Table Information
Dropping a Table
Collation Basics
Viewing Collation Metadata
Designating a Column’s Collation
Keys
Creating a Table with a Primary Key
Adding a Primary Key Constraint to an Existing Table
Creating a Table with a Foreign Key Reference
Adding a Foreign Key to an Existing Table
Creating Recursive Foreign Key References
Allowing Cascading Changes in Foreign Keys
Surrogate Keys
Using the IDENTITY Property During Table Creation
Using DBCC CHECKIDENT to View and Correct IDENTITY
Seed Values
Using the ROWGUIDCOL Property
Constraints
Creating a Unique Constraint
Adding a UNIQUE Constraint to an Existing Table
Using CHECK Constraints
Adding a CHECK Constraint to an Existing Table
Disabling and Enabling a Constraint
Using a DEFAULT Constraint During Table Creation
Adding a DEFAULT Constraint to an Existing Table
Dropping a Constraint from a Table
Temporary Tables and Table Variables
Using a Temporary Table for Multiple Lookups Within a Batch
Creating a Table Variable to Hold a Temporary Result Set
Manageability for Very Large Tables
Implementing Table Partitioning
Determining the Location of Data in a Partition
Adding a New Partition
Removing a Partition
Moving a Partition to a Different Table
Removing Partition Functions and Schemes
Easing VLDB Manageability with Filegroups
Reducing Disk Space Usage with Data Compression
CHAPTER 5 Indexes
Index Overview
Creating a Table Index
Enforcing Uniqueness on Non-Key Columns
Creating an Index on Multiple Columns
Defining Index Column Sort Direction
Viewing Index Meta Data
Disabling an Index
Dropping Indexes
Changing an Existing Index with DROP_EXISTING
Controlling Index Build Performance and Concurrency
Intermediate Index Creation in Tempdb
Controlling Parallel Plan Execution for Index Creation
Allowing User Table Access During Index Creation
Index Options
Using an Index INCLUDE
Using PAD_INDEX and FILLFACTOR
Disabling Page and/or Row Index Locking
Managing Very Large Indexes
Creating an Index on a Filegroup
Implementing Index Partitioning
Indexing a Subset of Rows
Reducing Index Size
CHAPTER 6 Full-Text Search
Full-Text Indexes and Catalogs
Creating a Full-Text Catalog
Creating a Full-Text Index
Modifying a Full-Text Catalog
Modifying a Full-Text Index
Retrieving Full-Text Catalog and Index Metadata
Discarding Common Strings from a Full-Text Index
Dropping a Full-Text Index
Dropping a Full-Text Catalog
Basic Searching
Using FREETEXT to Search Full-Text Indexed Columns
Using CONTAINS for Word Searching
Advanced Searching
Using CONTAINS to Search with Wildcards
Using CONTAINS to Search for Inflectional Matches
Using CONTAINS for Searching Results by Term Proximity
Ranked Searching
Returning Ranked Search Results by Meaning
Returning Ranked Search Results by Weighted Value
CHAPTER 7 Views
Regular Views
Creating a Basic View
Querying the View Definition
Displaying Views and Their Structures
Refreshing a View’s Definition
Modifying a View
Dropping a View
Modifying Data Through a View
View Encryption
Encrypting a View
Indexed Views
Creating an Indexed View
Forcing the Optimizer to Use an Index for an Indexed View
Partitioned Views
Creating a Distributed-Partitioned View
CHAPTER 8 SQL Server Functions
Aggregate Functions
Returning the Average of Values
Returning Row Counts
Finding the Lowest and Highest Values from an Expression
Returning the Sum of Values
Using Statistical Aggregate Functions
Mathematical Functions
Performing Mathematical Operations
String Functions
Converting a Character Value to ASCII and Back to Character
Returning Integer and Character Unicode Values
Finding the Start Position of a String Within Another String
Finding the Start Position of a String Within Another String Using Wildcards
Determining the Similarity of Strings
Taking the Leftmost or Rightmost Part of a String
Determining the Number of Characters or Bytes in a String
Replacing a Part of a String
Stuffing a String into a String
Changing Between Lower- and Uppercase
Removing Leading and Trailing Blanks
Repeating an Expression N Number of Times
Repeating a Blank Space N Number of Times
Outputting an Expression in Reverse Order
Returning a Chunk of an Expression
Working with NULLs
Replacing a NULL Value with an Alternative Value
Performing Flexible Searches Using ISNULL
Returning the First Non-NULL Value in a List of Expressions
Returning a NULL Value When Two Expressions Are Equal: Otherwise Returning the First Expression
Date Functions
Returning the Current Date and Time
Converting Between Time Zones
Incrementing or Decrementing a Date’s Value
Finding the Difference Between Two Dates
Displaying the String Value for Part of a Date
Displaying the Integer Representation for Parts of a Date
Displaying the Integer Value for Part of a Date Using YEAR, MONTH,and DAY
Type Conversion
Converting Between Data Types
Converting Dates to Their Textual Representation
Representing Binary Data in String Literals
Evaluating the Data Type Returned by an Expression
Ranking Functions
Generating an Incrementing Row Number
Returning Rows by Rank
Returning Rows by Rank Without Gaps
Using NTILE
Probing Server, Database, and Connection-Level Settings Using System Functions
Determining the First Day of the Week
Viewing the Language Used in the Current Session
Viewing and Setting Current Connection Lock Timeout Settings
Displaying the Nesting Level for the Current Stored Procedure Context
Returning the Current SQL Server Instance Name and SQL Server Version
Returning the Current Connection’s Session ID (SPID)
Returning the Number of Open Transactions
Retrieving the Number of Rows Affected by the
Previous Statement
Retrieving System Statistics
Displaying Database and SQL Server Settings
Returning the Current Database ID and Name
Returning a Database Object Name and ID
Returning the Application and Host for the Current User Session
Reporting Current User and Login Context
Viewing User Connection Options
IDENTITY and uniqueidentifier Functions
Returning the Last Identity Value
Returning an Identity Column’s Seed and Incrementing Value
Creating a New uniqueidentifier Value
CHAPTER 9 Conditional Processing, Control-of-Flow, and Cursors
Conditional Processing
Using CASE to Evaluate a Single Input Expression
Using CASE to Evaluate Boolean Expressions
Using IFELSE
Control-of-Flow
Using RETURN
Using WHILE
Using GOTO
Using WAITFOR
Cursors
Creating and Using Transact-SQL Cursors
CHAPTER 10 Stored Procedures
Stored Procedure Basics
Creating a Basic Stored Procedure
Creating a Parameterized Stored Procedure
Using OUTPUT Parameters
Modifying a Stored Procedure
Dropping Stored Procedures
Executing Stored Procedures Automatically at SQL Server Startup
Reporting Stored Procedure Metadata
Documenting Stored Procedures
Stored Procedure Security
Encrypting a Stored Procedure
Using EXECUTE AS to Specify the Procedure’s Security Context
Recompilation and Caching
RECOMPILE(ing) a Stored Procedure Each Time It Is Executed
Flushing the Procedure Cache
CHAPTER 11 User-Defined Functions and Types
UDF Basics
Creating Scalar User-Defined Functions
Creating Inline User-Defined Functions
Creating Multi-Statement User-Defined Functions
Modifying User-Defined Functions
Viewing UDF Metadata
Dropping User-Defined Functions
Benefitting from UDFs
Maintaining Reusable Code
Cross-Referencing Natural Key Values
Replacing Views with Multi-Statement UDFs
UDT Basics
Creating and Using User-Defined Types
Identifying Columns and Parameters with Dependencies
on User-Defined Types
Dropping User-Defined Types
Passing Table-Valued Parameters
CHAPTER 12 Triggers
DML Triggers
Creating an AFTER DML Trigger
Creating an INSTEAD OF DML Trigger
Handling Transactions Within DML Triggers
Controlling DML Triggers Based on Modified Columns
Viewing DML Trigger Metadata
DDL Triggers
Creating a DDL Trigger That Audits Database-Level Events
Creating a DDL Trigger That Audits Server-Level Events
Using a Logon Trigger
Viewing DDL Trigger Metadata
Managing Triggers
Modifying a Trigger
Enabling and Disabling Table Triggers
Limiting Trigger Nesting
Controlling Trigger Recursion
Setting Trigger Firing Order
Dropping a Trigger
CHAPTER 13 CLR Integration
CLR Overview
When (and When Not) to Use Assemblies
CLR Objects Overview
Creating CLR Database Objects
Enabling CLR Support in SQL Server
Writing an Assembly for a CLR Stored Procedure
Compiling an Assembly into a DLL File
Loading the Assembly into SQL Server
Creating the CLR Stored Procedure
Creating a CLR Scalar User-Defined Function
Creating a CLR Trigger
Administering Assemblies
Viewing Assembly Metadata
Modifying an Assembly’s Permissions
Removing an Assembly from the Database
CHAPTER 14 XML, Hierarchies, and Spatial Data
Working with Native XML
Creating XML Data Type Columns
Inserting XML Data into a Column
Validating XML Data Using Schemas
Retrieving XML Data
Modifying XML Data
Indexing XML Data
Converting Between XML Documents and Relational Data
Formatting Relational Data As XML
Converting XML to a Relational Form
Working with Native Hierarchical Data
Storing Hierarchical Data
Returning a Specific Ancestor
Returning Child Nodes
Returning a Node’s Depth
Returning the Root Node
Determining Whether a Node Is a Child of the Current Node
Changing Node Locations
Native Spatial Data
Storing Spatial Data
Querying Spatial Data
CHAPTER 15 Hints
Using Join Hints
Forcing a HASH Join
Using Query Hints
Forcing a Statement Recompile
Using Table Hints
Executing a Query Without Locking
Forcing a SEEK over a SCAN
CHAPTER 16 Error Handling
System-Defined and User-Defined Error Messages
Viewing System Error Information
Creating a User-Defined Error Message
Dropping a User-Defined Error Message
Manually Raising an Error
Invoking an Error Message
Trapping and Handling Application Errors
Old-Style Error Handling
Error Handling with TRYCATCH
Applying Error Handling Without Recoding a Stored Procedure
Nesting Error Handling
CHAPTER 17 Principals
Windows Principals
Creating a Windows Login
Viewing Windows Logins
Altering a Windows Login
Dropping a Windows Login
Denying SQL Server Access to a Windows User or Group
SQL Server Principals
Creating a SQL Server Login
Viewing SQL Server Logins
Altering a SQL Server Login
Managing a Login’s Password
Dropping a SQL Login
Managing Server Role Members
Reporting Fixed Server Role Information
Database Principals
Creating Database Users
Reporting Database User Information
Modifying a Database User
Removing a Database User from the Database
Fixing Orphaned Database Users
Reporting Fixed Database Roles Information
Managing Fixed Database Role Membership
Managing User-Defined Database Roles
Managing Application Roles
CHAPTER 18 Securables, Permissions, and Auditing
Permissions Overview
Reporting SQL Server Assignable Permissions
Server-Scoped Securables and Permissions
Managing Server Permissions
Querying Server-Level Permissions
Database-Scoped Securables and Permissions
Managing Database Permissions
Querying Database Permissions
Schema-Scoped Securables and Permissions
Managing Schemas
Managing Schema Permissions
Object Permissions
Managing Object Permissions
Managing Permissions Across Securable Scopes
Determining a Current Connection’s Permissions to a Securable
Reporting the Permissions for a Principal by Securable Scope
Changing Securable Ownership
Allowing SQL Logins to Access Non-SQL Server Resources
Auditing SQL Instance and Database-Level Activity of Principals
Against Securables
Defining Audit Data Sources
Capturing SQL Instance–Scoped Events
Capturing Database-Scoped Events
Querying Captured Audit Data
Managing, Modifying, and Removing Audit Objects
CHAPTER 19 Encryption
Encryption by Passphrase
Using a Function to Encrypt by Passphrase
Master Keys
Backing Up and Restoring a Service Master Key
Creating, Regenerating, and Dropping a Database Master Key
Backing Up and Restoring a Database Master Key
Removing Service Master Key Encryption from the Database Master Key
Asymmetric Key Encryption
Creating an Asymmetric Key
Viewing Asymmetric Keys in the Current Database
Modifying the Asymmetric Key’s Private Key Password
Encrypting and Decrypting Data Using an Asymmetric Key
Dropping an Asymmetric Key
Symmetric Key Encryption
Creating a Symmetric Key
Viewing Symmetric Keys in the Current Database
Changing How a Symmetric Key Is Encrypted
Using Symmetric Key Encryption and Decryption
Dropping a Symmetric Key
Certificate Encryption
Creating a Database Certificate
Viewing Certificates in the Database
Backing Up and Restoring a Certificate
Managing a Certificate’s Private Key
Using Certificate Encryption and Decryption
Automatically Opening and Decrypting via a Symmetric Key
Transparent Data Encryption
Enabling Transparent Data Encryption
Managing and Removing TDE
CHAPTER 20 Service Broker
Example Scenario: Online Bookstore
Creating a Basic Service Broker Application
Enabling Databases for Service Broker Activity
Creating the Database Master Key for Encryption
Managing Message Types
Creating Contracts
Creating Queues
Creating Services
Initiating a Dialog
Querying the Queue for Incoming Messages
Receiving and Responding to a Message
Ending a Conversation
Prioritizing Service Broker Conversations
Creating a Stored Procedure to Process Messages
Creating the Bookstore Stored Procedure
Remote-Server Service Broker Implementations
Enabling Transport Security
Enabling Dialog Security
Creating Routes and Remote Service Bindings
Event Notifications
Capturing Login Commands
CHAPTER 21 Configuring and Viewing SQL Server Options
Viewing SQL Server Configurations
Changing SQL Server Configurations
CHAPTER 22 Creating and Configuring Databases
Creating, Altering, and Dropping Databases
Creating a Database with a Default Configuration
Viewing Database Information
Creating a Database Using File Options
Creating a Database with a User-Defined Filegroup
Setting Database User Access
Renaming a Database
Dropping a Database
Detaching a Database
Attaching a Database
Configuring Database Options
Viewing Database Options
Configuring ANSI SQL Options
Configuring Automatic Options
Creating or Modifying a Database to Allow External Access
Creating or Changing a Database to Use a Non-Server Default Collation
Configuring Cursor Options
Enabling Date Correlation Optimization
Modifying Database Parameterization Behavior
Enabling Read Consistency for a Transaction
Configuring Database Recovery Models
Configuring Page Verification
Controlling Database Access and Ownership
Changing a Database State to Online, Offline, or Emergency
Changing a Database Owner
Managing Database Files and Filegroups
Adding a Data File or Log File to an Existing Database
Removing a Data or Log File from a Database
Relocating a Data or Transaction Log File
Changing a File’s Logical Name
Increasing a Database’s File Size and Modifying Its Growth Options
Adding a Filegroup to an Existing Database
Setting the Default Filegroup
Removing a Filegroup
Making a Database or Filegroup Read-Only
Viewing and Managing Database Space Usage
Viewing Database Space Usage
Shrinking the Database or a Database File
CHAPTER 23 Database Integrity and Optimization
Database Integrity Checking
Checking Consistency of the Disk Space Allocation Structures with DBCC CHECKALLOC
Checking Allocation and Structural Integrity with DBCC CHECKDB
Tables and Constraints
Checking Allocation and Structural Integrity of All Tables in a Filegroup Using DBCC CHECKFILEGROUP
Checking Data Integrity for Tables and Indexed Views Using
DBCC CHECKTABLE
Checking Table Integrity with DBCC CHECKCONSTRAINTS
Checking System Table Consistency with DBCC CHECKCATALOG
Index Maintenance
Rebuilding Indexes
Defragmenting Indexes
Rebuilding a Heap
CHAPTER 24 Maintaining Database Objects and Object Dependencies
Database Object Maintenance
Changing the Name of a User-Created Database Object
Changing an Object’s Schema
Object Dependencies
Identifying Object Dependencies
Identifying Referencing and Referenced Entities
Viewing an Object’s Definition
CHAPTER 25 Database Mirroring
Database Mirroring in Context
Database Mirroring Architecture
Setting Up Database Mirroring
Creating Mirroring Endpoints
Backing Up and Restoring Principal Databases
Creating a Database Mirroring Session
Setup Summary
Operating Database Mirroring
Changing Operating Modes
Performing Failovers
Pausing or Resuming a Mirroring Session
Stopping Mirroring Sessions and Removing Endpoints
Monitoring and Configuring Options
Monitoring Mirror Status
Configuring the Connection Timeout Period
CHAPTER 26 Database Snapshots
Snapshot Basics
Creating and Querying Database Snapshots
Removing a Database Snapshot
Recovering Data with a Database Snapshot
CHAPTER 27 Linked Servers and Distributed Queries
Linked Server Basics
Creating a Linked Server to Another SQL Server Instance
Configuring Linked Server Properties
Viewing Linked Server Information
Dropping a Linked Server
Linked Server Logins
Adding a Linked Server Login Mapping
Viewing Linked Logins
Dropping a Linked Server Login Mapping
Executing Distributed Queries
Executing Distributed Queries Against a Linked Server
Creating and Using an Alias to Reference Four-Part Linked Server Names
Executing Distributed Queries Using OPENQUERY
Executing Ad Hoc Queries Using OPENROWSET
Reading Data from a File Using OPENROWSET BULK Options
Chapter 28 Query Performance Tuning
Query Performance Tips
Capturing and Evaluating Query Performance
Capturing Executing Queries Using sysdm_exec_requests
Viewing Estimated Query Execution Plans Using Transact-SQL Commands
Viewing Execution Runtime Information
Viewing Performance Statistics for Cached Query Plans
Viewing Aggregated Performance Statistics Based on Query or Plan Patterns
Identifying the Top Bottleneck
Identifying I/O Contention by Database and File
Index Tuning
Displaying Index Fragmentation
Displaying Index Usage
Statistics
Manually Creating Statistics
Creating Statistics on a Subset of Rows
Updating Statistics
Generating and Updating Statistics Across All Tables
Viewing Statistics Details
Removing Statistics
Miscellaneous Techniques
Using an Alternative to Dynamic SQL
Forcing SQL Server to Use a Query Plan
Applying Hints Without Modifying Application SQL
Creating Plan Guides from Cache
Checking the Validity of a Plan Guide
Parameterizing a Non-parameterized Query Using Plan Guides
Limiting Competing Query Resource Consumption
CHAPTER 29 Backup and Recovery
Creating a Backup and Recovery Plan
Making Backups
Performing a Basic Full Backup
Compressing Your Backups
Naming and Describing Your Backups and Media
Configuring Backup Retention
Striping Backup Sets
Using a Named Backup Device
Mirroring Backup Sets
Performing a Transaction Log Backup
Create Backups Without Breaking the Backup Sequence
Performing a Differential Backup
Backing Up Individual Files or Filegroups
Performing a Partial Backup
Viewing Backup Metadata
Restoring a Database
Restoring a Database from a Full Backup
Restoring a Database from a Transaction Log Backup
Restoring a Database from a Differential Backup
Restoring a File or Filegroup
Performing a Piecemeal (PARTIAL) Restore
Restoring a Page
Identifying Databases with Multiple Recovery Paths
Tags: Uncategorized
I came home to find a hard copy of my new book waiting for me. This is always an exciting and strange moment to finally hold the finished product in my hand. I’m pretty happy with the result, and I feel it reflects the problem/solution theme I was aiming for. It is always a little daunting once a book is printed, because you have to hand it off to the world and hope that people find it useful. You kind of have to just give in, hope for the best, and move on to the next project.
It is a bigger book than last time (last book was 733 pages, this one is 839). Although it was an update - it still took 9 months, but they were high quality hours and I enjoyed a good portion of it.
Looking through it today - I was disappointed to see that two of my requested changes that I submitted to the publisher in June didn’t get integrated into to Chapter 2 - so I’ll blog about them here so you can know to look for them. There were some syntax changes that were made in the Release Candidate that differ from CTP 6. The two changes that didn’t get in for whatever reason are as follows:
- Page 91 - the CTP6 version of the MERGE syntax, instead of the RC0 version was used. Here is the corrected, RC0 version of the syntax:
MERGE INTO HumanResources.CorporateHousing p
USING dbo.StagingCorporateHousing s
ON p.UnitNBR = s.UnitNBR
WHEN MATCHED AND s.IsRentedIND <> p.IsRentedIND THEN
UPDATE SET IsRentedIND = s.IsRentedIND
WHEN NOT MATCHED BY TARGET THEN
INSERT (UnitNBR, IsRentedIND) VALUES (s.UnitNBR, s.IsRentedIND)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
- Page 98 - the @partition_switch argument should be removed from sys.sp_cdc_enable_table
The rest of the RC0 changes I made and requested seem to be there, so I’m happy. We’ll see if there are any RTM changes I need to blog about. With 800 pages - the possibilities are endless. 
Tags: Uncategorized
Problem: Install of a new SQL Server Failover Cluster is failing. Looking in the Datastore.xml file shows a watsonFailedFunction = “ClusterInfoCollector::collectClusterGroups.”
Resolution: This particular failed function is often associated with one or more cluster resources being offline or failed during a SQL Server install. In the case I worked on, there was a bad Veritas cluster disk resource. They removed the resource, and the install succeeded.
Tags: SQL Tips
I was reading an article the other day that detailed how to collect file usage across user databases using the sys.dm_db_file_space_usage DMV. I did a double-take, because that DMV only applies to the tempdb system database. In all fairness and based on the name of this DMV, it would be easy to think this applies to all databases. Perhaps it was named as it was (in a more generic format) in preparation for expanding to other databases in a future version (as of SQL Server 2008, it still just applies to tempdb). Today the appropriate name should be something more like sys.dm_db_tempdb_file_space_usage…
So what could you use instead, to detect free space usage for a user database? I like to use the undocumented DBCC SHOWFILESTATS command. It is pretty straight-forward, returning the file id, file group id, total extents, used extents, logical file name and physical file name. If you are worried about using something undocumented, in this case wouldn’t be too concerned as this is the same command used in SQL Server Management Studio when you run the “Disk Usage” report.
Oh and by the way - if you are looking for transaction log file space usage statistics, you can use the undocumented DBCC SQLPERF(LOGSPACE) command. Again, this is being used under the covers for the “Disk Usage” SQL Server Management Studio reports.
Tags: SQL Tips
Problem:
User was trying to CREATE LOGIN for a Windows Domain account, [DG\example] on a case sensitive SQL Server instance and was seeing error message 15401, “Windows NT user or group ‘%s’ not found. Check the name again.” This was on a four node cluster. Checking one of the nodes, we found that the failing account was actually added as a member of the local admin group using upper-case (so it was created as DG\EXAMPLE). Looking in Active Directory however, the account was actually DG\example, lower-case. So even though we are trying to add the login to SQL Server with the correct lower-case, it looked like SQL Server was doing a local lookup based on the local node’s version of the name, which was upper-case. I used SUSER_SID to validate each version - lower case and upper case, and only the upper case version of the account worked.
Solution:
To resolve this issue, I followed the instructions in KB 946358, “The LsaLookupSids function may return the old user name instead of the new user name if the user name has changed on a domain controller.” After creating the registry entry and setting the value to “0″, the CREATE LOGIN of the account with the proper case was successful. I then removed the registry value to return to the default SID cache behavior (default maximum number is 128).
Tags: SQL Tips
Now that I am finished with the book, I’m looking forward to getting out into the community a little bit more. Being an introverted, behind-the-scenes kind of guy, I am sorry to say that I have never attended a PASS Minnesota SQL Server User group meeting. This is about to chance, as Lara Rubbelke was kind enough to invite me to join the “SQL Server Ask the Experts” panel coming up on July 15th. Should be fun to meet everyone. Each panelist will give a 5 minute tip or trick in his/her subject area - and then the rest of the time will be spent answering questions from the attendees. Those of you SQL Server professionals that live in the Twin Cities, please register for the event and stop by to say hello!
Tags: Random
