beyondrelational.com

Cross-Database Computing at Will Is No Longer Miracle

Wed, 19 Jun 2013 00:00:00 GMT

Java language does not have any competitive advantages in data computing, in particular the massive structural data computing. For example, according to the order detail computation, find out the sales persons who achieve the sales increase over 10% in consecutive 3 months.

Java does not have the related advanced function to implement this. So, only with its native capability, Java can hardly handle such computation, at the cost of a large amount of time and effort to implement the details in computation manually. For example, firstly, define classes and represent every piece of data with objects; secondly, use List to store multi-pieces of data; thirdly, use the nested multi-level loops to compute. Except the sorting algorithm, almost all massive data processing algorithms involved in the computation require implementing manually, such as aggregating, filtering, and grouping. Such computations usually involve the set computation and relation computation between massive data, or computation on relative positions between objects and object attributes. It takes great efforts to implement the underlying logics for these computations.

That’s why we must improve the Java computational capability. We need a tool tailored for implementing the structural data computation easily!

How about SQL? Not all Java application allows for using database. In addition, there are many databases in Txt/Excel, and sometimes, problems of computation across databases and code reuse may be encountered. Moreover, SQL is still not convenient for handling many computation cases.Take the above-mentioned computation for example. SQL is by no means convenient to compose:

01 WITH A AS

02 (SELECT salesMan,month, amount/lag(amount)

03 OVER(PARTITION BY salesMan ORDER BY month)-1 rising_range

04 FROM sales),

05 B AS

06 (SELECT salesMan,

07 CASE WHEN rising_range>=1.1 AND

08 lag(rising_range) OVER(PARTITION BY salesMan

09 ORDER BY month)>=1.1 AND

10 lag(rising_range,2) OVER(PARTITION BY salesMan

11 ORDER BY month)>=1.1

12 THEN 1 ELSE 0 END is_three_consecutive_month

13 FROM A)

14 SELECT DISTINCT salesMan FROM B WHERE is_three_consecutive_month=1

In this case, esProc is the better choice.

esProc is a development tool for database computing, specializing in simplifying the complex computation and is quite convenient to integrate with Java. For esProc, the corresponding scripts are shown below:

esProc allows for the direct retrieval and computation across multiple databases, text files, and Excel sheets. Its grid-like cellset and agile syntax are especially designed for the massive structural data computation. The result can be output via JDBC directly. So, with esProc, the computational capability of Java is dramatically improved.In addition, by nature, esProc supports the computation across databases and the code reuse, with a set of very perfect debugging functions. No wonder that the development productivity in esProc is also superior to that in SQL.

Make capital letter the first character of each word in a sentence

Wed, 19 Jun 2013 00:00:00 GMT

There are many questions on making/replacing the capital letter the first character of each word in a sentence in every forums. Find the below function for such cases without a loop. Hope this would help you...

Create Function dbo.CapitalizeEachword ( @MyString varchar(max))

Returns nvarchar(Max)
As
Begin
	Declare @XML xml

	Set @XML =CAST( '' + REPLACE(@MyString,' ','') + '' As XML)

	Return 
			(
			Stuff (
				(Select ' ' + Col
				From
				(
						Select Stuff(COL, 1,1, UPPER(Left(col,1))) As Col
						From 
							(
								Select X.p.value('Row[1]','nvarchar(200)') As col
								From @XML.nodes('/Root/Rows') As X(p)
							) X
				) Y
				For XML Path('')), 1, 1, '')
			)

End


Select dbo.CapitalizeEachword('This is an example of capitalize the each word in a sentence.')

what are Difference between DATALENGTH and LEN?

Tue, 18 Jun 2013 00:00:00 GMT

1) LEN- it returns the number of characters, not bytes, and it is return same output for regular character or Unicode character string.

it remove trailing spaces

SELECT LEN(N'abc d ') /*OUTPUT:5*/

2) DATALENGTH-returns the length of the input in terms of number of bytes and for Unicode character string, it will count 2 bytes

per character, it does not remove trailing spaces

SELECT DATALENGTH(N'abc d ') /*OUTPUT:12*/

#0273 - SQL Server - IDENTITY columns – Myths – IDENTITY columns cannot be added to existing tables – Part 02

Mon, 17 Jun 2013 00:00:00 GMT

As seen in the first part of this post, we can add IDENTITY columns to existing tables without any issue. IDENTITY columns cannot be NULL. Hence, when we add a new column as an IDENTITY column, every record existing in the table has to be assigned an IDENTITY value. So, the question arises that which record should be the first one to be assigned with the Identity seed, which record will be the second record and so on. In short:

How existing records are assigned the IDENTITY value?

We saw in the first part of the post that because a table was ordered via a clustered primary key, the IDENTITY specification was directly applied in that order. However, when the table is a heap (i.e. does not have a clustered index defined on it and is therefore unordered), the IDENTITY values are assigned based on the order in which records were inserted into the table.

The script and it’s results below prove this point.

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table, this time WITHOUT a Clustered Key
CREATE TABLE dbo.tIdentity (RecordId INT,
                            IdentityValue VARCHAR(20),
                            CONSTRAINT pk_tIdentityRecordId PRIMARY KEY NONCLUSTERED (RecordId)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (3, 'Three'),
       (5, 'Five'),
       (1, 'One'),
       (0, 'Zero'),
       (4, 'Four');
GO

--2b. As a test, inserting some interleaving data separately
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (2, 'Two'),
       (6, 'Six');
GO

--3. Check the values inserted into the table
--   Observe the order of the records that come up in the results
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

--4. Alter the table to add an IDENTITY Column
ALTER TABLE dbo.tIdentity
    ADD IdentityId INT IDENTITY(1,1);
GO    
    
--4. Check the values inserted into the table
--   Observe the order of the records that come up in the results
--   v/s the value of the IdentityId
SELECT RecordId, IdentityId, IdentityValue
FROM dbo.tIdentity;
GO

----4b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

/**********************************************
               RESULTS
**********************************************/
/*
RecordId    IdentityValue
----------- --------------
3           Three
5           Five
1           One
0           Zero
4           Four
2           Two
6           Six

RecordId    IdentityId  IdentityValue
----------- ----------- --------------
3           4           Three
5           6           Five
1           2           One
0           1           Zero
4           5           Four
2           3           Two
6           7           Six
*/

Now, let us define a CLUSTERED INDEX on this table such that the records are re-arranged. Once the clustered index is defined, we will insert a couple of records and then look at the assignment of the IDENTITY column. We can see that IDENTITY values continue to be assigned in the order in which records are inserted, however, the records are now ordered based on the clustered index specification.

USE tempdb;
GO

-- 5. Define a clustered index for the Primary Key
ALTER TABLE dbo.tIdentity
    DROP CONSTRAINT pk_tIdentityRecordId;
GO

ALTER TABLE dbo.tIdentity
    ADD CONSTRAINT pk_tIdentityRecordId PRIMARY KEY CLUSTERED (RecordId);
GO

--6. As a test, inserting some interleaving data separately
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (-1, 'Minus One'),
       (7, 'Seven'),
       (8, 'Eight');
GO

--7. Check the values inserted into the table
--   Observe the order of the records that come up in the results
--   v/s the value of the IdentityId
SELECT RecordId, IdentityId, IdentityValue
FROM dbo.tIdentity;
GO

----7b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO  

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
RecordId    IdentityId  IdentityValue
----------- ----------- --------------
-1          8           Minus One
0           1           Zero
1           2           One
2           3           Two
3           4           Three
4           5           Four
5           6           Five
6           7           Six
7           9           Seven
8           10          Eight
*/

Conclusion (Part 02)

The behavior of IDENTITY columns and of SQL Server seen in this post is quite interesting – IDENTITY values are always generated in the order in which the records are inserted. When adding IDENTITY column to an existing table, this is true only if the table is not ordered, i.e. does not have a clustered index. Once the IDENTITY values have been assigned to all existing records in the table, all future values will be generated in the order of insertion.

Until we meet next time,

Be courteous. Drive responsibly.

Configuration Replication failure and retry alert-SQL Server

Sun, 16 Jun 2013 00:00:00 GMT

As I wrote about replication latency from publisher to distributor and distributor to subscriber for replication and an alert for replication latency, same as we need to have an alert in case of replication failure. I would like to review some of my earlier posts about replication before move to this post which you may like,

Let the discussion to move ahead and check as how can we setup an alert for in case of replication failure. So going with steps,

1. Create a job which having static code to fire an email as following and do not need to schedule it,

DECLARE @Subject varchar(50), @body varchar(200)
SET @Subject = 'Replication Alert (Agent Failure)'
SET @body = 'Replication Alert (Agent Failure).<BR />Check the status of agent from replication monitor.'

EXEC msdb.dbo.sp_send_dbmail 
		@recipients = 'prajapatipareshm@gmail.com',
		@subject = @Subject, 
		@body = @body,
		@profile_name = '<Profile Name>',
		@body_format = 'HTML';

2. Go to Replication monitor from Replication tab under SQL server instance.

3. Select the Publication for which you want to receive an alert for failure and move to Warning tab as following,

(Click on image to enlarge)

4. Go Configure alert and select “Replication : agent failure” as mentioned in below image,

(Click on image to enlarge)

5. For agent property, click on configure button –> Response tab and select the job which we created earlier in step 1 in Execute job check box,

(Click on image to enlarge)

This is the just steps to configure an alert for replication agent failure. We can also add notification alert to job operators also, so whenever replication agent fails, this configuration run the job and it will fire an email. We can setup the same configuration for replication agent retry too.

Relive TechEd 2013 at SQLBangalore User Group Meeting

Sat, 15 Jun 2013 00:00:00 GMT

Impressed by awesome sessions at SQLBangalore user group meeting today (15th of June, 2013), I decided to write a blog post about it. It’s a monthly event that takes place at Microsoft campus in Bangalore. Today’s UG meeting named “Relive TechEd 2013 at SQL Bangalore” had three 1-hour sessions:

- Balmukund Lakhani. SQL Server 2012 - High Availability Redefined.

- Vinod Kumar. What is wrong with Transactional Logs? Common Misconceptions.

- Pinal Dave. Indexes – The Unsung Hero.

I should point out that UG meeting had great success and I personally loved it. There were lots of fun and learning. Though the meeting had early start today (9-45 against 10-30 before), many people came there. I reached Microsoft campus by 9-30 and was impressed by a queue of people waiting to get their visitor badges. By 10 o’clock the room was full or I can even say overfull (some people was listening sessions standing all 3 hours long). UG was started by small introductory speech by Vinod Kumar and giving special gifts from Pluralsight to all members, who brought friends with them to UG.

First session was done by Balmukund Lakhani (Microsoft), blogger, speaker and father of one daughter and husband of one wife, as his “About me” slide said. He started his session with special funny clapping exercises. And later it was very interesting session about AlwaysOn Availability Groups (AG).

We learned that they are flexible, integrated and efficient, and got information about pre-requisites. As I remember, it is not possible to implement them if database has simple or bulk recovery model (so need only full model), or if there is no back ups. Also all nodes should be in same domain and same cluster. Then Balmukund made several demos:

- Creating AG

- Back up on secondary replicas

- Readable secondary replicas

- AG listener

The next session was done by Vinod Kumar, Technology Architect at Microsoft. First he asked audience what transaction log is and got many different answers. He presented “What is wrong with Transactional Logs? Common Misconceptions”, including transaction log physical architecture, virtual log files (VLFs), log cycling, log shrinking. Session had many very interesting demos. Personally, I learned new command for me DBCC LOGINFO to get information about virtual log files inside transaction log.

The last session of the UG meeting was made by Pinal Dave “Indexes – The Unsung Hero”. It was really amazing session. During it Pinal told us imaginary story about Raj, Priya and Rahul, 3 coworkers at Yash Raj Computers. According to story Raj was a big fan of indexes, Priya was neutral and Rahul was against indexes.

So to let us know who is right Raj or Rahul, we passed through several interesting demos. Out of demos audience learned that unused indexes can slow down performance not only of DML queries, but also performance of SELECT; that clustered indexes can give less performance than non-clustered indexes in some cases. And there is no “depends” for all questions he asks.

And at the end of my post I want just to say “Thank you” to all speakers, it was so great that it impressed me to write about it.

PS: SQLBangalore is an open group, so feel free to join us in Facebook and come to our monthly meetings. The next meeting’s date was announced today and it’s 13th of July.

Containers in SSIS

Thu, 13 Jun 2013 00:00:00 GMT

Moving ahead from Import and Export Wizard, we will now discuss on the topic Containers in SSIS. In my previous post on SSIS and It’s Architecture , I discussed about Packages, Control and Data flow, Connection Managers, Containers, etc. Let’s recall some of the concepts and wrap it shortly:-

Package: - A basic unit of Design, Deployment and Execution.
Connection Managers: - A logical representation of connections stored in the package. These cannot be shared between the packages.
Data Flow: - It is defined as the complete process of Extracting data into server’s memory, transforming it and Load the transformed data onto Destination.
Control Flow: - It acts as the brain of a package. A package consists of a single control flow. It contains Containers, Tasks, Variables, Precedence constraints and Event handlers.

So, we are clear with the basics now. For further details, you can jump to my article on SSIS and its Architecture . Now, let’s unite our concentration on the main point- Containers. They are classified as follows:-

Task Host Containers: - This is an abstract concept like an interface. They are not visible in our toolbox and provides services to a single task.
Sequence Container: - It allows you to group tasks into logical subject areas. Within the development environment, you can then collapse or expand this container for usability.
For Loop Container: - The basic function of this is to loop through a series of tasks contained in a container until a condition is met or for predetermined number of times.
For Each Loop Container: - It Loops through a series of files or records in a data set, and then execute the tasks in the container for each record in the collection.

Each container has some common properties that affect the usage of these features. Understanding these properties and what they do helps a lot in the developing SSIS packages.

Property	Description
DelayValidation	A Boolean value that indicates whether validation of the container is delayed until run time
Disable	A Boolean value that indicates whether the container runs
DisableEventHandlers	A Boolean value that indicates whether the event handlers associated with the container run
FailPackageOnFailure	A Boolean value that specifies whether the package fails if an error occurs in the container.
FailParentOnError	A Boolean value that specifies whether the parent container fails if an error occurs in the container.
IsolationLevel	The isolation level of the container transaction. The values are Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.
MaximumErrorCount	The maximum number of errors that can occur before a container stops running.
TransactionOption	The transactional participation of the container. The values are NotSupported, Supported, Required.

We will cover all the above mentioned containers by implementing them separately with reference to simple examples. I am mentioning the links below, Click on the respective link who’s Implementation you want to see :-

Click Here for the ==> Implementation Of SEQUENCE CONTAINER.

Click Here for the ==> Implementation Of FOR LOOP CONTAINER.

Click Here for the ==> Implementation Of FOREACH LOOP CONTAINER.

Export to XML File using SSIS

Thu, 13 Jun 2013 00:00:00 GMT

Recently I was given a task to export data in XML format using SSIS. I initially thought it should be straight forward – just dump it in a flat file and give it a name xml type, however SSIS has no XML destination just an XML source. I had no luck dumping the XML in flat file. My solution was to use a script task which worked well.

My requirement is that the stored procedure would return the XML as a single row and I need to export that in a file.

Please visit the article posted on Sql-Server-Performance for the complete solution. Here, is the complete link

http://www.sql-server-performance.com/2013/export-to-xml-using-ssis/

#0272 - SQL Server - IDENTITY columns – Myths – IDENTITY columns cannot be added to existing tables – Part 01

Thu, 13 Jun 2013 00:00:00 GMT

Generally, IDENTITY columns are used when developing new tables so that when records are inserted into a table, they are assigned the appropriate values.

Hence if ever the need arises to insert an IDENTITY column into an existing question, the big question is – can it be done?

Is it really possible to add an IDENTITY column to an existing table?

The answer is quite simple – Yes!

The only restrictions that ALTER TABLE enforces upon us are that a new column must be added and that it should be of a data-type compatible with IDENTITY Columns. To demonstrate these points, I will perform two experiments shown below.

Modifying an existing column to an IDENTITY column

Other than recreating the entire table, an existing column cannot be modified to an IDENTITY column. Copy-Paste the query below into SSMS and simply parse it by using Ctrl+ F5 key combination or the blue check mark on the SSMS toolbar. The following error will be encountered:

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (RecordId INT,
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (3, 'Three'),
       (2, 'Two'),
       (1, 'One'),
       (0, 'Zero'),
       (4, 'Four'),
       (5, 'Five'),
       (6, 'Six');
GO

--3. Check the values inserted into the table
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

--4. Alter the table to add an IDENTITY Column
ALTER TABLE dbo.tIdentity
    ALTER COLUMN RecordId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED;
GO    
    
--4. Check the values inserted into the table
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

----4b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'IDENTITY'.
*/

Tip: If your design ever needs to be modified to convert an existing column into an IDENTITY column, this can be achieved using the following high-level steps:

Create an identical table under a different name, but with an IDENTITY column (e.g. tmp_tIdentity)
Set IDENTITY_INSERT to ON for this new table
Pump data from the old table to the new table (i.e. from tIdentity to tmp_tIdentity)
Set IDENTITY_INSERT to OFF for this new table
Drop the old table
Rename the newly created table (i.e. rename from tmp_tIdentity to tIdentity)

Adding a new Identity column to an existing table

While existing columns cannot be converted to IDENTITY columns, new IDENTITY column can always be added to an existing table (provided there are no other IDENTITY columns on it).

The below script quite simply creates a table and then add an IDENTITY column to it. As can be seen from the results below, although the records were inserted randomly, the identity values are assigned based on the way in which the records are ordered. This is an important consideration, as we will see in part 02 of this post. In this case, we have a clustered key on the RecordId column and therefore, the values in the IDENTITY column follow the same order as the RecordId.

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (RecordId INT PRIMARY KEY CLUSTERED,
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (3, 'Three'),
       (5, 'Five'),
       (1, 'One'),
       (0, 'Zero'),
       (4, 'Four'),
       (2, 'Two'),
       (6, 'Six');
GO

--3. Check the values inserted into the table
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

--4. Alter the table to add an IDENTITY Column
ALTER TABLE dbo.tIdentity
    ADD IdentityId INT IDENTITY(1,1);
GO    
    
--4. Check the values inserted into the table
SELECT RecordId, IdentityId, IdentityValue
FROM dbo.tIdentity;
GO

----4b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
--Before adding the IDENTITY value
RecordId    IdentityValue
----------- --------------
0           Zero
1           One
2           Two
3           Three
4           Four
5           Five
6           Six

--After adding the IDENTITY value
RecordId    IdentityId  IdentityValue
----------- ----------- --------------
0           1           Zero
1           2           One
2           3           Two
3           4           Three
4           5           Four
5           6           Five
6           7           Six
*/

Conclusion (Part 01)

The above experiments show that Microsoft SQL Server allows us to define IDENTITY columns on existing tables, under certain restrictions. This can be a very useful feature in case an original, legacy design needs to be changed for a universal adoption of IDENTITY columns in your product.

Until we meet next time,

Be courteous. Drive responsibly.

Question of the month June 2013 - When did you use CURSOR for performance benefit?

Wed, 12 Jun 2013 00:00:00 GMT

As we know, in SQL Server we can almost use SET-BASED approach to implement any kind of business logic. Rarely CURSOR is used in some cases.

Now the question is "When did you use CURSOR to get better performance than SET-BASED approach?"

T SQL Script - Splitting a delimited column value to multiple columns

Mon, 10 Jun 2013 00:00:00 GMT

There is a large number of requests in multiple forums to split a delimitted column value to multiple columns. Here is one method that I worked on, probably, this would help most of us in future if we come across similar situation:


Create Table Product(Col1 varchar(10),Col2 Varchar(1000))

iNSERT INTO pRODUCT(Col1,Col2) sELECT 1,'AR-5381 BA-8327 BB-7421 BB-8107 BB-9108'
iNSERT INTO pRODUCT(Col1,Col2) sELECT 2,'BC-M005 BC-R205 BE-2349 BE-2908 BK-M18B-40'
 
    
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT Col1, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5
FROM
(SELECT Col1,
        ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY nums.n) AS PositionInList,
        LTRIM(RTRIM(SUBSTRING(valueTable.Col2, nums.n, charindex(N' ', valueTable.Col2 + N' ', nums.n) - nums.n))) AS [Value]
 FROM   Numbers AS nums INNER JOIN Product AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col2)) AND SUBSTRING(N' ' + valueTable.Col2, n, 1) = N' ') AS SourceTable
PIVOT
(
MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5])
) AS Table2

Drop table Product

POWER function may not return accurate values

Mon, 10 Jun 2013 00:00:00 GMT

POWER is one of the mathematical functions used in SQL Server which returns the value of the expression to a specific power. But it rounds the values after 16 digits if the result is more than 16 digits. Consider the following example

declare @value decimal(38), @power_to int, @total decimal(38)
select @value=5, @power_to =29, @total=1
select power(@value,@power_to) as value

The result is

value
---------------------------------------
186264514923095690000

We know that any power(>0) of 5 ends with 5 only and the result ends with 0. As you see the result is rounded from 16th digit onwards. So how do we overcome this problem? As usual a numbers or tally table comes handy. Use the following code

declare @value decimal(38), @power_to int, @total decimal(38)
select @value=5, @power_to =29, @total=1
select @total=@total*@value from master..spt_values where type='p' and number < @power_to
select @total as value

The result is

value
---------------------------------------
186264514923095703125

So beware of this behavior of power function.

#0271 - SQL Server - IDENTITY columns – Myths – IDENTITY values (including SEED & INCREMENT) cannot be negative

Mon, 10 Jun 2013 00:00:00 GMT

When one thinks of an incremental number, a series of positive numbers comes to mind. Since IDENTITY columns are an auto-incrementing series of numbers, it is human to associate IDENTITY columns with positive numbers. However when we talk about numbers and the number line, we can go to infinity from both sides of 0 (-Infinity….-5, -4, -3, -2, -1, 0, +1, +2, +3, +4, +5,….+Infinity).

Hence, the question that comes up is whether IDENTITY values (including the seed & increment values) can be negative. The only way to test it out is to use an example:

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY (-1, -1),
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Minus One'),
       ('Minus Two'),
       ('Minus Three'),
       ('Minus Four'),
       ('Minus Five');
GO

--3. Check the values inserted into the table
SELECT IdentityId, IdentityValue
FROM dbo.tIdentity;

----3b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValue;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityId  IdentityValue
----------- --------------
-1          Minus One
-2          Minus Two
-3          Minus Three
-4          Minus Four
-5          Minus Five
*/

As can be seen from the result of the script above, it is possible for the SEED, INCREMENT and the IDENTITY column value itself to be negative. Keeping in mind the rules of the number line (from elementary school), please note that as one moves away from 0 (in either direction), the number keeps increasing in value and is therefore incrementing.

Conclusion

As shown by the quick experiment above, it is quite possible to have negative values for IDENTITY columns. As of writing this post, I do not have a practical implementation of this in mind. However, if you have ever encountered a situation where auto-incrementing negative values might be useful, do drop a line in the blog comments below.

Until we meet next time,

Be courteous. Drive responsibly.

Script to get replication latency and alert in SQL Server

Sun, 09 Jun 2013 00:00:00 GMT

Recently I had posted for one issue I faced for replication trace token ,Could not find the Distributor or the distribution database for the local server. Hope you read and liked it. Today I am writing for replication latency and it’s alert and I am using one script to post a tracer token which I used same in earlier post where you can see the script there and details for same. So move on the topic here as how can we get replication latency information and alert for same. Putting in steps,

1. Post tracer tokens : Schedule a script to post a tracer tokens frequently, says 5 minutes which posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics and run this script at publisher,

USE publisherdb 
GO

DECLARE @out_tracer_token_id INT 

EXEC sys.Sp_posttracertoken 
  @publication = N'<Publication Name>',  -- Put Publication name here
  @tracer_token_id=@out_tracer_token_id out 

SELECT @out_tracer_token_id

2. Script : After scheduling above scripts every 5 minutes which will put latency history in MStracer_history system table in distribution database, following script use to get details for the latency from publisher to distributor and distributor to subscriber which belongs to distribution database,

USE distribution 
GO 

SELECT publisher_db                                                    AS 
       [PublisherDB] 
       , 
       publication 
       AS [Publication], 
       name                                                            AS 
       [Subscriber], 
       subscriber_db                                                   AS 
       [SubscriberDB], 
       RIGHT('0' + Cast([timetosubscriber]/3600 AS VARCHAR(3)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([timetosubscriber] % 3600) / 60 AS VARCHAR(2)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([timetosubscriber] % 60) AS VARCHAR(2)), 2) AS 
       [DistToSubLatency], 
       RIGHT('0' + Cast([timetopublisher]/3600 AS VARCHAR(3)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([timetopublisher] % 3600) / 60 AS VARCHAR(2)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([timetopublisher] % 60) AS VARCHAR(2)), 2)  AS 
       [PubToDistLatency], 
       RIGHT('0' + Cast([totaltime]/3600 AS VARCHAR(3)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([totaltime] % 3600) / 60 AS VARCHAR(2)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([totaltime] % 60) AS VARCHAR(2)), 2)        AS 
       [TotalLatency] 
FROM   (SELECT DISTINCT msda.publisher_db, 
                        syss.name, 
                        msda.subscriber_db, 
                        msda.publication, 
                        publisher_commit, 
                        distributor_commit, 
                        Datediff(ss, publisher_commit, distributor_commit)    AS 
                               [TimeToPublisher], 
                        subscriber_commit, 
                        Datediff(ss, distributor_commit, subscriber_commit)   AS 
                               [TimeToSubscriber], 
                        Datediff(ss, publisher_commit, distributor_commit) 
                        + Datediff(ss, distributor_commit, subscriber_commit) AS 
                               [TotalTime] 
        FROM   mstracer_history msth 
               INNER JOIN msdistribution_agents msda 
                       ON msth.agent_id = msda.id 
               INNER JOIN sys.servers syss 
                       ON msda.subscriber_id = syss.server_id 
               INNER JOIN mstracer_tokens 
                       ON msth.parent_tracer_id = mstracer_tokens.tracer_id 
        WHERE  subscriber_commit > Dateadd(mi, -5, Getdate())) res 
ORDER  BY [totaltime] DESC

3. Alert : As we seen a script to get the latency for replication , herewith I am sharing a script to get alert for highest latency, In script logic is implies to send an alert only when publisher to distributor or distributor to subscriber latency exceed 1 minute. You can schedule this script to every 5 minutes as we use condition to scan latency history inserted in last 5 minutes,

USE distribution 
GO 

DECLARE @subject SYSNAME 
DECLARE @body VARCHAR(4000) 
DECLARE @SubscriberTime VARCHAR(20) 
DECLARE @PublisherTime VARCHAR(20) 
DECLARE @TotalTime VARCHAR(20) 
DECLARE @PublisherDB VARCHAR(20) 
DECLARE @Publication VARCHAR(20) 
DECLARE @Subscriber VARCHAR(20) 
DECLARE @SubscriberDB VARCHAR(20) 

SELECT @PublisherDB = publisher_db, 
       @Publication = publication, 
       @Subscriber = name, 
       @SubscriberDB = subscriber_db, 
       @SubscriberTime = RIGHT('0' + Cast([timetosubscriber]/3600 AS VARCHAR(3)) 
                         , 2) 
                         + ':' 
                         + RIGHT('0' + Cast(([timetosubscriber] % 3600) / 60 AS 
                         VARCHAR 
                         (2)), 2) 
                         + ':' 
                         + RIGHT('0' + Cast(([timetosubscriber] % 60) AS VARCHAR 
                         (2)), 2 
                         ), 
       @PublisherTime = RIGHT('0' + Cast([timetopublisher]/3600 AS VARCHAR(3)), 
                        2) 
                        + ':' 
                        + RIGHT('0' + Cast(([timetopublisher] % 3600) / 60 AS 
                        VARCHAR(2 
                        )), 2) 
                        + ':' 
                        + RIGHT('0' + Cast(([timetopublisher] % 60) AS VARCHAR(2 
                        )), 2), 
       @TotalTime = RIGHT('0' + Cast([totaltime]/3600 AS VARCHAR(3)), 2) 
                    + ':' 
                    + RIGHT('0' + Cast(([totaltime] % 3600) / 60 AS VARCHAR(2)), 
                    2) 
                    + ':' 
                    + RIGHT('0' + Cast(([totaltime] % 60) AS VARCHAR(2)), 2) 
FROM   (SELECT DISTINCT msda.publisher_db, 
                        syss.name, 
                        msda.subscriber_db, 
                        msda.publication, 
                        publisher_commit, 
                        distributor_commit, 
                        Datediff(ss, publisher_commit, distributor_commit)    AS 
                               [TimeToPublisher], 
                        subscriber_commit, 
                        Datediff(ss, distributor_commit, subscriber_commit)   AS 
                               [TimeToSubscriber], 
                        Datediff(ss, publisher_commit, distributor_commit) 
                        + Datediff(ss, distributor_commit, subscriber_commit) AS 
                               [TotalTime] 
        FROM   mstracer_history msth 
               INNER JOIN msdistribution_agents msda 
                       ON msth.agent_id = msda.id 
               INNER JOIN sys.servers syss 
                       ON msda.subscriber_id = syss.server_id 
               INNER JOIN mstracer_tokens 
                       ON msth.parent_tracer_id = mstracer_tokens.tracer_id 
        WHERE  subscriber_commit > Dateadd(mi, -5, Getdate())) res 
WHERE  ( [timetopublisher] > 60 
          OR [timetosubscriber] > 60 ) 
-- Fetch if publisher to distributor or distributor to subscriber latency greater than 60 seconds
ORDER  BY [totaltime] DESC 

SET @subject='Replication Latency Alert' 
SET @body= 
'Replication latency exceeded the highest acceptable replication delay' 
SET @body=@body 
          + 'Publisher to Distributor: ' + @PublisherTime 
          + 'Distributor to Subscriber: '+ @SubscriberTime 
          + 'Total Delay: '+ @TotalTime 
          + 'Publication: '+ @Publication 
          + 'Subscriber: ' + @Subscriber 

IF ( @body IS NOT NULL ) 
  BEGIN 
      EXEC msdb.dbo.Sp_send_dbmail 
        @recipients = 'prajapatipareshm@gmail.com', 
        @subject = @subject, 
        @body = @body, 
        @profile_name = '<Profile name>', 
        @body_format = 'HTML'; 
  END

This is the details I wanted to present here to catch up replication latency and hope you may like it.

Inter-row Computation of Summary Value-A Tough Issue in Spreadsheet

Fri, 07 Jun 2013 00:00:00 GMT

Spreadsheet is popular with business users for its simplicity and usability. But it is a pity that some common computations are still tough to solve with spreadsheets. The inter-row computation of summary value is such tough problem.

According to the data of order below, how to calculate the rate of sales increase in each month?

Obviously, the rate of increase in February should be (D58-D2)/D2, which is a typical inter-row computation. The tough problem for traditional business spreadsheet software is that the traditional business spreadsheet software only allows for manual formulas entering. Copying or dragging formulas to other cells will only lead to the wrong result. For example, copy a formula to the cell of March, as shown in below figure:

75-fold increase? Obviously wrong! The correct formula should be (D113-D58)/D58, while the resulting formula by copying is (D113-D57)/D57. The reason for this phenomenon is that the traditional business spreadsheet software only allows for the rigid formula-pasting based on the relative positions, lacking the intelligent adjustment mechanism.

Obviously, if the data volume is huge, entering all formulas manually will be such a pain and error-prone.

Then, let’s talk about esCalc. As brand new business spreadsheet software reputed for great computing capability, esCalc is highly expected on this problem.

The same data is shown below:

In esCalc, you only need to input the formula once to solve this problem! For example, enter (D58-D2)/D2 for February, and the result is shown below:

No doubt you’ve seen that all computations are finished by entering the formula for once. No need to copy or adjust the formula. Take the formula for March for example, it is (D113-D58)/D58, just the same as I expected.

esCalc boasts an unique homocell model which arranges cells not in a simple relative positions, but in an auto-established business association. The immediate benefit is that the formulas will be copied automatically, that is, the formula will be copied and pasted to the cells at the same business level automatically. In the above-mentioned case, for the March, April, and February bands, the respective cell in the respective summery row can be regarded as the homocells to each other. Therefore, the formula written in the cell of February will be copied and pasted to the corresponding cells of March and April.

Needless to say, such copying is not the migration of the relative positions in the traditional business spreadsheet software. This is a kind of Intelligent Migration, for example, migrate the formula for February to the homocell for March, as mentioned above.

Through the auto-pasting and intelligent migration of formulas, esCalc can relieve the great amount of manual work. Because it is implemented automatically, the possibilities of errors are also reduced greatly.

Seeing is believing. The computational capability of esCalc, as legend has it, is truly powerful. Let Excel trembles at esCalc!

Convert XML data to sql table data

Thu, 06 Jun 2013 00:00:00 GMT

Few days back, I came across a situation in which I need to import my table data from SSIS into XML file. I did that part, now I have to do enough testing that whether the XML generated was correct or not. The XML contains thousands of nodes and checking each node one by one was a pain, and even the structure of XML was custom and having hundred’s of columns, so cannot easily convert into table format.

I searched about it and created one generic solution which will convert the XML into a table in sql server and then it will be easy to join the table with source and do testing.

Let us run an example to demonstrate the solution.

I have created a TestData table, in which all the XML file data will be saved, it will behave as an audit table, where the history is logged.

CREATE TABLE TestData (RowID int, XMLdata XML)

I am inserting a test xml row created from sysobjects for testing.

INSERT INTO dbo.TestData(RowID,XMLdata)
SELECT 1,
			(SELECT name as "name" ,
					object_id as "objectid",
					schema_id as "schemaid",
					type  as "objecttype"
			FROM sys.objects
			FOR XML PATH ('Objects'), ROOT('AllObjects'))

Now, once the data is inserted let us start coverting it row by row into a table

I have used Xquery local-name function to fetch the node name value, since there are 100’s of nodes inside the xml, it’s a generic method. Now, each node should have a unique identifier to identify the record. Here, I am having Objectid as a Unique identifier node, which can be used to identify a row. Since this will be totally unpivoted data, we need one identification column, and objectid will be the same.

   

SELECT  DISTINCT
		a.RowID,
		f.x.value('(objectid)[1]', 'varchar(max)')  AS ObjectID,
		t.c.value('local-name(..)', 'varchar(max)') AS ParentNodeName,
		t.c.value('local-name(.)', 'varchar(max)') AS NodeName,        
		t.c.value('(.)[1]', 'varchar(max)') AS NodeText
		INTO #TempNodeValues
FROM   TestData a
CROSS APPLY a.XMLdata.nodes('//AllObjects/Objects') AS   f(x)
CROSS APPLY f.x.nodes('./*') AS   t(c)

Insert the data of the above query in some Temp table. I am inserting in #TempNodeValues temp table.

Next step is to PIVOT the data so that all the columns comes up in one single row for one objectid. So, do a distinct nodename from the temp table

  
SELECT DISTINCT NodeName from #TempNodeValues

Output is four values which we have used while creating the XML

NodeName

------------
objectid
name
objecttype
schemaid

Now all the major work is completed. We just need to pivot the data. In the query below paste the distinct nodenames in the Pivot section. Since we have taken objectid as a separate column in the hash table we will ignore it now.

SELECT rowid, objectid,name,objecttype,schemaid
FROM (SELECT *  FROM #TempNodeValues )T
PIVOT
( 
	MIN(T.NODETEXT) FOR NODENAME IN (name,objecttype,schemaid)
)PVT

Run the query, and all the XML is now converted to table. Here, only two place we need to do a manual work, otherwise it is generic to every problem.

Let me know your views on the post and If anyone has implemented the solution in some other way, please share with me.

#0270 - SQL Server - IDENTITY columns – Myths – The value for SEED and INCREMENT must be 1

Thu, 06 Jun 2013 00:00:00 GMT

When I interview students and ask them about IDENTITY values, they are often under the impression that they have a fixed starting value (i.e. SEED) and increment (i.e. IDENTITY values go from 1, 2, 3, 4, 5… and so on).

For those who read my Introduction to this series on IDENTITY values, I had mentioned that it is a common implementation to have the IDENTITY value increment by 1, but it is not the only implementation possible. Let’s look at this by means of some examples:

SEED & INCREMENT values – DEFAULT behavior

It might come as a surprise to many that the customary definition of IDENTITY (1,1) is used for consistency purposes only. Simply defining a column as IDENTITY would cause the database engine to assume a default value of 1 for the seed and identity.

The script below shows this by not specifying anything for the seed & increment values when defining the IDENTITY column.

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
--NOTE! No values have been explicitly defined for the SEED & INCREMENT
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY,
                            IdentityValue VARCHAR(10)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('One'),
       ('Two'),
       ('Three');
GO

--3. Check the values inserted into the table
SELECT IdentityId, IdentityValue
FROM dbo.tIdentity;

----3b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityId  IdentityValue
----------- -------------
1           One
2           Two
3           Three
*/

As can be seen from the results shown above, the database engine used default values of 1 for both seed & increment.

SEED & INCREMENT values – Custom values

Now, let us attempt a similar experiment but with a seed value of 10 and an increment value of 5.

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY (10, 5),
                            IdentityValue VARCHAR(10)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Ten'),
       ('Fifteen'),
       ('Twenty');
GO

--3. Check the values inserted into the table
SELECT IdentityId, IdentityValue
FROM dbo.tIdentity;

----3b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityId  IdentityValue
----------- -------------
10          Ten
15          Fifteen
20          Twenty
*/

As can be seen from the results shown above, the identity columns worked perfectly fine with seed and increment values other than 1.

The only limitation that this approach might end up with is the limited range of allowed values. For example, if the seed is kept at 1000, the column can accommodate an equivalent number of lesser records before the max. value for the given data-type is reached.

Conclusion

As shown by the experiments above, it is not necessary for any application to use the default values of IDENTITY columns – they can be set to values acceptable by the business and domain rules applicable to the application.

Until we meet next time,

Be courteous. Drive responsibly.

How to Migrate the SSIS Packages from 2005 to 2008

Wed, 05 Jun 2013 00:00:00 GMT

Hi,

1)I have an requirement where I need to migrate the SSIS Packages from 2005 to 2008, and Please list out the possible issues while migrating the SSIS Packages from 2005 to 2008

2)I also need to migrate the SQL Server 2005 Database Server to SQL Server 2008 Database Server.

If Possible Please help me out with the screen shots.

Thank You.

#0269 - SQL Server - IDENTITY columns – Myths – Duplicate Values cannot exist IDENTITY columns

Tue, 04 Jun 2013 00:00:00 GMT

The SQL Server database engine automatically generates the next value for use by the IDENTITY column on a table. This often leads to a misconception that IDENTITY columns always contain unique values.

The truth is that they don’t have to be unique. It is perfectly valid to have duplicate values in IDENTITY columns and today, I present two such scenarios under which this is possible:

Duplicate values in IDENTITY columns – Explicit Inserts

As already know, explicit values can be inserted into IDENTITY columns. It is therefore possible to insert duplicate values in this scenario:

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO

--Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('One'),
       ('Two'),
       ('Three');
GO

--2. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueBeforeTest;
GO

--3. Turn Explicit IDENTITY_INSERT ON and insert duplicate IDENTITY values
SET IDENTITY_INSERT dbo.tIdentity ON;
GO

INSERT INTO dbo.tIdentity (IdentityId, IdentityValue)
VALUES (2, 'Two2'),
       (3, 'Three2');
GO

SET IDENTITY_INSERT dbo.tIdentity OFF;
GO

--4. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterExplicitInsert;
GO

SELECT * FROM dbo.tIdentity;
GO

--5. Run DBCC CHECKIDENT and see what happens
DBCC CHECKIDENT ('dbo.tIdentity');
GO
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
GO

--Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityValueBeforeTest
-----------------------
3

IdentityValueAfterExplicitInsert
--------------------------------
3

IdentityId  IdentityValue
----------- -------------
1           One
2           Two
3           Three
2           Two2
3           Three2

Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

IdentityValueAfterDBCC
----------------------
3
*/

As can be seen from the script above, we were successfully able to insert duplicate values for the IdentityId column in the table. Also, the current IDENTITY value continues to remain 3 although there are about 5 records inserted in the table.

Duplicate values in IDENTITY columns – Re-seed IDENTITY column

The DBCC CHECKIDENT can be used to get the current value of the IDENTITY column value. By default, this DBCC statement does not reseed the IDENTITY column. However, if the RESEED parameter is specified, it allows the administrator to reset the “seed” value of the column to any value supplied (Refer the Introductory Post for a refresher).

The query shown below:

Creates a test table and inserts some test data into it
Verifies the value of the IDENTITY column
Reseed the IDENTITY column using DBCC CHECKIDENT with the RESEED parameter
Insert some test data into the test table
Verify the values in the table and the current value of the IDENTITY column

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO

--Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('One'),
       ('Two'),
       ('Three');
GO

--2. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueBeforeTest;
GO

--3. Reseed the IDENTITY column
DBCC CHECKIDENT ('dbo.tIdentity', RESEED, 1);
GO

INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Two2'),
       ('Three2');
GO

--4. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValuesAfterReseed;
GO

SELECT * FROM dbo.tIdentity;
GO

--5. Run DBCC CHECKIDENT and see what happens
DBCC CHECKIDENT ('dbo.tIdentity');
GO
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
GO

--Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityValueBeforeTest
-----------------------
3

Checking identity information: current identity value '3', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

IdentityValuesAfterReseed
-------------------------
3

IdentityId  IdentityValue
----------- -------------
1           One
2           Two
3           Three
2           Two2
3           Three2

Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

IdentityValueAfterDBCC
----------------------
3
*/

Duplicate values in IDENTITY columns – BCP/BULK INSERT/OPENROWSET - -E, KEEPIDENTITY, -f switches

A long time ago, I wrote about the BCP utility being an underappreciated feature of Microsoft SQL Server.

When data is exported or inserted in bulk via the bcp and BULK INSERT statements, it is possible to preserve the value of IDENTITY columns using the following switches:

BCP uses the –E switch
BULK INSERT uses the KEEPIDENTITY switch
OPENROWSET uses the –f switch

Conclusion

As is the case with all columns in SQL Server, uniqueness can only be guaranteed by the use of a UNIQUE constraint. Without the constraint, IDENTITY columns may end up having duplicate values.

Often, applications and T-SQL code use IDENTITY columns as a unique sequence of numbers – this should be done with due care as duplicate values may cause the code to break.

Until we meet next time,

Be courteous. Drive responsibly.

REPLACE string function in SQL Server and C#

Tue, 04 Jun 2013 00:00:00 GMT

Recently I’ve faced with one error in our .NET application code, which I did not expect. But it helped me to learn something new for me.

All we know about string function REPLACE. We have it in SQL Server and in .NET (as a method of String class).

Let’s make a small research and compare results of REPLACE functions in SQL Server and C#. Let us have string variable to save in it a substring to be changed in some other original string. So our small research will concern one question: what will we get if this substring (to be replaced) is empty string?

First let’s check in SQL Server:

DECLARE @toReplace varchar(50) = ''
SELECT REPLACE('Hello', @toReplace, '') AS Result

When we execute this snippet of code, we will get:

Result

---------------------------------------------

Hello

Let’s implement the same logic in C# and check result:

using System;
namespace TestStringReplace
{
  class Program
  {
    static void Main()
    {
       string toReplace = string.Empty;
       Console.WriteLine("Hello".Replace(toReplace, string.Empty));
    }
  }
}

And guess what will we get? The answer is ArgumentException:

Unhandled Exception: System.ArgumentException: String cannot be of zero length.

Parameter name: oldValue

So now we know that in .NET first argument of REPLACE string function cannot be empty string.

Hope, it would be useful for somebody.

just another solution

Tue, 04 Jun 2013 00:00:00 GMT

Microsoft announced new SQL Server 2014

Tue, 04 Jun 2013 00:00:00 GMT

Software world is changing very fast these days. It’s been only 14 months since Microsoft released SQL Server 2012. And yesterday (June 3, 2013) at TechEd North America Microsoft announced a wave of new products and services that will help customers embrace the “enterprise cloud era”, including next version of data platform – SQL Server 2014.

Microsoft states that SQL Server 2014 is designed and developed with cloud-first principles in mind, it delivers built-in in-memory capabilities, new hybrid cloud scenarios and enables even faster data insights. What new features are waiting for us in SQL Server 2014, including:

- In memory OLTP (codename Hekaton)

- Improved AlwaysOn features (including AlwaysOn to Windows Azure Virtual machine)

- Hybrid cloud

- Hadoop connectivity

Microsoft officials said that SQL Server 2014 would be out shortly after release of Windows Server 2012 R2, System Center 2012 R2 and Visual Studio 2013 (planned tobe out by the end of calendar 2013).

I’m personally exciting to explore new features of announced product. So you are, I think.

Database mail sending fails with SP_Send_DBmail.

Tue, 04 Jun 2013 00:00:00 GMT

Hi,

I am stuck in below problem.

I have two servers; ServerA and ServerB. I have linked serverA to serverB by

sp_addlinkedserver @server = 'serverB',@provider = 'SQLNCLI',@datasrc = 'ServerB',

@srvproduct = '' on serverA. Now I am querying on ServerA and retriveing data from ServerB's Table.

The Query is :

EXEC msdb.dbo.sp_send_dbmail @recipients = 'myname@myDomain.com',

@subject = 'MySubject',

@body = 'This Is Test.',

@body_format = 'Text', @profile_name = 'Profile1',

@query = 'set nocount on

SELECT * FROM [ServerB].[Database].dbo.myTable AS MT

WHERE DATEADD(DAY,0,DATEDIFF(DAY,0,Date_Col)) = DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))',

@execute_query_database = 'master',

@query_result_separator = ' ',

@attach_query_result_as_file = 1,

@query_attachment_filename = 'myFile.csv'

I have created an proc on serverA's master database and calling this proc into a job.

This job throws an error:

" Executed as user: ServerA\sqlAgent. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000] (Error 18452). The step failed. "

Can Anybody help in solving this??

It would be a great help.

Thanks and Regards,

Table with only one column

Tue, 04 Jun 2013 00:00:00 GMT

One of my colleagues asked me "Is there a real practical use of having a table with only one column?" Well. There are some cases where a table with only one column makes sense. Here are some cases I know

1 The numbers or Tally table

This table can have one column with serial numbers and there can be many uses of this table Let us consider this table

Create table #numbers(number int)
insert into #numbers(number)
select top 100000 row_number() over (order by (select 0)) from sys.columns as c1 
cross join sys.columns as c2
select * from #numbers

The above will create a table of numbers from 1 to 100000 Now this can be used to generate a series dates for a year, split a string, extract some part of data from a string, etc.

Extract only numbers from a String

Count number of words in a string

Jeff Moden, SQL Server MVP, has written an excellant aricle here http://www.sqlservercentral.com/articles/T-SQL/62867/

2 To have Maximum value of a sequence number

If your business requirement needs you to generate a sequence number without gaps for each record before data are added to a table, you can update the maximum value to this table and pick it up whenever needed Suppose you have another table where you want to add sequence number to that table along with the data, you can use

Create table #sequence(number int)

insert into #sequence(number)
select 1

Create table #customers(cust_id varchar(15), customer_name varchar(100), dob datetime)

declare @cust_id varchar(15)
select @cust_id ='CUST'+replace(str(number,10),' ','0') from #sequence 
 insert into #customers(cust_id,customer_name,dob)
select @cust_id, 'Vimal','19650119'

update #sequence 
set number=number+1

select * from #customers 
select * from #sequence

Note that the #sequence table has next value that can be used to #customers table

Have you used one column table for any other purposes?

Art of Filestream - Chapter 2

Mon, 03 Jun 2013 00:00:00 GMT

I have reached only as far as Chapter 2, but here are my observations so far: 1) The download page http://brurl.com/fs4 is empty. The scripts and labs are available elsewhere, but not philFactor.gif, MicrosoftMouse.jpg or the other images mentioned in the book. 2) On page 50, you write: > a FILESTREAM-enabled database needs to have at least three filegroups: > • a data filegroup to store relational data > • a log filegroup to store transactional logs > • a FILESTREAM filegroup to store FILESTREAM data. However, a log file cannot be put in a file group. See [Add Data or Log Files to a Database][1] where it says in step 7: Transaction logs cannot be put in filegroups. 3) Also on page 50, you go on to say that: > you might decide to have more than one FILESTREAM filegroup in a database ... one of those filegroups must be marked as the default filegroup”. Listing 2-26 on page 84 shows T-SQL code for creating a database with 2 FILESTREAM filegroups. My observation is that I can create a database with 2 FILESTREAM filegroups without specifying a default. That is, no error is generated. However, SQL Server will make the first filegroup the default if none is explicitly specified. 4) Listing 2-13: Altering a VARBINARY(MAX)column to a FILESTREAM column. The UPDATE statement fails because you cannot add a column and then reference it in the same batch. The listing should be: ALTER TABLE ... ADD ItemImage_New ... GO – need to start a new batch after ALTER TABLE UPDATE ... SET ItemImage_New = ... 5) On page 71, you say that “we could ... use a default value, of 0x. This will create a zero-length file ...” Actually, the file size will be 1 byte. 6) You do not define a procedure for submitting feedback, so I was forced to (ab?)use the comments section for that purpose. What is the right way to send feedback about the book? [1]: http://msdn.microsoft.com/en-us/library/ms189253.aspx "Books Online"