SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database | Journey to SQL Authority with Pinal Dave

UPDATE : SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database This is very long query. Optionally, we can limit the query to return results for one or more than one table. SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN… Continue Reading...

Source: SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query to Find ByteSize of All the Tables in Database | Journey to SQL Authority with Pinal Dave

SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'    ELSE ISNULL(sob.name, 'unknown') END AS Table_name,    SUM(sys.length) AS Byte_Length FROM sysobjects sob, syscolumns sys WHERE sob.xtype='u' AND sys.id=sob.id GROUP BY sob.name WITH CUBE Reference : Pinal Dave (http://blog.SQLAuthority.com) Continue Reading...

Source: SQL SERVER – Query to Find ByteSize of All the Tables in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database | Journey to SQL Authority with Pinal Dave

I always mark fields to be deprecated with "dep_" as prefix. In this way, after few days, when I am sure that I do not need the field any more I run the query to auto generate the deprecation script. The script also checks for any constraint in the system and auto generate the script… Continue Reading...

Source: SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created | Journey to SQL Authority with Pinal Dave

Following cursor query runs through database and find all the table with certain prefixed ('b_','delete_'). It also checks if the Table is more than certain days old or created before certain days, it will delete it. We can have any other opertation on that table like delete, print or reindex. SET NOCOUNT ON DECLARE @lcl_name… Continue Reading...

Source: SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created | Journey to SQL Authority with Pinal Dave

SQL SERVER – Cursor to Kill All Process in Database | Journey to SQL Authority with Pinal Dave

When you run the script please make sure that you run it in different database then the one you want all the processes to be killed. CREATE TABLE #TmpWho (spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150)) INSERT INTO #TmpWho EXEC sp_who DECLARE @spid INT DECLARE @tString VARCHAR(15) DECLARE… Continue Reading...

Source: SQL SERVER – Cursor to Kill All Process in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure | Journey to SQL Authority with Pinal Dave

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results. ----Option 1 SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%' ----Option 2 SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE… Continue Reading...

Source: SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure | Journey to SQL Authority with Pinal Dave

SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved. | Journey to SQL Authority with Pinal Dave

To fix the error which occurs after the Windows server name been changed, when trying to update or delete the jobs previously created in a SQL Server 2000 instance, or attaching msdb database. Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job… Continue Reading...

Source: SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved. | Journey to SQL Authority with Pinal Dave

SQL SERVER – Shrinking Truncate Log File – Log Full | Journey to SQL Authority with Pinal Dave

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(, 1) BACKUP LOG WITH TRUNCATE_ONLY DBCC SHRINKFILE(, 1) GO [Update:… Continue Reading...

Source: SQL SERVER – Shrinking Truncate Log File – Log Full | Journey to SQL Authority with Pinal Dave

SQL SERVER – Simple Example of Cursor | Journey to SQL Authority with Pinal Dave

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 CURSOR SET @getAccountID = CURSOR FOR SELECT… Continue Reading...

Source: SQL SERVER – Simple Example of Cursor | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database | Journey to SQL Authority with Pinal Dave

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 database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp… Continue Reading...

Source: SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query Analyzer Shortcuts | Journey to SQL Authority with Pinal Dave

Download Query Analyzer Shortcuts (PDF) Shortcut Function Shortcut Function ALT+BREAK Cancel a query CTRL+SHIFT+F2 Clear all bookmarks ALT+F1 Database object information CTRL+SHIFT+INSERT Insert a template ALT+F4 Exit CTRL+SHIFT+L Make selection lowercase CTRL+A Select all CTRL+SHIFT+M Replace template parameters CTRL+B Move the splitter CTRL+SHIFT+P Open CTRL+C Copy CTRL+SHIFT+R Remove comment CTRL+D Display results in grid format… Continue Reading...

Source: SQL SERVER – Query Analyzer Shortcuts | Journey to SQL Authority with Pinal Dave

SQL SERVER – CASE Statement/Expression Examples and Explanation | Journey to SQL Authority with Pinal Dave

CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions. Two basic formulations for CASE expression 1) Simple CASE expressions A simple CASE expression… Continue Reading...

Source: SQL SERVER – CASE Statement/Expression Examples and Explanation | Journey to SQL Authority with Pinal Dave

SQL SERVER – 64 bit Architecture and White Paper | Journey to SQL Authority with Pinal Dave

In supportability, manageability, scalability, performance, interoperability, and business intelligence, SQL Server 2005 provides far richer 64-bit support than its predecessor. This paper describes these enhancements. Read the original paper here. Following abstract is taken from the same paper. Another interesting article on 64-bit Computing with SQL Server 2005 is here. The primary differences between the… Continue Reading...

Source: SQL SERVER – 64 bit Architecture and White Paper | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Introduction | Journey to SQL Authority with Pinal Dave

UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. Hello All, Thank you all for sending request for Interview Questions and Answers. It begins from today. This is six part series. I will update this post once… Continue Reading...

Source: SQL Server Interview Questions and Answers – Introduction | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 1 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What is RDBMS? Relational Data Base Management Systems (RDBMS) are database management systems that… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 1 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 2 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What is the difference between clustered and a non-clustered index? A clustered index is… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 2 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 3 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What is a NOLOCK? Using the NOLOCK query optimiser hint is generally considered good… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 3 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 4 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What is SQL Profiler? SQL Profiler is a graphical tool that allows system administrators… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 4 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 5 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What command do we use to rename a db? sp_renamedb ‘oldname’ , ‘newname’ If… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 5 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 6 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What are the properties of the Relational tables? Relational tables have six properties: Values… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 6 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Complete List Download | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. Download SQL Server Interview Questions and Answers Complete List Thank you all for your… Continue Reading...

Source: SQL Server Interview Questions and Answers Complete List Download | Journey to SQL Authority with Pinal Dave

SQL SERVER – Fix : Error: 1418 – Microsoft SQL Server – The server network address can not be reached or does not exist. Check the network address name and reissue the command | Journey to SQL Authority with Pinal Dave

Error: 1418 - Microsoft SQL Server - The server network address can not be reached or does not exist. Check the network address name and reissue the command The server network endpoint did not respond because the specified server network address cannot be reached or does not exist. Fix/Workaround/Solution: Step 1) Your system Firewall should… Continue Reading...

Source: SQL SERVER – Fix : Error: 1418 – Microsoft SQL Server – The server network address can not be reached or does not exist. Check the network address name and reissue the command | Journey to SQL Authority with Pinal Dave

SQL SERVER – @@DATEFIRST and SET DATEFIRST Relations and Usage | Journey to SQL Authority with Pinal Dave

The master database's syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as first day of the week using SET DATEFIRST 5 This will… Continue Reading...

Source: SQL SERVER – @@DATEFIRST and SET DATEFIRST Relations and Usage | Journey to SQL Authority with Pinal Dave

SQL SERVER – Raid Configuration – RAID 10 | Journey to SQL Authority with Pinal Dave

I get question about what configuration of redundant array of inexpensive disks (RAID) I use for my SQL Servers. The answer is short is: RAID 10. Why? Excellent performance with Read and Write. RAID 10 has advantage of both RAID 0 and RAID 1. RAID 10 uses all the drives in the array to gain… Continue Reading...

Source: SQL SERVER – Raid Configuration – RAID 10 | Journey to SQL Authority with Pinal Dave

SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index | Journey to SQL Authority with Pinal Dave

Clustered Index Only 1 allowed per table Physically rearranges the data in the table to conform to the index constraints For use on columns that are frequently searched for ranges of data For use on columns with low selectivity Non-Clustered Index Up to 249 allowed per table Creates a separate list of key values with… Continue Reading...

Source: SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table | Journey to SQL Authority with Pinal Dave

Following script will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table. SELECT IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE TABLE' Reference : Pinal Dave… Continue Reading...

Source: SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table | Journey to SQL Authority with Pinal Dave

SQL SERVER – 2005 Query Analyzer – Microsoft SQL SERVER Management Studio | Journey to SQL Authority with Pinal Dave

Following may be very simple to some and helpful to other type of question. I have seen this in my server log as well as this has been always first question in my Developer Team. Where is SQL SERVER 2005 Query Analyzer? SQL SERVER 2005 has combined Query Analyzer and Enterprise Manager into one Microsoft… Continue Reading...

Source: SQL SERVER – 2005 Query Analyzer – Microsoft SQL SERVER Management Studio | Journey to SQL Authority with Pinal Dave

SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server. | Journey to SQL Authority with Pinal Dave

An error has occurred while establishing a connection to the server when connecting to SQL server 2005, this failure may be caused by the fact that under default settings SQL server does not allow remote connection. ( provider: Named Pipes Provider, error: 40 - could not open a connection to SQL server. ) Fix/Workaround/Solution: Step… Continue Reading...

Source: SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server. | Journey to SQL Authority with Pinal Dave

SQL SERVER – 2005 Collation Explanation and Translation | Journey to SQL Authority with Pinal Dave

Just a day before one of our SQL SERVER 2005 needed Case-Sensitive Binary Collation. When we install SQL SERVER 2005 it gives options to select one of the many collation. I says in words like 'Dictionary order, case-insensitive, uppercase preference'. I was confused for little while as I am used to read collation like 'SQL_Latin1_General_Pref_Cp1_CI_AS_KI_WI'.… Continue Reading...

Source: SQL SERVER – 2005 Collation Explanation and Translation | Journey to SQL Authority with Pinal Dave

SQL SERVER – Six Properties of Relational Tables | Journey to SQL Authority with Pinal Dave

Relational tables have six properties: Values Are Atomic This property implies that columns in a relational table are not repeating group or arrays. The key benefit of the one value property is that it simplifies data manipulation logic. Such tables are referred to as being in the "first normal form" (1NF). Column Values Are of… Continue Reading...

Source: SQL SERVER – Six Properties of Relational Tables | Journey to SQL Authority with Pinal Dave

SQL SERVER – TRIM() Function – UDF TRIM() | Journey to SQL Authority with Pinal Dave

SQL Server does not have function which can trim leading or trailing spaces of any string. TRIM() is very popular function in many languages. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. I was expecting SQL Server 2005 to have TRIM() function. Unfortunately, SQL Server 2005 does not have… Continue Reading...

Source: SQL SERVER – TRIM() Function – UDF TRIM() | Journey to SQL Authority with Pinal Dave

SQL SERVER – 2005 Take Off Line or Detach Database | Journey to SQL Authority with Pinal Dave

EXEC sp_dboption N'mydb', N'offline', N'true' OR ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS OR ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds. The default… Continue Reading...

Source: SQL SERVER – 2005 Take Off Line or Detach Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – ERROR Messages – sysmessages error severity level | Journey to SQL Authority with Pinal Dave

SQL ERROR Messages Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error. The error severity levels provide a quick reference for you about the nature of the error. The error state number is an integer value between 1 and 127; it represents information about… Continue Reading...

Source: SQL SERVER – ERROR Messages – sysmessages error severity level | Journey to SQL Authority with Pinal Dave

SQL SERVER – Alternate Fix : ERROR 1222 : Lock request time out period exceeded | Journey to SQL Authority with Pinal Dave

ERROR 1222 : Lock request time out period exceeded. MSDN Suggests solution here. It says find offending transaction and terminate it and run the query again. Though sometime there is requirement that we can not terminate anything. If we know which transaction is locking up resources and database, we need to still run the same… Continue Reading...

Source: SQL SERVER – Alternate Fix : ERROR 1222 : Lock request time out period exceeded | Journey to SQL Authority with Pinal Dave

SQL SERVER – 2005 – DBCC ROWLOCK – Deprecated | Journey to SQL Authority with Pinal Dave

Title says all. My search engine log says many web users are looking for DBCC ROWLOCK in SQL SERVER 2005. It is deprecated feature for SQL SERVER 2005. It is Automatically on for SQL SERVER 2005. More Deprecated Features of SQL SERVER 2005 Refer MSDN Discontinued Database Engine Functionality in SQL Server 2005. Reference :… Continue Reading...

Source: SQL SERVER – 2005 – DBCC ROWLOCK – Deprecated | Journey to SQL Authority with Pinal Dave

SQL SERVER – Enable xp_cmdshell using sp_configure | Journey to SQL Authority with Pinal Dave

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. ---- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO ---- To update the currently configured value for advanced options. RECONFIGURE GO ---- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO ---- To update the currently configured value for this feature. RECONFIGURE GO Reference : Pinal Dave (http://blog.SQLAuthority.com) Continue Reading...

Source: SQL SERVER – Enable xp_cmdshell using sp_configure | Journey to SQL Authority with Pinal Dave

SQL SERVER – Difference Between Unique Index vs Unique Constraint | Journey to SQL Authority with Pinal Dave

Unique Index and Unique Constraint are the same. They achieve same goal. SQL Performance is same for both. Add Unique Constraint ALTER TABLE dbo. ADD CONSTRAINT UNIQUE NONCLUSTERED ( ) ON [PRIMARY] Add Unique Index CREATE UNIQUE NONCLUSTERED INDEX ON dbo. ( ) ON [PRIMARY] There is no difference between Unique… Continue Reading...

Source: SQL SERVER – Difference Between Unique Index vs Unique Constraint | Journey to SQL Authority with Pinal Dave

SQL SERVER – SELECT vs. SET Performance Comparison | Journey to SQL Authority with Pinal Dave

Usage: SELECT : Designed to return data. SET : Designed to assign values to local variables. While testing the performance of the following two scripts in query analyzer, interesting results are discovered. SET @foo1 = 1; SET @foo2 = 2; SET @foo3 = 3; SELECT @foo1 = 1, @foo2 = 2, @foo3 = 3; While… Continue Reading...

Source: SQL SERVER – SELECT vs. SET Performance Comparison | Journey to SQL Authority with Pinal Dave

SQL SERVER – 2005 Locking Hints and Examples | Journey to SQL Authority with Pinal Dave

Locking Hints and Examples are as follows. The usage of them is same same but effect is different. ROWLOCK Use row-level locks when reading or modifying data. PAGLOCK Use page-level locks when reading or modifying data. TABLOCK Use a table lock when reading or modifying data. DBLOCK Use a database lock when reading or modifying… Continue Reading...

Source: SQL SERVER – 2005 Locking Hints and Examples | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query to Retrieve the Nth Maximum Value | Journey to SQL Authority with Pinal Dave

Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined. SELECT * FROM Employee E1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary > E1.Salary) In the above example, the inner query uses a value of the outer query in its filter… Continue Reading...

Source: SQL SERVER – Query to Retrieve the Nth Maximum Value | Journey to SQL Authority with Pinal Dave

SQL SERVER – Good, Better and Best Programming Techniques | Journey to SQL Authority with Pinal Dave

A week ago, I was invited to meeting of programmers. Subject of meeting was "Good, Better and Best Programming Techniques". I had made small note before I went to meeting, so if I have to talk about or discuss SQL Server it can come handy. Well, I did not get chance to talk on that… Continue Reading...

Source: SQL SERVER – Good, Better and Best Programming Techniques | Journey to SQL Authority with Pinal Dave

SQL SERVER – Restrictions of Views – T SQL View Limitations | Journey to SQL Authority with Pinal Dave

UPDATE: (5/15/2007) Thank you Ben Taylor for correcting errors and incorrect information from this post. He is Database Architect and writes Database Articles at www.sswug.org. I have been coding as T-SQL for many years. I never have to use view ever in my career. I do not see in my near future I am using… Continue Reading...

Source: SQL SERVER – Restrictions of Views – T SQL View Limitations | Journey to SQL Authority with Pinal Dave

SQL SERVER – Explanation SQL SERVER Merge Join | Journey to SQL Authority with Pinal Dave

The Merge Join transformation provides an output that is generated by joining two sorted data sets using a FULL, LEFT, or INNER join. The Merge Join transformation requires that both inputs be sorted and that the joined columns have matching meta-data. User cannot join a column that has a numeric data type with a column… Continue Reading...

Source: SQL SERVER – Explanation SQL SERVER Merge Join | Journey to SQL Authority with Pinal Dave

SQL SERVER – Replication Keywords Explanation and Basic Terms | Journey to SQL Authority with Pinal Dave

While discussing replication with Jr. DBAs at work, I realize some of them have not experienced replication feature of SQL SERVER. Following is quick reference of replication keywords I created for easy conversation. Publisher The publisher is the source database where replication begins. It makes data available for replication. Subscriber The subscriber is the destination… Continue Reading...

Source: SQL SERVER – Replication Keywords Explanation and Basic Terms | Journey to SQL Authority with Pinal Dave

SQL SERVER – Random Number Generator Script – SQL Query | Journey to SQL Authority with Pinal Dave

Random Number Generator There are many methods to generate random number in SQL Server. Method 1 : Generate Random Numbers (Int) between Rang ---- Create the variables for the random number generation DECLARE @Random INT; DECLARE @Upper INT; DECLARE @Lower INT ---- This will create a random number between 1 and 999 SET @Lower =… Continue Reading...

Source: SQL SERVER – Random Number Generator Script – SQL Query | Journey to SQL Authority with Pinal Dave

SQL SERVER – 2005 Security DataSheet | Journey to SQL Authority with Pinal Dave

Microsoft has implemented strong security features into the Microsoft® SQL Serverâ„¢ 2005, which provides a security-enabled platform for enterprise-class relational database and analysis solutions. SQL Server 2005 provides cutting edge security technology and addresses several security issues, including automatic secured updates and encryption of sensitive data. Download the SQL Server 2005 Security DataSheet from SQLAuthority.com… Continue Reading...

Source: SQL SERVER – 2005 Security DataSheet | Journey to SQL Authority with Pinal Dave

SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1 | Journey to SQL Authority with Pinal Dave

While moving some of the script from SQL SERVER 2000 to SQL SERVER 2005 our migration team faced following error. Msg 3159, Level 16, State 1, Line 1 The tail of the log for the database "AdventureWorks" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work… Continue Reading...

Source: SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1 | Journey to SQL Authority with Pinal Dave

SQL SERVER – SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query | Journey to SQL Authority with Pinal Dave

A SET ROWCOUNT statement simply limits the number of records returned to the client during a single connection. As soon as the number of rows specified is found, SQL Server stops processing the query. The syntax looks like this: SET ROWCOUNT 10 SELECT * FROM dbo.Orders WHERE EmployeeID = 5 ORDER BY OrderDate SET ROWCOUNT… Continue Reading...

Source: SQL SERVER – SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query | Journey to SQL Authority with Pinal Dave

SQL SERVER – Collate – Case Sensitive SQL Query Search | Journey to SQL Authority with Pinal Dave

Case Sensitive SQL Query Search If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records. SELECT Column1 FROM Table1 WHERE Column1 = 'casesearch' To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to… Continue Reading...

Source: SQL SERVER – Collate – Case Sensitive SQL Query Search | Journey to SQL Authority with Pinal Dave

SQLAuthority.com 100th Post – Gratitude Note to Readers | Journey to SQL Authority with Pinal Dave

Hello All, I would like to express my deep gratitude to all of my readers for their emails, comments, suggestions and continuous support on the occasion of 101st post on this blog. I would like to extend my gratitude to my parents. In good times or trying times my parents are there with me always.… Continue Reading...

Source: SQLAuthority.com 100th Post – Gratitude Note to Readers | Journey to SQL Authority with Pinal Dave