SQL Scripts Archives - Page 317 of 318 - SQL Authority with Pinal Dave

SQL SERVER – Function to Convert List to Table

February 10, 2007

Update : (5/5/2007) I have updated the UDF to support SQL SERVER 2005. Visit :SQL SERVER – UDF – Function to Convert List to Table Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)

SQL SERVER – Primary Key Constraints and Unique Key Constraints

February 5, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Database, Software Development, SQL Coding Standards, SQL Constraint and Keys, SQL Download, SQL Error Messages, SQL Index, SQL Joins, SQL Scripts, SQL Server Security

Primary Key: Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls. Create table with Primary Key: CREATE TABLE Authors ( AuthorID INT NOT NULL PRIMARY KEY, Name VARCHAR(100) NOT NULL ) GO…

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

February 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

Following function will convert any string to Title Case. I have this function for long time. I do not remember that if I wrote it myself or I modified from original source. Run Following T-SQL statement in query analyzer: SELECT dbo.udf_TitleCase('This function will convert this string to title case!') The…

SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables

January 31, 2007

Pinal Dave

SQL Performance

Best Practices, SQL Constraint and Keys, SQL Cursor, SQL Index, SQL Joins, SQL Scripts, SQL Server DBCC, SQL Stored Procedure

SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions. Let us learn how to do ReIndexing Database Tables and Update Statistics on Tables.

SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

January 10, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure

USE DatabaseName GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate…

SQL SERVER – Simple Example of Cursor

January 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts

UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2 This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL. DECLARE @AccountID INT DECLARE @getAccountID…

SQL SERVER – Shrinking Truncate Log File – Log Full

December 30, 2006

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Backup and Restore, SQL Log, SQL Scripts

UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008. Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible. USE DatabaseName GO DBCC SHRINKFILE(<TransactionLogName>, 1) BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY…