Tar's Arena

Print from 1 to 100 sequentially with out using cursor, while or any other iterative loop. Ans: We can achieve this by using Common Table Expression (CTE). CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. We can achieve the above using recursive CTE: Please execute the below statements: WITH Series (Number) AS ( SELECT 1 UNION ALL SELECT Number +1 FROM Series WHERE Number 100 ) Select * from Series And see the result

Millions of developers in both small and large enterprises use Visual Studio to more easily use the latest platform technology to revolutionize the way they build, maintain, and deploy today’s complex business applications. Visual Studio supports a rich, diverse ecosystem of developers who ship thousands of Visual Studio integrated products to make it easy for Visual Studio users to use their favorite tool their way. And if someone else hasn’t created it, you can -- the Visual Studio SDK is freely available to the developer community and contains everything you need to extend Visual Studio. With Visual Studio, you’ll find new opportunities to build extensions for specific development needs. These include the Visual Studio Shell, a foundational IDE for custom tools and programming languages, Domain-Specific Language (DSL) tools, a set of tools to help you create graphical models that generate code, and support for Visual Basic within the Visual Studio SDK. Released on a quarterly basis,...

Three Functions of SQL Server: 1) Row_Number() -- To get the Unique order of Rows 2) Rank() – To rank the Records with skipped Rows 3) Dense_Rank() – To rank the Records without skipping any Rows Lets prepare base for these: 1) Create a table like this: CREATE TABLE [dbo].[Student]( [StudentID] [int] IDENTITY(1,1) NOT NULL, [StudentName] [varchar](50) NULL, [Age] [int] NULL ) ON [PRIMARY] 2) Insert data into that like this: 3) Now run the below Query: SELECT * ,ROW_NUMBER() OVER (ORDER BY age DESC) [Unique Order] ,RANK() OVER (ORDER BY age DESC) [Skipped Order] ,DENSE_RANK() OVER (ORDER BY age DESC) [Multiple Order] FROM student If u closely look at the above result set [Unique Order] column represent the Unique row number of each row which we got using Row_Number(). [Skipped Order] column represent the order of Rank which skips the number. This may be use in situation where u need to find out the rank of student based on marks, or rank of participants based on points.. [Multiple Orde...

Problem: Which one of the following should be use to get the last Identity of the recently added row in T-SQL or Stored Procedure: @@Identity, Scope_Identity (), IDENT_Current(‘tablename’) Solution: So many times we required to get the last inserted rows identity to be insert them in child table for reference in stored procedure. All the above statements gives us the last identity inserted but in different perspective. So if you want to get the last identity in T-SQL or Stored Procedure always use Scope_Identity () to avoid problems in Multiuser (concurrent) scenarios. Let me explain why? Lets see what does each means? @@Identity : It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current...

SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table. Let us take a look at a common scenario now and how OUTPUT clause can be used to solve the problem. Use of identity column as primary key in a table is a fairly common practice. Additionally, if you have references to the identity column you need to know the value explicitly before inserting the related row. This scenario gets complicated if multiple identity values are generated as a result of inserting multiple rows. In this case, there is no easy way to determine the values that were inserted without using a trigger to populate a temporar...

Problem: One of the Junior SQL Server Developers in my company approached me yesterday with a dilemma. He was developing an SSIS Package which imports data from a comma separated text file and he wanted to know the different ways in which one can execute an SSIS Package in SQL Server 2005 and higher versions. At first I started to tell him, but figured it would be smarter to document the options and share the information. Solution: In SQL Server 2005 and higher versions there are different ways in which one can execute an SSIS package. Let us go through each option one by one. Execute SSIS Package Using SQL Server Business Intelligence Development Studio (BIDS) During the development phase of the project developers can test the SSIS package execution by running the package from Business Intelligence Development Studio a.k.a. BIDS. 1. In Solution Explorer, right click the SSIS project folder that contains the package which you want to run and then click properties as shown in the snipp...