Feeds:
Posts
Comments

Archive for the ‘SQL Server’ Category

SQL SERVER – Weekly Series – Memory Lane – #028

Posted in Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 11, 2013 | Leave a Comment »

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

UDF – Function to Convert List to Table
Article contains UDF written for SQL SERVER 2005. It will also work well with the very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR. It will convert the list to table.

Creating Comma Separate Values List from Table – UDF – SP
Script contained in the blog will create common separate values (CSV) or common separate list from tables, convert list to table. Following script is written for SQL SERVER 2005. It will also work well with the very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR (MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

Examples and Explanation for GOTO
GOTO statement can be used anywhere within a procedure, batch, or function. GOTO can be nested as well. GOTO can be executed by any valid user on SQL SERVER. GOTO can co-exists with other control of flow statements (IF…ELSE, WHILE). GOTO can only go(jump) to label in the same batch, it can not go to label outside of the batch.

Index Optimization CheckList
Index optimization is always interesting subject to me. Every time I receive request to help optimize query or query on any specific table. I always ask Jr.DBA to go over following list first before I take a look at it. Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.

Explanation SQL Command GO
GO is not a Transact-SQL statement; it is often used in T-SQL code. Go causes all statements from the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independent of any other batches. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

2008

Here are two straight to script blog posts where I explain how to do something quickly and easily.

2009

Find Last Date Time Updated for Any Table
If a user wants to finds out when was the last table updated he can query dynamic management view (dmv) – sys. dm_db_index_usage_stats and easily figure out when the table was updated last. Let us comprehend this example by creating a table and updating it. We can use dmv to determine when it was updated last.

Questions and Answers with Database Administrators
Interesting conversation – I recommend everyone to read this.

Q. According to you what goes into making the best Database Administrator?

A. The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.

When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

2010

Get Latest SQL Query for Sessions – DMV
A straight to script blog which is my favorite as well.

Size of Index Table for Each Index – Solution 2
Another blog post where I explain how to find size of index using T-SQL script.

Spatial Database Queries – What About BLOB
In this blog post I explain how to find distance between two cities using Spatial Database.

Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
Not everyone knows the difference between ALTER INDEX ALL REBUILD and disabled Clustered index. In this blog post, I have tried to explain the same with example.

2011

Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
One of the most popular blog post because it is the basic requirement of the developer. Developers like to do things their own way on their database. This blog post explains how developers can absolutely create another replica of the database and later do practice over it.

What Kind of Lock WITH (NOLOCK) Hint Takes on Object?
Do we know what kind of lock WITH(NOLOCK) hint takes on object. Answer is Schema Lock. Yes, WITH(NOLOCK) hint takes Schema Lock on the object which is accessed. In this blog post we have working example where I demonstrate the same.

Resource Database ID – 32767
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

2012

In year 2012 in a single week, I had come up with the Quiz based on the Joes 2 Pros Book and here are the five questions asked in the quiz. Just like every other quiz, this quiz also had a video hints. Something very unique and never been attempted.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

Read Full Post »

SQL SERVER – Adding Column Defaulting to Current Datetime in Table

Posted in PostADay, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 10, 2013 | 7 Comments »

Presenting a technical session is a greatest experience one can have and I enjoy doing the same. While I write this blog post, I am presenting at Great Indian Developer Summit in India. The event is a grand success and I am having a great time at this event. One of the questions which I often receive is how do one can add the column to existing table which will be auto-populated with the current datetime when the original row is inserted. There is indeed a simple solution to achieve this goal. One has to just create table with default value as a current datetime.

In following example we will first create a sample table and later we will add a column which will be defaulted to the current date time when any new record is inserted. The only drawback of this method is that if there is any existing row in your table it will be automatically have the current date time when the column is created. Honestly I do not see any solution to this issue as this is related to design of the database. If you know what was the datetime when rows were created you can update those rows with those value otherwise, just have any values stored there.

Let us see our solution. Let us first create a table which does not have column with current datetime. In our case we will assume that there are only two rows in the table.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (ID INT, Col1 VARCHAR(100));
-- Insert Values
INSERT INTO TestTable (ID, Col1)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second';
-- Select from table
SELECT *
FROM TestTable
GO

Now let us add a column to this table with default value as a current datetime. You will notice that the two rows which are inserted into the table have current datetime.

-- Add Column with Default Current Date Time
ALTER TABLE TestTable
ADD DateInserted DATETIME NOT NULL DEFAULT (GETDATE());
-- Select from table
SELECT *
FROM TestTable
GO

As a third step let us enter the a new row. Make sure that you do not insert any value in the newly created column where you have default value as a current date time.

-- Now Insert New Rows
INSERT INTO TestTable (ID, Col1)
SELECT 3, 'Third';
INSERT INTO TestTable (ID, Col1)
SELECT 4, 'Fourth';
GO
-- Select from table
SELECT *
FROM TestTable
GO

You will notice in the result set that the new column will contain current date time of the row created. This way you can get the value when the row was created.

Now you can clean up the resultset.

-- Clean up
DROP TABLE TestTable
GO

Here is the question back to you – “It is simple to create a column where we have default daytime value to know when the row was created. Is there any way to know when the row was updated without explicitly updating any column with datetime?”

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

SQL SERVER – A New Approach to Scale .NET Applications

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology, tagged , on May 9, 2013 | Leave a Comment »

In a previous article, I wrote about scale-up vs. scale-out architectures using SQL Server and NuoDB as examples.  NuoDB recently announced the general availability of their latest product release, 1.1, and it looks like they’ve made significant progress in improving their Microsoft support. NuoDB now supports 64-bit Windows environments, natively integrates with Visual Studio, LINQ and EntityFramework to name a few.

For those of you who haven’t had a chance to read my previous article, NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.

The NuoDB distributed architecture enables you to scale computational and storage resources independently. Meaning, if your application demands higher transactional throughput, simply add more transaction engines, and if you need a highly available redundant database, add more storage managers.

A typical scale-out architecture might look like this:

Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.

Many of you have been developing, deploying and maintaining .NET applications using SQL Server and might be familiar with setting up a server farm or application cluster to improve performance. As you know, this is no easy task, from both a development and deployment perspective.  I’m not advocating that you eliminate SQL Server from your stack, but let’s entertain the idea of using NuoDB as the primary database for your application backend. Since it presents itself as a single logical database to your application regardless of how many machines it might be running on, you’re able to simplify your code and your scale-out strategy. The transaction engine layer of NuoDB uses a dynamic caching mechanism to eliminate the need to shard or federate your data. Transaction engines connect directly to client applications and are able to process all transaction types (CRUD) and maintain data consistency across the entire deployment environment. This opens up the opportunity to write applications specifically designed for a scale-up architecture and leverage NuoDB to provide scale-out capabilities. Below is an example of co-locating transaction engines on each node in a server farm.

 The genius of this system is that co-located transaction engines should be the most responsive to the application. However, in the event that one or more transaction engines get tied up, the broker process automatically connects applications to the next most responsive. There is no prerequisite for the data to actually be present on the transaction engine. NuoDB uses a built-in asynchronous replication technique to get data directly from other transaction engines or storage managers.

I have been following NuoDB for some time now and with each release the technology becomes more exciting. Click here if you would like to try out the latest 64-bit version of NuoDB that includes an ADO.NET driver for Visual Studio integration. Comments welcome!

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051

Posted in Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology, Video, tagged on May 8, 2013 | 3 Comments »

This is the 51th episode of SQL in Sixty Seconds Video and we will see in this episode how to RESEED identity of the table column. Identity column is every increasing (or decreasing) value based on the interval specified in its property. In today’s SQL in Sixty Seconds video we will see that how we can reseed the identity value to any other value. In the video I demonstrate that we can set the value to any value which is greater than the current column value however, you can also set the identity value to any value lower than the current column as well.

In the real world, developers sometimes delete few rows from the table and due to any reasons they want to reseed the identity value to a lower value, this feature can be very much useful. However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity. Anyway here is the SQL in Sixty Seconds Video which explains how we can reseed identity value.

Let us see the same concept in following SQL in Sixty Seconds Video:

Here is the script used in this episode:

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100));
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select data
SELECT *
FROM TestTable
GO
-- Returns current Identity Value
DBCC CHECKIDENT ('TestTable', NORESEED);
-- Resets the current Identity value to maximum value
DBCC CHECKIDENT ('TestTable', RESEED, 11);
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select Table
SELECT *
FROM TestTable
ORDER BY ID;
-- Drop Table
DROP TABLE TestTable;

 

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

SQLAuthority News – Sharding or No Sharding of Database – Working on my Weekend Project

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology, tagged on May 7, 2013 | 4 Comments »

Recently I came across situation where database sharding was once again a suggested solution by architectures. Everytime I hear the word sharding I remember my earlier article about NuoDB on Shard No More – An Innovative Look at Distributed Peer-to-peer SQL Database. Sharding requires developers to think about things like rollbacks, constraints, and referential integrity across tables within their applications when these types of concerns are best handled by the database. It also makes other common operations such as joins, searches, and memory management very difficult.

Each NuoDB database consists of at least three or more processes that enable a single database to run across multiple hosts. These processes include a Broker, a Transaction Engine and a Storage Manager.  Brokers are responsible for connecting client applications to Transaction Engines and maintain a global view of the network to keep track of the multiple Transaction Engines available at any time. Transaction Engines are in-memory processes that client applications connect to for processing SQL transactions. Storage Managers are responsible for persisting data to disk and serving up records to the Transaction Managers if they don’t exist in memory.

Here is my question to you all – do you ever face a situation of sharding of your database is required? When is sharding required not a good solution?

I have downloaded NuoDB today and going to build a small application which I can play with to build a scenario where sharding is no longer required. Would you be interesting in joining me with this exercise? I would like to work along with you to build such a scenario. Download NuoDB and let us start building something interesting.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

« Newer Posts - Older Posts »