Recently, I came across a piece of TSQL code that would take a comma separated string as an input and parse it to return a single column table from it.
Lets’ call this function as Split1. The code is as follows:
CREATE FUNCTION [dbo].Split1(@input AS Varchar(4000) )
RETURNS
@Result TABLE(Value BIGINT)
AS
BEGIN
DECLARE @str VARCHAR(20)
DECLARE @ind Int
IF(@input is not null)
BEGIN
SET @ind = CharIndex(',',@input)
WHILE @ind > 0
BEGIN
SET @str = SUBSTRING(@input,1,@ind-1)
SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)
INSERT INTO @Result values (@str)
SET @ind = CharIndex(',',@input)
END
SET @str = @input
INSERT INTO @Result values (@str)
END
RETURN
END
This is a very old fashioned (but still effective enough) script which does a loop over a string to cut out all possible string values that are separated by a comma.
Let’s see now, how the same could be achieved in modern day TSQL languages (such as SQL 2005 or SQL 2008).
Approach 1: Common Table Expression (CTE)
Lets call this function as Split2. here we are using
CREATE FUNCTION dbo.Split2 ( @strString varchar(4000))
RETURNS @Result TABLE(Value BIGINT)
AS
begin
WITH StrCTE(start, stop) AS
(
SELECT 1, CHARINDEX(',' , @strString )
UNION ALL
SELECT stop + 1, CHARINDEX(',' ,@strString , stop + 1)
FROM StrCTE
WHERE stop > 0
)
insert into @Result
SELECT SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
FROM StrCTE
return
end
GO
Approach 2: XML (surprise)
XML could be applied to do some type of string parsing (see this) Let’s call this function as Split3.
CREATE FUNCTION dbo.Split3 ( @strString varchar(4000))
RETURNS @Result TABLE(Value BIGINT)
AS
BEGIN
DECLARE @x XML
SELECT @x = CAST(''+ REPLACE(@strString,',','')+ '' AS XML)
INSERT INTO @Result
SELECT t.value('.', 'int') AS inVal
FROM @x.nodes('/A') AS x(t)
RETURN
END
GO
Approach 4: Classic TSQL Way
I got this approach from SQL Server Central site. This approach is slightly unusual but very effective. this needs you to create a table of sequential numbers called a Tally Table.
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
Lets index the table for better performance.
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
WITH FILLFACTOR = 100
Finally out Split4 function.
CREATE FUNCTION dbo.Split4 ( @strString varchar(4000))
RETURNS @Result TABLE(Value BIGINT)
AS
BEGIN
SET @strString = ','+@strString +','
INSERT INTO @t (Value)
SELECT SUBSTRING(@strString,N+1,CHARINDEX(',',@strString,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@strString)
AND SUBSTRING(@strString,N,1) = ',' --Notice how we find the comma
RETURN
END
GO
BEST OF ALL
DECLARE @str VARCHAR(4000)
= '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832,833,836,837,846,913,914,919,922,923,924,925,926,927,927,928,929,929,930,931,932,934,935,1029,1072,1187,1188,1192,1196,1197,1199,1199,1199,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1366,1367,1387,1388,1666,1759,1870,2042,2045,2163,2261,2374,2445,2550,2676,2879,2880,2881,2892,2893,2894'
Declare @x XML
select @x = cast(''+ replace(@str,',','')+ '' as xml)
select t.value('.', 'int') as inVal
from @x.nodes('/A') as x(t)
Tuesday, November 15, 2011
Friday, October 28, 2011
History Of SQL
1970s
It all began at a little known company called IBM in the early 1970s. A couple of researchers named Donald D. Chamberlin and Raymond F. Boyce developed the first incarnation of the SQL language while they were working in IBM’s San Jose Research lab. They originally called this new coding language SEQUEL, which stood for Structured English Query Language. They invented this language to allowed programmers and infrequent database users to interact with data. The original SQL code set identified a set of functions , and a set of simple and consistent rules. If you want to find out more information about the first SEQUEL rule set you can read a paper published by Chamberlin and Raymond, which can be found here: http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf.
It was discovered that the name SEQUEL was already trademarked by a United Kingdom aircraft company named Hawker Siddeley, which caused IBM to change the name of this new data manipulation language. They shortened the name to just three letters, SQL. That is how the SQL language was now born.
In the late 1970s a company called Relational Software Inc., which later became Oracle, saw the value of the relational database model and the SQL language developed by Chamberlin and Raymond. They started developing a database management system they hoped to sell to the U.S. Government. In June 1979 they released the first commercially available RDBMS that used SQL, which was called Oracle V2 which ran on a VAX machine.
Relational Software Inc. beat IBM to market place with SQL, and this wouldn’t be the first or last time that a company beat IBM to the market place on a technology product. Shortly after the release of Oracle V2, IBM released its RDBMS known as System/38 which used the SQL language to manipulate data. System/38 proved to be a viable offering that lead IBM to spend even more time and effort exploring other software applications that took advantage of SQL.
1980s
In the 1980s many other products that used SQL came to market. With many vendors exploiting SQL, the language was standardized by the American National Standards Institute (ANSI) who defined the SQL-86 standard in 1986. In 1987 the International Organization for Standards (ISO) adopted SQL. During this standardization process the official pronunciation for SQL was declared to be ess queue el, but many people still refer to it as sequel.
With the SQL language now being a standard, vendor implementations of SQL found it hard to support their product lines with only standard SQL syntax, therefore vendors started creating their own extensions to the SQL language to enhance their products. This is what led to Sybase to develop the Transact-SQL language extensions to support their own RDBMS implementation. Over time many of these original vendor-specific extensions would be adopted by other vendors, eventually finding their way into the standard SQL language.
In late 1987 Sybase and Microsoft launched into a partnership to produce and market DataServer, which used SQL, and ran on the OS/2 operating system. At the time Ashton-Tate was the leader in PC databases with a product named dBase. In 1989 Microsoft went into a partnership with Ashton-Tate to release the first product that contained the name SQL Server, with a product named Ashton-Tate/Microsoft SQL Server. A beta release of this product was shipped in the fall of 1988. This release was also called version 1.0 of SQL Server by some, since the original name was a little too awkward for many people to refer to easily.
As with any standard, as it is used and expanded by different vendors and products, it needs to evolve. This was no different for SQL. In 1989 a new version of the ANSI/ISO SQL standard was established, which was dubbed SQL-89.
1990s
In the 1990s there were many vendors who were providing products that used SQL. This continued support and expansion of the SQL language kept SQL moving forward in the software evolution process. The standards developed in the 80s, where eventually reviewed and a new standard emerged called SQL-92.
The SQL-92 standard expanded the standard SQL Language by leaps and bounds. SQL-92 brought in the concept of a schema manipulation language. This change introduced the ALTER and DROP commands into the SQL language. It also allowed for the dynamic creation of SQL statements and supported a number of new data types. Additional syntax was added to support outer joins as well as cascade updates and deletes. There were many other new features added to make the SQL language more flexible.
At the time SQL-92 was written there was no one vendor that adhered to the complete standard. Because of this, the standard was broken down into three different levels of compliance: entry, intermediate, and full. Each of these levels implemented a subset of the next higher level. The entry level contained a subset of the functionality of the intermediate level, and the intermediate level contained a subset of the full standard. When buying products in the 1990s, vendors noted the level at which the product conformed to the standard.
In early 1990, Microsoft continued to enhance their 1988 release of SQL Server. In the summer of 1990 they release version 1.1. This version contained many bug fixes, but also supported the just released Windows 3.0 operating system. Microsoft continued to exploit the SQL language when version 4.2 of SQL Server was released in 1993. This was the last release provided during the Sybase/Microsoft partnership.
In June of 1995 Microsoft released version 6.0 of SQL Server, which supported their new Windows 95 operating system. This release was then followed by SQL Server 6.5 in 1996. To round out the different versions released in the 90s Microsoft rolled out SQL Server 7.0 in in 1998.
While Microsoft and other vendors continued rolling out new releases of their database software, the SQL standards continued evolving as well. In 1999 the standards organizations published SQL:1999. This latest standard included a number of new features to support the ever changing SQL Server landscape. It was this version of the standard that incorporated support for large object types, user defined data types (UDT), established the SIMILAR and DISTINCT predicate, and many more new features. By the close of the century SQL was becoming a commonly used language for storing and manipulating relational data.
2000s
Evolution of SQL didn’t stop when the sands of time crossed over to the new millennium. Shortly after the beginning of the twenty-first century Microsoft released the next version of SQL Server, code named Shiloh, or more commonly known as SQL Server 2000. As time marched on it took another 5 years before the next release of SQL Server, code named Yukon, which was released in 2005 with a name of SQL Server 2005. Before the first decade of the new millennium was in the history books Microsoft would release SQL Server 2008, staying with the year theme for their releases.
Just as Microsoft did in updating their older releases with a newer version of SQL Server, so did the governing body over the SQL language standards. During the first decade of the new millennium two different versions of the SQL standards were published: SQL:2003 and SQL:2008. These two new versions bring the standards into the new technology age. These releases provide many clarifications as well as made minor modifications to the already solid SQL language. A couple of significant features released with the SQL:2003 were support for XML and the MERGE statement. With the release of SQL:2008 support for INSTEAD OF triggers, and the TRUNCATE statement are a couple of the key new features.
The Rest is History
The SQL language has come a long way since its early days at IBM. It is now widely used by many database vendors in one flavor or another. Microsoft SQL Server has also evolved into one of the leading contenders within the RDBMS space. The current decade has already seen a new release of SQL Server, named SQL Server 2008 R2, which was released in 2010. The rest of the future for the current decade is not known, but I’m sure the SQL language will continue to be refined and expanded. The SQL language invented in the 70s was incorporated into the first version SQL Server in the late 80s. The rest of the journey from there to where we are at today is just history.
To truly have an appreciation for the world of SQL one needs to have at least a high level understanding of relational database design. In my next Stairway article I will be providing another history lesson about the father of relational database design (Edgar Frank “Ted” Codd), as well as a discussion of different database components related to database design.
It all began at a little known company called IBM in the early 1970s. A couple of researchers named Donald D. Chamberlin and Raymond F. Boyce developed the first incarnation of the SQL language while they were working in IBM’s San Jose Research lab. They originally called this new coding language SEQUEL, which stood for Structured English Query Language. They invented this language to allowed programmers and infrequent database users to interact with data. The original SQL code set identified a set of functions , and a set of simple and consistent rules. If you want to find out more information about the first SEQUEL rule set you can read a paper published by Chamberlin and Raymond, which can be found here: http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf.
It was discovered that the name SEQUEL was already trademarked by a United Kingdom aircraft company named Hawker Siddeley, which caused IBM to change the name of this new data manipulation language. They shortened the name to just three letters, SQL. That is how the SQL language was now born.
In the late 1970s a company called Relational Software Inc., which later became Oracle, saw the value of the relational database model and the SQL language developed by Chamberlin and Raymond. They started developing a database management system they hoped to sell to the U.S. Government. In June 1979 they released the first commercially available RDBMS that used SQL, which was called Oracle V2 which ran on a VAX machine.
Relational Software Inc. beat IBM to market place with SQL, and this wouldn’t be the first or last time that a company beat IBM to the market place on a technology product. Shortly after the release of Oracle V2, IBM released its RDBMS known as System/38 which used the SQL language to manipulate data. System/38 proved to be a viable offering that lead IBM to spend even more time and effort exploring other software applications that took advantage of SQL.
1980s
In the 1980s many other products that used SQL came to market. With many vendors exploiting SQL, the language was standardized by the American National Standards Institute (ANSI) who defined the SQL-86 standard in 1986. In 1987 the International Organization for Standards (ISO) adopted SQL. During this standardization process the official pronunciation for SQL was declared to be ess queue el, but many people still refer to it as sequel.
With the SQL language now being a standard, vendor implementations of SQL found it hard to support their product lines with only standard SQL syntax, therefore vendors started creating their own extensions to the SQL language to enhance their products. This is what led to Sybase to develop the Transact-SQL language extensions to support their own RDBMS implementation. Over time many of these original vendor-specific extensions would be adopted by other vendors, eventually finding their way into the standard SQL language.
In late 1987 Sybase and Microsoft launched into a partnership to produce and market DataServer, which used SQL, and ran on the OS/2 operating system. At the time Ashton-Tate was the leader in PC databases with a product named dBase. In 1989 Microsoft went into a partnership with Ashton-Tate to release the first product that contained the name SQL Server, with a product named Ashton-Tate/Microsoft SQL Server. A beta release of this product was shipped in the fall of 1988. This release was also called version 1.0 of SQL Server by some, since the original name was a little too awkward for many people to refer to easily.
As with any standard, as it is used and expanded by different vendors and products, it needs to evolve. This was no different for SQL. In 1989 a new version of the ANSI/ISO SQL standard was established, which was dubbed SQL-89.
1990s
In the 1990s there were many vendors who were providing products that used SQL. This continued support and expansion of the SQL language kept SQL moving forward in the software evolution process. The standards developed in the 80s, where eventually reviewed and a new standard emerged called SQL-92.
The SQL-92 standard expanded the standard SQL Language by leaps and bounds. SQL-92 brought in the concept of a schema manipulation language. This change introduced the ALTER and DROP commands into the SQL language. It also allowed for the dynamic creation of SQL statements and supported a number of new data types. Additional syntax was added to support outer joins as well as cascade updates and deletes. There were many other new features added to make the SQL language more flexible.
At the time SQL-92 was written there was no one vendor that adhered to the complete standard. Because of this, the standard was broken down into three different levels of compliance: entry, intermediate, and full. Each of these levels implemented a subset of the next higher level. The entry level contained a subset of the functionality of the intermediate level, and the intermediate level contained a subset of the full standard. When buying products in the 1990s, vendors noted the level at which the product conformed to the standard.
In early 1990, Microsoft continued to enhance their 1988 release of SQL Server. In the summer of 1990 they release version 1.1. This version contained many bug fixes, but also supported the just released Windows 3.0 operating system. Microsoft continued to exploit the SQL language when version 4.2 of SQL Server was released in 1993. This was the last release provided during the Sybase/Microsoft partnership.
In June of 1995 Microsoft released version 6.0 of SQL Server, which supported their new Windows 95 operating system. This release was then followed by SQL Server 6.5 in 1996. To round out the different versions released in the 90s Microsoft rolled out SQL Server 7.0 in in 1998.
While Microsoft and other vendors continued rolling out new releases of their database software, the SQL standards continued evolving as well. In 1999 the standards organizations published SQL:1999. This latest standard included a number of new features to support the ever changing SQL Server landscape. It was this version of the standard that incorporated support for large object types, user defined data types (UDT), established the SIMILAR and DISTINCT predicate, and many more new features. By the close of the century SQL was becoming a commonly used language for storing and manipulating relational data.
2000s
Evolution of SQL didn’t stop when the sands of time crossed over to the new millennium. Shortly after the beginning of the twenty-first century Microsoft released the next version of SQL Server, code named Shiloh, or more commonly known as SQL Server 2000. As time marched on it took another 5 years before the next release of SQL Server, code named Yukon, which was released in 2005 with a name of SQL Server 2005. Before the first decade of the new millennium was in the history books Microsoft would release SQL Server 2008, staying with the year theme for their releases.
Just as Microsoft did in updating their older releases with a newer version of SQL Server, so did the governing body over the SQL language standards. During the first decade of the new millennium two different versions of the SQL standards were published: SQL:2003 and SQL:2008. These two new versions bring the standards into the new technology age. These releases provide many clarifications as well as made minor modifications to the already solid SQL language. A couple of significant features released with the SQL:2003 were support for XML and the MERGE statement. With the release of SQL:2008 support for INSTEAD OF triggers, and the TRUNCATE statement are a couple of the key new features.
The Rest is History
The SQL language has come a long way since its early days at IBM. It is now widely used by many database vendors in one flavor or another. Microsoft SQL Server has also evolved into one of the leading contenders within the RDBMS space. The current decade has already seen a new release of SQL Server, named SQL Server 2008 R2, which was released in 2010. The rest of the future for the current decade is not known, but I’m sure the SQL language will continue to be refined and expanded. The SQL language invented in the 70s was incorporated into the first version SQL Server in the late 80s. The rest of the journey from there to where we are at today is just history.
To truly have an appreciation for the world of SQL one needs to have at least a high level understanding of relational database design. In my next Stairway article I will be providing another history lesson about the father of relational database design (Edgar Frank “Ted” Codd), as well as a discussion of different database components related to database design.
Thursday, October 27, 2011
My List of Useful Websites
As a professional software developer, I have come across many websites which I use in,
1) Getting the code snippet
2) Getting help while debugging my code
3) Fixing some really critical issues
4) How to perform testing
5) How to increase performance even
6) How to write mails and
7) How to communicate and present and a lot more.
Over the years I have found that these websites are quite helpful in increasing my productivity and gaining a better understanding of the subject I am currently working upon. The list of such websites has grown over time and I find them quite indispensable. Even though they are my personal choice only, I feel that the list will be very usable for other developers too.
We have www.google.com for almost every situation (at least until something better comes up and. http://msdn.microsoft.com/en-in, http://technet.microsoft.com/en-us/default and http://www.codeproject.com/ for Microsoft Technology. I have categorised the websites into various groups to help you locate a specific website as per requirement.
Process/Standards
OOPS
Link: http://www.codeproject.com/KB/architecture/OOP_Concepts_and_manymore.aspx
This article elaborates the concept of oops and believe me this is one of the best article I have ever read in the topic of OOPs. This one is neatly described by Nirosh in codeproject, with clear pictorial representation and some real life examples.
Project Management
http://www.projectsmart.com/
Coding Standards/Best Practice
C#
http://msdn.microsoft.com/en-us/library/xzf533w0(v=VS.71).aspx
http://msdn.microsoft.com/en-us/library/czefa0ke(v=VS.71).aspx
http://weblogs.asp.net/lhunt/pages/CSharp-Coding-Standards-document.aspx
Sql Server
http://technet.microsoft.com/en-us/library/cc966413.aspx
http://www.extremeexperts.com/sql/articles/BestPractices.aspx
http://www.pinaldave.com/sql-download/SQLServerGuideLines.pdf
Language/Tools
C#/.Net
http://msdn.microsoft.com/en-us/library/kx37x362.aspx
http://msdn.microsoft.com/en-us/vcsharp/
http://www.software-pointers.com/en-articles-csharp.html
http://www.csharphelp.com/
http://www.c-sharpcorner.com/
http://www.csharp-examples.net/
http://www.blackwasp.co.uk/Default.aspx
http://www.dotnetcurry.com/BrowseArticles.aspx
http://www.csharptricks.com/
http://beyondrelational.com/
C# /.Net Blogs
http://msdn.microsoft.com/en-us/netframework/aa569283.aspx
http://www.dijksterhuis.org/
http://www.networkedblogs.com/topic/C%23/
http://www.csharp411.com/
http://www.techrepublic.com/
http://blogs.microsoft.co.il/blogs/gilf/
http://www.devmarch.com/.NET/exclusives/writing-high-performance-net-code.html
http://beyondrelational.com/
Windows Programming
http://www.blackwasp.co.uk/Default.aspx
Database
SQL Server
http://blog.sqlauthority.com/
http://beyondrelational.com/
http://www.extremeexperts.com/General/Home/Homepage.aspx
http://www.databasejournal.com/
http://www.sql-server-performance.com/
Visual Studio
http://visualstudiomagazine.com/Home.aspx
http://msdn.microsoft.com/en-us/vstudio/aa718325
http://www.microsoft.com/visualstudio/en-us
http://www.learnvisualstudio.net/
Testing
http://www.quicktestingtips.com/tips/
http://www.softwaretestinghelp.com/practical-software-testing-tips-to-test-any-application/
http://www.readwriteweb.com/archives/12_unit_testing_tips_for_software_engineers.php
Testing Blogs
http://www.sujitnayak.com/?category_name=testing-tips
Interview Questions
http://www.questpond.com/
http://blog.sqlauthority.com/
http://www.geekinterview.com/Interview-Questions
http://csharp-interview-questions.net-tutorials.org/category/c-interview-questions/basic-interview-questions/
Quiz
http://www.software-architects.com/TechnicalArticles/CNETSecrets/tabid/81/Default.aspx
http://www.ex-designz.net/test_page.asp?tid=48
http://beyondrelational.com/quiz/default.aspx
Tips & Tricks
#justlearned
Follow @jacobsebastian on twitter for everyday tips and tricks saying #JustLearned
http://beyondrelational.com/
http://www.dailycoding.com/
http://dailydotnettips.com/tag/tips-tricks/
http://abhijitjana.net/2011/02/28/15-tips-from-daily-net-tips/
http://dotnetkicks.com/default.aspx
Tutorials
http://www.w3schools.com/
http://www.html-5.com/tutorials/index.html
http://www.csharp-station.com/Tutorial.aspx
http://csharp.net-tutorials.com/
http://www.functionx.com/csharp/
http://www.pluralsight-training.net/microsoft/
Presentation Skill/ Public Speaking
Public Speaking
http://blogs.extremeexperts.com/2010/10/11/being-a-public-speaker/
This article shows you the way and put emphasis on the different points that is required by a public speaker. Article like this can flow from the pen of a genius public speaker, and yes Vinod Kumar is the one. I have personally attended many of his presentations and you have to trust me when I say “He is the guide you are searching for”. Also visit his blog and you will get plenty of articles on SQL SERVER and presentation skills.
Presentation Skills
http://blogs.extremeexperts.com/2010/06/21/presentation-experiences-and-tips/
Blogs Writing
http://www.wikihow.com/Write-a-Famous-Blog
http://www.problogger.net/31-days-to-building-a-better-blog/
http://www.lifehack.org/articles/technology/101-steps-to-becoming-a-better-blogger.html
http://weblogbetter.com/
Cheat Sheet
http://www.cheat-sheets.org/
http://refcardz.dzone.com/
Community
http://bdotnet.in/
http://bitpro.in/
http://www.ineta.org/
http://www.sqlpass.org/
Please add your choice and let it grow. I will be waiting for your suggestion and feedback on this. Till next time “Challenge you limits”.
1) Getting the code snippet
2) Getting help while debugging my code
3) Fixing some really critical issues
4) How to perform testing
5) How to increase performance even
6) How to write mails and
7) How to communicate and present and a lot more.
Over the years I have found that these websites are quite helpful in increasing my productivity and gaining a better understanding of the subject I am currently working upon. The list of such websites has grown over time and I find them quite indispensable. Even though they are my personal choice only, I feel that the list will be very usable for other developers too.
We have www.google.com for almost every situation (at least until something better comes up and. http://msdn.microsoft.com/en-in, http://technet.microsoft.com/en-us/default and http://www.codeproject.com/ for Microsoft Technology. I have categorised the websites into various groups to help you locate a specific website as per requirement.
Process/Standards
OOPS
Link: http://www.codeproject.com/KB/architecture/OOP_Concepts_and_manymore.aspx
This article elaborates the concept of oops and believe me this is one of the best article I have ever read in the topic of OOPs. This one is neatly described by Nirosh in codeproject, with clear pictorial representation and some real life examples.
Project Management
http://www.projectsmart.com/
Coding Standards/Best Practice
C#
http://msdn.microsoft.com/en-us/library/xzf533w0(v=VS.71).aspx
http://msdn.microsoft.com/en-us/library/czefa0ke(v=VS.71).aspx
http://weblogs.asp.net/lhunt/pages/CSharp-Coding-Standards-document.aspx
Sql Server
http://technet.microsoft.com/en-us/library/cc966413.aspx
http://www.extremeexperts.com/sql/articles/BestPractices.aspx
http://www.pinaldave.com/sql-download/SQLServerGuideLines.pdf
Language/Tools
C#/.Net
http://msdn.microsoft.com/en-us/library/kx37x362.aspx
http://msdn.microsoft.com/en-us/vcsharp/
http://www.software-pointers.com/en-articles-csharp.html
http://www.csharphelp.com/
http://www.c-sharpcorner.com/
http://www.csharp-examples.net/
http://www.blackwasp.co.uk/Default.aspx
http://www.dotnetcurry.com/BrowseArticles.aspx
http://www.csharptricks.com/
http://beyondrelational.com/
C# /.Net Blogs
http://msdn.microsoft.com/en-us/netframework/aa569283.aspx
http://www.dijksterhuis.org/
http://www.networkedblogs.com/topic/C%23/
http://www.csharp411.com/
http://www.techrepublic.com/
http://blogs.microsoft.co.il/blogs/gilf/
http://www.devmarch.com/.NET/exclusives/writing-high-performance-net-code.html
http://beyondrelational.com/
Windows Programming
http://www.blackwasp.co.uk/Default.aspx
Database
SQL Server
http://blog.sqlauthority.com/
http://beyondrelational.com/
http://www.extremeexperts.com/General/Home/Homepage.aspx
http://www.databasejournal.com/
http://www.sql-server-performance.com/
Visual Studio
http://visualstudiomagazine.com/Home.aspx
http://msdn.microsoft.com/en-us/vstudio/aa718325
http://www.microsoft.com/visualstudio/en-us
http://www.learnvisualstudio.net/
Testing
http://www.quicktestingtips.com/tips/
http://www.softwaretestinghelp.com/practical-software-testing-tips-to-test-any-application/
http://www.readwriteweb.com/archives/12_unit_testing_tips_for_software_engineers.php
Testing Blogs
http://www.sujitnayak.com/?category_name=testing-tips
Interview Questions
http://www.questpond.com/
http://blog.sqlauthority.com/
http://www.geekinterview.com/Interview-Questions
http://csharp-interview-questions.net-tutorials.org/category/c-interview-questions/basic-interview-questions/
Quiz
http://www.software-architects.com/TechnicalArticles/CNETSecrets/tabid/81/Default.aspx
http://www.ex-designz.net/test_page.asp?tid=48
http://beyondrelational.com/quiz/default.aspx
Tips & Tricks
#justlearned
Follow @jacobsebastian on twitter for everyday tips and tricks saying #JustLearned
http://beyondrelational.com/
http://www.dailycoding.com/
http://dailydotnettips.com/tag/tips-tricks/
http://abhijitjana.net/2011/02/28/15-tips-from-daily-net-tips/
http://dotnetkicks.com/default.aspx
Tutorials
http://www.w3schools.com/
http://www.html-5.com/tutorials/index.html
http://www.csharp-station.com/Tutorial.aspx
http://csharp.net-tutorials.com/
http://www.functionx.com/csharp/
http://www.pluralsight-training.net/microsoft/
Presentation Skill/ Public Speaking
Public Speaking
http://blogs.extremeexperts.com/2010/10/11/being-a-public-speaker/
This article shows you the way and put emphasis on the different points that is required by a public speaker. Article like this can flow from the pen of a genius public speaker, and yes Vinod Kumar is the one. I have personally attended many of his presentations and you have to trust me when I say “He is the guide you are searching for”. Also visit his blog and you will get plenty of articles on SQL SERVER and presentation skills.
Presentation Skills
http://blogs.extremeexperts.com/2010/06/21/presentation-experiences-and-tips/
Blogs Writing
http://www.wikihow.com/Write-a-Famous-Blog
http://www.problogger.net/31-days-to-building-a-better-blog/
http://www.lifehack.org/articles/technology/101-steps-to-becoming-a-better-blogger.html
http://weblogbetter.com/
Cheat Sheet
http://www.cheat-sheets.org/
http://refcardz.dzone.com/
Community
http://bdotnet.in/
http://bitpro.in/
http://www.ineta.org/
http://www.sqlpass.org/
Please add your choice and let it grow. I will be waiting for your suggestion and feedback on this. Till next time “Challenge you limits”.
Find column names which have Special characters
The following script helps to find columns which have special characters other than alphanumeric, in a given database. If some special character for example under score "_" as part of column name and want to ignore, include special character with escape character as below in regular expression. col.COLUMN_NAME LIKE '%[^a-zA-Z0-9/_]%'
USE[]
GO
SELECT tab.TABLE_NAME,col.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS col
INNER JOIN INFORMATION_SCHEMA.TABLES tab
ON tab.Table_name = col.TABLE_NAME
WHERE tab.TABLE_TYPE = 'BASE TABLE'
AND
col.COLUMN_NAME LIKE '%[^a-zA-Z0-9]%'
USE[
GO
SELECT tab.TABLE_NAME,col.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS col
INNER JOIN INFORMATION_SCHEMA.TABLES tab
ON tab.Table_name = col.TABLE_NAME
WHERE tab.TABLE_TYPE = 'BASE TABLE'
AND
col.COLUMN_NAME LIKE '%[^a-zA-Z0-9]%'
Neo Launches NoSQL Graph Database
NoSQL type databases have become increasingly popular over the last several years as a way to deliver better scalability and performance. There are a number of different types of NoSQL databases, including a graph database structure, which is what open source startup Neo Technology is all about.
Neo Technology is the lead commercial sponsor behind the open source Neo4j NoSQL database. This week the company is launching its Spring Data Neo4j 2.0 release, bringing the database to the popular Spring Java framework. The company has also just completed raising $10.6 million in Series A funding.
"There is so much noise in the NoSQL space now, it's a term that isn't strictly defined," Emil Eifrem, CEO of Neo, told InternetNews.com.
In Eifrem's view, there are only four types of NoSQL databases: key value stores, BigTable types like Apache Cassandra, document databases like CouchDB and MongoDB, and the fourth is Graph databases like Neo4j.
"In the graph data model, there are nodes with type relationships across nodes," Eifrem said.
Eifrem said a graph database can then attach key value pairs to nodes and their relationship. He noted that the way nodes are connected is a first-class citizen in the graph data model.
In contrast with the concept of a traditional join that exists in traditional relational SQL type databases, Eifrem said there are some key underlying differences.
"What the relational guys did is work with a data model that is all tables that is optimized for access and that goes along with a table," Eifrem said.
For example, in the relational model, if you're looking for all the people in a table with an age greater than 20, that's an optimized query. Eifrem said that if you want to hop from one entity to another, that requires a join, which is a CPU-bound operation where you merge the entities that match your criteria from the first table with the second table.
"In contrast, with a graph database the only thing you do when you hop from one node to another is just have a direct pointer for access," Eifrem said. "You don't have to traverse an index or do a merge which leads to some amazing performance improvements."
Neo4j is Java-based but does not require Java middleware in order to run. Eifrem noted that it works well as a POJO (Plain Old Java Object). There are also bindings to connect Neo4j for other languages.
While Neo4j offers performance and scalability benefits, it's not a drop-in replacement for a relational SQL database.
"We don't have a SQL layer and we can't emulate SQL," Eifrem said.
That said, he added that if an enterprise is building a new application, or if they want to take parts of a database and use a graph database, that is possible. Currently there are no tools for migrating data from a relational database like Oracle to neo4j. Eifrem said that is something his company is looking at for a future product roadmap.
The new Spring Data Neo4j 2.0 release is a JPA (Java Persistence API) for the graph database. Eifrem explained that it makes it easier for Spring developers to leverage and use neo4j 2.0 as part of their Java applications.
Sean Michael Kerner is a senior editor at InternetNews.com, the news service of Internet.com, the network for technology professionals.
Neo Technology is the lead commercial sponsor behind the open source Neo4j NoSQL database. This week the company is launching its Spring Data Neo4j 2.0 release, bringing the database to the popular Spring Java framework. The company has also just completed raising $10.6 million in Series A funding.
"There is so much noise in the NoSQL space now, it's a term that isn't strictly defined," Emil Eifrem, CEO of Neo, told InternetNews.com.
In Eifrem's view, there are only four types of NoSQL databases: key value stores, BigTable types like Apache Cassandra, document databases like CouchDB and MongoDB, and the fourth is Graph databases like Neo4j.
"In the graph data model, there are nodes with type relationships across nodes," Eifrem said.
Eifrem said a graph database can then attach key value pairs to nodes and their relationship. He noted that the way nodes are connected is a first-class citizen in the graph data model.
In contrast with the concept of a traditional join that exists in traditional relational SQL type databases, Eifrem said there are some key underlying differences.
"What the relational guys did is work with a data model that is all tables that is optimized for access and that goes along with a table," Eifrem said.
For example, in the relational model, if you're looking for all the people in a table with an age greater than 20, that's an optimized query. Eifrem said that if you want to hop from one entity to another, that requires a join, which is a CPU-bound operation where you merge the entities that match your criteria from the first table with the second table.
"In contrast, with a graph database the only thing you do when you hop from one node to another is just have a direct pointer for access," Eifrem said. "You don't have to traverse an index or do a merge which leads to some amazing performance improvements."
Neo4j is Java-based but does not require Java middleware in order to run. Eifrem noted that it works well as a POJO (Plain Old Java Object). There are also bindings to connect Neo4j for other languages.
While Neo4j offers performance and scalability benefits, it's not a drop-in replacement for a relational SQL database.
"We don't have a SQL layer and we can't emulate SQL," Eifrem said.
That said, he added that if an enterprise is building a new application, or if they want to take parts of a database and use a graph database, that is possible. Currently there are no tools for migrating data from a relational database like Oracle to neo4j. Eifrem said that is something his company is looking at for a future product roadmap.
The new Spring Data Neo4j 2.0 release is a JPA (Java Persistence API) for the graph database. Eifrem explained that it makes it easier for Spring developers to leverage and use neo4j 2.0 as part of their Java applications.
Sean Michael Kerner is a senior editor at InternetNews.com, the news service of Internet.com, the network for technology professionals.
SQL Server Denali: New warnings in Actual Execution Plan
I’ve played with SQL Server Denali and found some new features and improvements in Query Execution plan. My intention was to test Extended Events and new events: sort_warning and hash_warnig. In SQL Server 2008R2, we can create SQL Trace to capture sort and hash warnings but there is no easy way to find out which statement cause it. I hoped that with new XEvent in Denali we can do that easily.
I reduced maximum server memory for SQL Server, ran some queries with ORDER BY and here is the Actual Execution Plan for one query.
Do you see something new? There is a warning on Sort operator (1) and on SELECT (2).
If we look at the Sort operator and SELECT we can see more information about warnings.
In Denali we see Sort warnings in query execution plan. It seems that some waits are also visible. Is the MemoryGrant only wait type that will appears in query plan?
I don’t know what kinds of warnings are included in Denali actual execution query plan but I think that these two warnings are not the only one.
I reduced maximum server memory for SQL Server, ran some queries with ORDER BY and here is the Actual Execution Plan for one query.
Do you see something new? There is a warning on Sort operator (1) and on SELECT (2).
If we look at the Sort operator and SELECT we can see more information about warnings.
In Denali we see Sort warnings in query execution plan. It seems that some waits are also visible. Is the MemoryGrant only wait type that will appears in query plan?
I don’t know what kinds of warnings are included in Denali actual execution query plan but I think that these two warnings are not the only one.
Isolation level for subsequent transactions issue
When you call the SqlConnection.BeginTransaction method and specify an isolation level in the IsolationLevel property, after the transaction is committed or rolled back, the IsolationLevel property is not reset to the default of IsolationLevel.ReadCommitted for subsequent commands. This can cause unexpected locking or cause the application to read the wrong version of a record. Use one of the following workarounds to set the isolation level back to the default IsolationLevel.ReadCommitted:
1.Close and re-open the connection. If you are using connection pooling (which is the default), make sure that you did not specify "connection reset=false" in the connect string. "Connection Reset" is "true" by default.
Run a SQL statement through a SqlCommand object to manually reset the transaction isolation level as follows
view sourceprint?
SqlCommand resetCMD = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn);
resetCMD.ExecuteNonQuery()
1.Close and re-open the connection. If you are using connection pooling (which is the default), make sure that you did not specify "connection reset=false" in the connect string. "Connection Reset" is "true" by default.
Run a SQL statement through a SqlCommand object to manually reset the transaction isolation level as follows
view sourceprint?
SqlCommand resetCMD = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn);
resetCMD.ExecuteNonQuery()
Tuesday, October 25, 2011
I got TC Winner Badge
<a href="http://beyondrelational.com/members/SineethaParveen/badges/tcwinner/default.aspx"> <img src="http://beyondrelational.com/images/badges/tcwinner.jpg" alt="TC Winner" /> </a>
Sunday, October 16, 2011
For
those SQL Server and development shops that have built a lot of SQL Server
Integration Services (SSIS) packages and have utilized Visual Studio Team
Foundation Server or Visual SourceSafe to provide version control over those
SSIS packages, you will be glad to learn about the new versioning capabilities
in SQL Server v-next, code-named “Denali.” In fact, when I’ve been trying out
the new version of SSIS, I have found the overall deployment experience to be a
huge step forward over the 2005/2008 deployment model, with many advances in
terms of configuration management and deployment to a server.