As a .NET Developer most of the common tasks you do are database related operations, like INSERT, SELECT UPDATE and DELETE. These tasks are often collectively referred to as CRUD operations. The problem comes when writing a complex query directly or in a Stored Procedure that retrieves expected data from more than one table of your Normalized database, in other words you are working on “Joining the Tables” to pull the data. Read Full Article Here

Agenda

  • Creating two sample tables for Join
  • Insert data into sample Join tables
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Cross Join

This article explains the most commonly used and must-know SQL Server functions and techniques that help and are handy in daily .NET Development and working with SQL Server Database projects. I found that working knowledge of these functions are very helpful and used very often in software development. Read Full Article Here

Article Covers

  • GETDATE()
  • DATEADD()
  • DATENAME()
  • DATEPART()
  • DATEDIFF()
  • DAY()
  • MONTH()
  • YEAR()
  • DATALENGTH()
  • APP_NAME()
  • HOST_NAME()
  • SYSTEM_USER
  • @@IDENTITY
  • IDENT_CURRENT
  • SET IDENTITY_INSERT
  • sp_defaultdb
  • Sp_Password

 

Using CLR With SQL Server 2012

December 9th, 2013 | Posted by Vidya Vrat in .NET | C# | CLR | SQL Server - (0 Comments)

In this article, I’ll cover the following:

  • Introduction to SQL CLR
  • Choosing Between T-SQL and SQL CLR
  • Enabling SQL CLR Integration
  • Creating a SQL CLR Stored Procedure
  • Deploying a SQL CLR Stored Procedure into SQL Server
  • Executing the SQL CLR Stored Procedure

Introduction to SQL CLR
SQL CLR is a tiny version of the .NET CLR that is integrated into the SQL Server 2005 and onwards. The existence of CLR in SQL Server allows the C# programmers and other .NET compliant language programmers to write database specific business logic in a programming language like C# instead of T-SQL. Let’s understand what type of objects a programmer can create with SQL CLR integration. Read Full Article Here
The following are objects that can be created using SQL CLR:

  • The following types of database objects can be created with SQL CLR Integration:
  • Stored Procedures
  • User defined aggregates
  • Triggers
  • User defined types

 

Understanding Transactions

September 29th, 2013 | Posted by Vidya Vrat in Database | SQL Server - (0 Comments)

For any business, transactions that may be comprised of many individual operations and even other transactions, play a key role. Transactions are essential for maintaining data integrity, both for multiple related operations and when multiple users that update the database concurrently.

This article will specifically talk about the concepts related to transactions and how transactions can be used in the context of a SQL Server database. Besides, a transaction is a fundamental concept and this article will be helpful for relating transaction concepts with other databases as well.

Read Full Article Here

SQL Server 2008 and SQL Server 2012 are two separate versions of databases.

Also, SSMS (SQL Server Management Studio) of SQL Server 2008 and SQL Server 2012 have different look and feel (UI).

But, SQL Server 2008 SSMS can still successfully access and connect to SQL Server 2012 instance.
Similarly, SQL Server 2012 can connect to SQL Server 2008 instance.

As SQL Server 2012 and Visual Studio 2011 Developer Preview are not yet released and so there bits are unstable when installed in wrong sequence.

This exception is known to occur when you start SSMS (SQL Server Management Studio) of SQL server 2012. This exception is being caused to occur when you installed VS 2011 on SQL 2012 RRC0 installed machine, I.e you install SQL Server 2012 1st and then VS 2011.

Otherwise you might have noticed that prior to installation of VS 2011; your SSMS was working fine.

many sites advise to uninstall and re-install SQL Server and VS 2011 in appropriate sequence or even keep these in two different machines atleast until these products are mature and finally released.

This is pretty cokplicated issues, now to solve this you need to modify a registry entry and you are set to work again.

1- Start regedit.exe

2- Expand HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio

3- You will see a node “11.0_Config”, DELETE this

4- Close Registry Editor,

5- Restart SQL Server 2012 SSMS (SQL Server Management Studio)

It will load just fine as expected.

Hope this will help.

 

In order to connect with SQL Azure Db, you need special version of SQL Server Management Studio.

Ability to connect with SQL Azure is buid in “SQL Server 2008 R2 November CTP”.

You can download this from SQL Server® 2008 R2 November Community Technology Preview – Express

SQL Server 2008 Experience

October 1st, 2008 | Posted by Vidya Vrat in SQL Server - (0 Comments)

Microsoft SQL Server 2008 Experience page on which you can watch many videos for Developers, decision makers and for fun as well.

You will watch and listen the real people who are behind the creation of SQL Server 2008.

Click here:
SQL Server 2008 Experience

You can download the SQL Server 2008 ScreenSaver and Wallpaper.
Download here

With SQL Server 2008, we have got an interesting enhancement in Insert statement.

Originally from previous releases of SQL Server until SQL Server 2005, an INSERT statement was supposed to accept set of values for one row/record at a time.

INSERT INTO Customers (Name,Qty,Price)
Values(‘A’,2,12)

INSERT INTO Customers (Name,Qty,Price)
Values(‘B’,3,24)

But with SQL Sever 2008, we can insert multiple rows/records at a time just separated by comma as a delimiter:

INSERT INTO Customers (Name,Qty,Price)
Values(‘A’,2,12),(‘B’,3,24)

This will help the developers to save their time while inserting data.