Journey to SQL Authority with Pinal DaveJourney to SQL Authority with Pinal DavePinalkumar Dave is the author of many SQL Server articles. He has five years experience as a Principal Database Administrator and Web Application Architecture Developer in MS SQL Server 2005 2000 and ColdFusion MX. He has a Masters of Science degree Articles
SQL SERVER - 2005 - Introduction to sys.dm_exec_query_optimizer_info
2008-03-28 02:30:00 Many times when I am just bored I surf Book On Line for SQL Server 2005. Almost all the time I find something new which makes me believe that I have lot to learn and there are so many things I am not aware of. Today I found system catalog view sys.dm_exec_query_optimizer_info. I just enjoyed ... More About: Introduction
SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performan
2008-03-27 02:30:00 Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I wrote to find fragmentation of ... More About: Find , Server , Details , Slow , Index
SQLAuthority News - Few Links About SQLAuthority
2008-03-26 02:30:00 I have listed few important links of SQLAuthority.com, I still receive some repeated questions. I do my best to respond to all of my readers, however, most of the time I am sending them link to one of my previously written article. Many times most of the answers can be found right away by ... More About: News , Links
SQL SERVER - Simple Puzzle Using Union and Union All - Answer
2008-03-25 02:30:00 Yesterday I posted a puzzle SQL SERVER - Simple Puzzle Using Union and Union All, today we will see the answer of this. Following image explains the answer of puzzle. You can read the explanation of why this is answer read my previous article SQL SERVER - Union vs. Union All - Which is better for ... More About: Answer , Server
SQL SERVER - Simple Puzzle Using Union and Union All
2008-03-24 02:30:00 I often get request to write puzzles using SQL Server . Today, I am presenting one very simple but very interesting puzzle. What will be the output of following two SQL Scripts. First try to answer without running this two script in Query Editor. Script 1 SELECT 1 UNION ALL (SELECT 1 UNION SELECT 2) GO Script 2 (SELECT 1 UNION ALL SELECT 1) UNION SELECT 2 GO Hint : This puzzle ... More About: Puzzle , Union , Simple
SQL SERVER - 2005 - Mechanisms to Ensure Integrity and Consistency of Datab
2008-03-23 02:30:00 Today I was going through Book On Line while researching something, I come across one interesting small article about two mechanisms to ensure integrity and consistency of databases - 1) Locking 2) Row Versioning Let us see their definition from Book Online Itself. Locking Each transaction requests locks of different types on the resources, such as rows, pages, ... More About: Technology , Database , Server , Integrity , Consistency
SQL SERVER - 2005 - Find Highest / Most Used Stored Procedure
2008-03-22 02:30:00 How many times we all DBA’s might have wonder which stored procedure is executing most in the database? I have wondered it often and I have written following small script which gives me answer to my above questions. I am also retrieving few additional data along with the highest used SP names. You can change the ... More About: Find , Server
SQL SERVER - Introduction to Live Lock - What is Live Lock?
2008-03-21 02:30:00 Some questions are very interesting to answer. I just received following question in Email. What is Live Lock ? A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which ... More About: Introduction , Server
SQLAuthority News - Book Review - Joe Celkos SQL Puzzles and Answers, Secon
2008-03-20 02:30:00 Joe Celko’s SQL Puzzles and Answers, Second Edition, Second Edition (The Morgan Kaufmann Series in Data Management Systems) (Paperback) by Joe Celko (Author) Link to Amazon (This is not affiliate Link) Short Review : This book is for all of them who enjoy little puzzles or just something which gives them challenge. Some puzzles took hours to solve and some ... More About: News , Technology , Book Review , Book
SQL SERVER - Add Column With Default Column Constraint to Table
2008-03-19 02:30:00 Just a day ago while working with database Jr. Developer asked me question how to add column along with column constraint. He also wanted to specify the name of the constraint. The newly added column should not allow NULL value. He requested my help as he thought he might have to write many lines to ... More About: Column , Server , Table
SQL SERVER - 2005 - Analysis Services Query Performance Top 10 Best Practic
2008-03-18 02:30:00 Analysis Services Query Performance Top 10 Best Practices Optimize cube and measure group design Define effective aggregations Use partitions Write efficient MDX Use the query engine cache efficiently Ensure flexible aggregations are available to answer queries. Tune memory usage Tune processor usage Scale up where possible Scale out when you can no longer scale up Technet More About: Technology , Analysis , Database
SQLAuthority News - Download White Papers - Migration from MySQL, Oracle, S
2008-03-17 02:30:00 Guide to Migrating from MySQL to SQL Server 2005 This migration guide explains the differences between the MySQL and SQL Server 2005 database platforms, and the steps necessary to convert a MySQL database to SQL Server. Guide to Migrating from Oracle to SQL Server 2005 This white paper explores challenges that arise when you migrate from an Oracle ... More About: News , Technology , Download , White
SQL SERVER - 2005 - Retrieve Any User Defined Object Details Using sys.obje
2008-03-16 02:30:00 sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database. Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of foreign key, name of ... More About: User , Server , Object , Details , Defined
SQL SERVER - 2005 - Retrieve Processes Using Specified Database
2008-03-15 02:30:00 Blog Reader Jim Sz posted quick but very interesting script. If user want to know how many processes are there in any particular database it can be retrieved querying sys.processes database. USE master GO DECLARE @dbid INT SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = ‘AdventureWorks’ IF EXISTS (SELECT spid ... More About: Database , Server , Processes
SQL SERVER - 2005 - Introduction to CLR and Integration to CLR with SQL - S
2008-03-14 02:30:00 It is always interesting to learn new technology. I always wanted to learn about CLR but I somehow never got chance to practice on it. I had requested excellent blog writer and .net expert Chirag Darji to write introduction to CLR and integration with SQL Server . I think he has posted some great stuff on ... More About: Technology , Integration , Introduction
SQL SERVER - FIX : Error : 3702 Cannot drop database because it is currentl
2008-03-13 02:30:00 Following error is very generic error and I have previously written SQL SERVER - FIX : Error : 3702 Cannot drop database because it is currently in use. Msg 3702, Level 16, State 3, Line 2 Cannot drop database ?DataBaseName? because it is currently in use. One of the reader Dave have posted additional information in comments. ... More About: Database , Server , Drop
SQL SERVER - 2005 - Find Nth Highest Record from Database Table - Using Ran
2008-03-12 02:30:00 I have previously written SQL SERVER - 2005 - Find Nth Highest Record from Database Table where I have shown query to find 4th highest record from database table. Everytime when I write blog I am always very eager to read comments of readers. Some of regular readers are industry leaders and and their ... More About: Server
SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL - Par
2008-03-11 02:30:00 Please read SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL before continuing this article. I had asked users to come up with alternate solution of the same problem. Khadar Khan came up with good solution using CTE SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL ... More About: Server , Bottom
SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL - Par
2008-03-10 02:30:00 Please read SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL before continuing this article. I had asked users to come up with alternate solution of the same problem. Let us see following example provided by Khadar Khan. This example is using newly introduced concept of CTE of SQL Server 2005. CTE ... More About: Bottom
SQLAuthority News - Authors Most Visited Article on Blog
2008-03-09 02:30:00 I received many emails regarding SQLAuthority News - 500th Post - An Interesting Journey with SQL Server. One of the email asked interesting question regarding my most visited article on this blog. It was interesting to know that reader wants to know which article I visit the most. Following is the link to the article which ... More About: Technology , Article , Blog , Authors
SQL SERVER - 2005 - Find Nth Highest Record from Database Table
2008-03-08 02:30:00 I have previously written SQL SERVER - Query to Retrieve the Nth Maximum value. I just received email that if I can write this using AdventureWorks database as it is default sample database for SQL Server 2005 and user can run the query against it and understand it better. Here is query to find Nth Highest ... More About: Find , Database , Table , Record
SQLAuthority News - 500th Post - An Interesting Journey with SQL Server
2008-03-07 02:30:00 I am very pleased to write my 500th post. After 500 posts, I still have same feeling when I wrote first post on this blog. I would like to thank my family for their continuous support in writing this blog. Most of all I want to thank all of YOU for being wonderful readers of ... More About: News , Interesting , Journey , Post , Server
SQLAuthority News - SQL Server 2005 is The Data Platform Leader
2008-03-06 02:30:00 Questions I often get asked : How big is market for SQL Server ? Is SQL Server industry leader? Does learning SQL Server technology will help future career? Why did you pick SQL Server as your expertise? I just love SQL Server. Let us read following article taken directly from Microsoft, which explains why SQL Server is Data Platform Leader . Microsoft is positioned ... More About: News
SQL SERVER - Simple Example of Cursor - Sample Cursor Part 2
2008-03-05 02:30:00 I have recently received email that I should update SQL SERVER - Simple Example of Cursor with example of AdventureWorks database. Simple Example of Cursor using AdventureWorks Database is listed here. USE AdventureWorks GO DECLARE @ProductID INT DECLARE @getProductID CURSOR SET @getProductID = CURSOR FOR ... More About: Server , Part , Sample
SQL SERVER - 2005 - A Simple Way To Defragment All Indexes In A Database Th
2008-03-04 02:30:00 Just a day ago, I received email from regular reader Rajiv Kayasthy about a script which demonstrates the A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold. He found this script on TechNet BOL and was attempting to run on SQL Server but was getting continuous error Msg ...
SQL SERVER - Sharpen Your Basic SQL Server Skills - Learn the distinctions
2008-03-03 02:30:00 Read my article in SQL Server Magazine March 2007 Edition I will be not able to post complete article here due to copyright issues. Please visit the link above to read the article. More About: Basic , Learn , Skills
SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL
2008-03-02 02:30:00 Just a day ago, while working with some inventory related projects, I faced one interesting situation. I had to find TOP 1 and BOTTOM 1 record together. I right away that I should just do UNION but then I realize that UNION will not work as it will only accept one ORDER BY clause. If ... More About: Server , Bottom
SQL SERVER - Transfer The Logins and The Passwords Between Instances of SQL
2008-03-01 02:30:00 This question was asked to me by one of reader. “I just upgraded my server with better hardware and newer operating system. How can I transfer the logins and the passwords between two of my SQL Server ?” I think Microsoft has wonderful documentation for this issue. kb 918992 I will briefly describe the solution here : Download the ... More About: Technology , Transfer , Passwords
SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryp
2008-02-29 02:30:00 SQL Server 2005 provides encryption as a new feature to protect data against the attacks of hackers. Hackers may be able to get hold of the database or tables, but they wouldn’t understand the data or be able to use it. It is very important to encrypt crucial security related data ... More About: Encryption , Introduction
SQL SERVER - Dynamic Case Statement - FIX : ERROR 156 : Incorrect syntax ne
More articles from this author:2008-02-28 02:30:00 One of my friend sent me query asking me how to generate dynamic case statements in SQL. Everytime he tries to run following query he is getting Error 156 : Incorrect syntax near the keyword. He was frustrated with following two queries. There are two different ways to solve the problem when user want to Incorrect ... More About: Server , Case , Syntax , Dynamic 1, 2, 3, 4, 5, 6, 7 |
|
 |
|
 |
