Find the best tutors and institutes for MS SQL Development

Find Best MS SQL Development Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

MS SQL Development Updates

Ask a Question

Post a Lesson

All

All

Lessons

Discussion

Lesson Posted on 03 Aug IT Courses/MS SQL IT Courses/MS SQL/MS SQL Certification IT Courses/MS SQL/MS SQL Administration +1 IT Courses/MS SQL/MS SQL Development less

What's the best way to learn SQL Server?

Manoj Kumar Vishwakarma

I have MCA ( Master of Computer Application ) regular and have 16 year IT Teaching Experience in technical...

Just read the book, type and run the code, and work the chapter examples. If you work the book, you will be ahead of many SQL Server programmers. You’ll understand declarative set-based interaction with SQL Server and know many critical language elements and built-in functions. The first couple... read more

Just read the book, type and run the code, and work the chapter examples. If you work the book, you will be ahead of many SQL Server programmers.

You’ll understand declarative set-based interaction with SQL Server and know many critical language elements and built-in functions.

The first couple chapters address SQL Server internals, DDL, and basic normalisation of relational data for applications. It doesn’t cover other relational data structures such as star schema.

After that, it dives into solid querying patterns and eventually gets into DML.

Some object-oriented programmers have difficulty writing good declarative set-based T-SQL code. But programmers that are fluent in object-oriented programming and scripting, and also fluent in T-SQL declarative set-based programming fundamentals, often build apps with fewer performance bottlenecks and require less rework. In my experience.

SQL Server administrator

The Microsoft SQL Server organisation subjects page gives assets to the undertaking SQL Server people group that incorporate the most recent themes on SQL Server execution, and tuning, reinforcement and recuperation, security, establishment and the sky is the limit from there. Peruse the SQL Server organisation subtopics underneath to find the most recent news and master specialised guidance on the best way, to begin with, SQL Server and database organisation, with fundamental instructional exercises and tips on database demonstrating and outline. I suggest svr technologies is the best place to learn SQL server.

Installation
Performance Tuning
Tools and Utilities
Backup and Recovery
Availability and Scalability
Interoperability
Replication
Security
Stored Procedures
T-SQL
SQL Server developer

The SQL Server database advancement theme page gives assets to the undertaking DB improvement group that incorporate the most recent points on putting away systems, SQL and T-SQL, .NET and the sky is the limit from there. You'll likewise discover master exhortation on database displaying an outline for Microsoft SQL Server, and also tips on business insight (BI) systems and information warehousing. Peruse the Microsoft SQL Server online training improvement subtopics beneath to find the most recent news, master specialised guidance, and that's only the tip of the iceberg.

Net Development
Database Design and Modeling
XML
SQL Server management issues

The Microsoft SQL Server administration issues subjects page gives assets to big business database supervisors including themes on SQL Server online relocation systems and permitting contemplations. You'll likewise discover subtle elements on SQL Server virtualisation with Microsoft Hyper-V and database solidification.

Business intelligence and data warehousing

The requirement for associations to have the sound business knowledge and information warehousing arrangements is winding up more basic by the day. Microsoft has perceived this by including a few SQL Server Business Intelligence (BI) improvements to the most recent releases of its database administration framework, SQL Server training online.

Data Visualization
Analysis Services
Integration Services
Reporting Services
BI Strategies
Data Warehousing

read less
Comments
Dislike Bookmark

Answered on 21 Apr IT Courses/MS SQL/MS SQL Administration IT Courses/MS SQL/MS SQL Development IT Courses/MS SQL/MS SQL Integration

Abdul

Trainer

Hi. give me details . will give better solution to you.
Answers 5 Comments
Dislike Bookmark

Lesson Posted on 19/12/2017 IT Courses/MS SQL IT Courses/MS SQL/MS SQL Development

What Is WorkTable In SQL Server?

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

What is WorkTable in SQL Server? USE AdventureWorks2014 GO SET STATISTICS IO ON GO SELECT * FROM Production.Product p CROSS JOIN Production.Product p1 GO If you run above query, there is good chance that it will give you following details in the message windows. Table ‘Product’.... read more

What is WorkTable in SQL Server?

USE AdventureWorks2014

GO

SET STATISTICS IO ON

GO

SELECT *

FROM Production.Product p

CROSS JOIN Production.Product p1

GO

If you run above query, there is good chance that it will give you following details in the message windows.

Table ‘Product’. Scan count 2, logical reads 30, physical reads 1, read-ahead reads 17, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable‘. Scan count 1, logical reads 8110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You will notice in the message there is a table called worktable. However, if you see the original table there is no worktable. This is where the question has originated.

The question in another word is, "Why does statistics show the worktable where it is not there in the original query?"

Well, the answer is very simple, quite often SQL Server has to perform any logical operations for any specific queries and to perform these logical operations SQL Server has to build a worktable. Worktables are built in tempdb and are dropped automatically when they are no longer needed.

Worktables:

The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Worktables are built in tempdb and are dropped automatically when they are no longer needed.

read less
Comments
Dislike Bookmark

Looking for MS SQL Development Training

Find best MS SQL Development Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 26/10/2017 IT Courses/MS SQL/MS SQL Development IT Courses/SQL Programming

Derived Tables

CodePicks

I am proficient in MS SQL Development. With over 6 years of experience in SQL Development, I have trained...

Following are the signs those qualify a query to be a derived table: These are defined in FROM clause of an outer query. It is surrounded by parenthesis followed by AS clause to define the derived table name. A derived table is an example of a Sub Query that is used in the FROM clause of a SELECT... read more

Following are the signs those qualify a query to be a derived table:

  1. These are defined in FROM clause of an outer query.
  2.  It is surrounded by parenthesis followed by AS clause to define the derived table name.

A derived table is an example of a Sub Query that is used in the FROM clause of a SELECT statement to retrieve a set of records. A derived table is a virtual table that is created within the scope of a query.

Case Scenario:

Create a table for trainers:

CREATE TABLE CodePicksTrainer(
ID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
JoiningDate DATE
)

Insert trainer details:

INSERT INTO CodePicksTrainer(FirstName, LastName, DateOfBirth, JoiningDate)
SELECT 'Vivek', 'Grover', '1989-09-01', '2017-10-01'
UNION ALL
SELECT 'Ujjwal', 'Grover', '1994-11-27', '2017-09-10'
UNION ALL
SELECT 'Sahil', 'Arora', '1988-09-28', '2016-05-01'

Create a table for batches taken by trainers:

CREATE TABLE CodePickBatches(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
StartDate DATE,
TrainerID INT FOREIGN KEY REFERENCES CodePicksTrainer(ID)
)

Insert Batches Detail:

INSERT INTO CodePickBatches (Name, StartDate, TrainerID)
SELECT 'SQL Programming', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Vivek' AND LastName = 'Grover')
UNION ALL
SELECT 'Java Programming', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Vivek' AND LastName = 'Grover')
UNION ALL
SELECT 'Corel Draw', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Ujjwal' AND LastName = 'Grover')
UNION ALL
SELECT 'ASP.Net', '2016-06-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')
UNION ALL
SELECT 'SQL Programming', '2016-07-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')
UNION ALL
SELECT 'R Programming', '2017-04-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')

Requirement – One of your client demands a report with detail in following format containing Trainer Name, Joining date, Date of Birth, Total number of batches being run by trainer.

Code Build: Our result relies firstly on CodePickBatches table from where we can do aggregation to find number of batches being run my each Trainer. Once, we find the number of batches being run my each trainer, then we can make a join with CodePickTrainer table to find the detail of each trainer as shown in Requirement section. So, it requires the use of a derived table or on the fly table having aggregated data.

 SELECT
T.LastName + ', '+ T.FirstName AS TrainerName,
T.DateOfBirth,
T.JoiningDate,
TS.TotalRunningBatches
FROM (
SELECT
COUNT(1) AS TotalRunningBatches, B.TrainerID
FROM CodePickBatches B
GROUP BY B.TrainerID
) AS TS
INNER JOIN CodePicksTrainer T ON T.ID = TS.TrainerID
ORDER BY TotalRunningBatches DESC

In above code, a derived table named TS is created which makes its join with CodePicksTrainer table to find expected data. Total running batches are calculated in derived table (TS) itself. (It is surrounded by round brackets and followed by AS clause).

read less
Comments
Dislike Bookmark

Lesson Posted on 26/08/2017 IT Courses/MS SQL/MS SQL Development IT Courses/MS SQL/MS SQL Certification IT Courses/MS SQL/MS SQL General +1 IT Courses/MS SQL less

Understanding Indexes In SQL Server

Redbush Technologies Pvt.Ltd

At RedBush technologies,we specialize in providing training on Hadoop, Big Data, SQL DBA, SQL Developer,...

Indexes in SQL Server are created on columns in tables or views. The index provides a faster way to look up data based on the values in those columns.For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds... read more

Indexes in SQL Server are created on columns in tables or views. The index provides a faster way to look up data based on the values in those columns.

For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom

When a query is issued against an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the one above. The query engine continues down through the index nodes until it reaches the leaf node.

For example, if you’re searching for the value 123 in an indexed column, the query engine would first look at the root level to determine which page to reference in the top intermediate level. In this example, the first page points the values 1-100, and the second page, the values 101-200, so the query engine would go to the second page on that level. The query engine would then determine that it must go to the third page at the next intermediate level. From there, the query engine would navigate to the leaf node for
value 123. The leaf node will contain either the entire row of data or a pointer to that row, depending on whether the index is clustered or nonclustered.

1. Indexes in SQL Server 2012:

Clustered
Non-clustered
Covering
Filtered
Columnstore

i. Clustered Indexes:

A clustered index can be compared to a dictionary, where data is stored in a sorted form. If the data is in sorted form, then you can search for any word very quickly. So essentially A clustered index is table itself in a sorted order based on some column(s).

A clustered index stores the actual data rows at the leaf level of the index.An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view.A table that has no clustered index is referred to as a heap.

ii. Nonclustered Indexes:

You can compare nonclustered index with the index pages given at the end of each book. They actually don't store the data but point you to the place where the actual data is. You can have multiple non clustered indexes on a table. And of course, the more Non clustered indexes you create on a table, the more storage they take.
Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only pointers to the actual data rows, rather than contain the data rows themselves. A row locator’s structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

iii. Covering Index:

A non-clustered index that contains all the information needed to satisfy a query is known as a covering index.They enable the database administrator to add information to the non-clustered index data pages and avoid having to look up the row in the clustered index.
An index can contain more than one column, as long as the index doesn’t exceed the 900-byte limit in a clustered index key and 1700 bytes for nonclustered index key (In SQL 2016).
1. Example showing 900 bytes limit
Create table IndexLimit(Empid int, EmpDesc varchar(1000))
Create clustered index idx on IndexLimit(Empid,EmpDesc)
2. Insert Fails
Insert into IndexLimit values(12,replicate('a',1000))
3. Insert Succeeds
Insert into IndexLimit values(12,replicate('a',896))

As beneficial as indexes can be, they must be designed carefully. Because they can take up significant disk space, you don’t want to implement more indexes than necessary. In addition, indexes are automatically updated when the data rows themselves are updated, which can lead to additional overhead and can affect performance.

You should consider the following guidelines when planning your indexing strategy:

For tables that are heavily updated, use as few columns as possible in the index, and don’t over-index the tables.

If a table contains a lot of data but data modifications are low, use as many indexes as necessary to improve query performance. However, use indexes judiciously on small tables because the query engine might take longer to navigate the index than to perform a table scan.
For clustered indexes, try to keep the length of the indexed columns as short as possible. Ideally, try to implement your clustered indexes on unique columns that do not permit null values. This is why the primary key is often used for the table’s clustered index, although query considerations should also be taken into account when determining which columns should participate in the clustered index.

The uniqueness of values in a column affects index performance. In general, the more duplicate values you have in a column, the more poorly the index performs. On the other hand, the more unique each value, the better the performance. When possible, implement unique indexes.

For composite indexes, take into consideration the order of the columns in the index definition. Columns that will be used in comparison expressions in the WHERE clause (such as WHERE FirstName = ‘Charlie’) should be listed first. Subsequent columns should be listed based on the uniqueness of their values, with the most unique listed first.

read less
Comments
Dislike Bookmark

Lesson Posted on 31/07/2017 IT Courses/MS SQL IT Courses/MS SQL/MS SQL Development Tuition/BCA Tuition +4 IT Courses Tuition/BCA Tuition/IT IT Courses/MS SQL/MS SQL Integration IT Courses/MS SQL/MS SQL Reporting less

Rename The Logical Name Of SQL Server Database Files Using T-SQL

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

Rename the logical name of SQL Server database files using T-SQL: 1. Problem: SQL Server database files have two names: one is the logical file name and the other is the physical file name. The logical file name is used with some T-SQL commands, such as DBCC SHRINKFILE. If you want to change the logical... read more

Rename the logical name of SQL Server database files using T-SQL:

1. Problem:

SQL Server database files have two names: one is the logical file name and the other is the physical file name. The logical file name is used with some T-SQL commands, such as DBCC SHRINKFILE. If you want to change the logical file names of your database files then this tip will be very useful. This way you can keep your logical database file names unique on your SQL Server instance.

Since you may want to move this database on a production server, or change the database name because of any reason, you may also want to change the names of the database files. You may want the database names consistent with the database names or ant other rules you obey for standardization.

2. Solution:

It is not mandatory to keep logical file names unique on a SQL Server instance, but ideally we should keep them unique to avoid any confusion.

When we restore a database, the logical database file names don't change and are the same as the source database. So if you are restoring to multiple databases with the same backup file, the logical file names will be the same for all of the restored databases.

Let's start with creating a database having three data files and a log file then we will change the logical file name of these database files to show the step by step method.

Rename the logical SQL Server database file name using T-SQL

Step 1: First we will create a database named "Test_DB" with 1 data files and 5 log files and one log file by running the below T-SQL code. You can change the name of the database, file path, file names, size and file growth according to your needs.

USE MASTER;

GO

CREATE DATABASE Test_DB

ON

( NAME = Test_DB,

FILENAME = 'E:\Data\Test_DB.mdf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB )

LOG ON

( NAME = Test_DB_log1,

FILENAME = 'E:\Data\Test_DB_log1.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log2,

FILENAME = 'E:\Data\Test_DB_log2.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log3,

FILENAME = 'E:\Data\Test_DB_log3.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log4,

FILENAME = 'E:\Data\Test_DB_log4.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log5,

FILENAME = 'E:\Data\Test_DB_log5.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ) ;

GO

Step 2: Next I created a backup of the database.

BACKUP DATABASE Test_DB TO DISK = 'D:\Data\Backup\Test_DB.bak'

GO

I ran the below T-SQL command to get the logical file names of database file for the database TEST_DB.

USE Test_DB

GO

SELECT file_id, name as [logical_file_name],physical_name

from sys.database_files

file_id

logical_file_name

physical_name

1.

Test_DB

E:\Data\Test_DB.mdf

2.

Test_DB_log1

E:\Data\Test_DB_log1.ldf

3.

Test_DB_log2

E:\Data\Test_DB_log2.ldf

4.

Test_DB_log3

E:\Data\Test_DB_log3.ldf

5.

Test_DB_log4

E:\Data\Test_DB_log4.ldf

6.

Test_DB_log5

E:\Data\Test_DB_log5.ldf

Step 3: Now we will change the logical file name for the log data files for database "Test_DB" from "Test_DB_log" to "Test_DB_New2_log.ldf" by running an ALTER DATABASE statement using MODIFY FILE according to their file_id. To modify the logical name of a data file or log file, we will specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. Run the below command to create a procedure apply this change.

CREATE PROCEDURE Rename_Logical_Name

(

@Database sysname, @New_name sysname

)

AS

BEGIN

DECLARE @cmd1 nvarchar(200)

DECLARE @cmd2 nvarchar(200)

DECLARE @ldffile sysname

DECLARE @id sysname

DECLARE theCursor CURSOR

FOR (SELECT name,FILE_ID FROM sys.database_files WHERE type = 1)

OPEN theCursor

FETCH NEXT FROM theCursor INTO @ldffile,@id

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd2 = 'ALTER DATABASE ' +@Database+' MODIFY FILE (NAME= '''+ @ldffile + ''', NEWNAME = '''+@ New_name +@ID+'_log.ldf'')'

EXEC (@cmd2)

FETCH NEXT FROM theCursor INTO @ldffile,@id

END

CLOSE theCursor

DEALLOCATE theCursor

--PRINT @cmd1

--PRINT @cmd2

--EXEC (@cmd1)

EXEC (@cmd2);

END

EXEC Rename_Logical_Name @Database = 'Test_DB',@New_name = 'Test_DB_New'

Step 4: Now we will check the logical file names to verify our change we made in the last step. Run the command below to check the logical names.

SELECT file_id, name as [logical_file_name],physical_name

from sys.database_files WHERE type = 1

We can see the logical file names has been changed without any downtime of the database.

file_id

logical_file_name

physical_name

2.

Test_DB_New2_log.ldf

E:\Data\Test_DB_log1.ldf

3.

Test_DB_New3_log.ldf

E:\Data\Test_DB_log2.ldf

4.

Test_DB_New4_log.ldf

E:\Data\Test_DB_log3.ldf

5.

Test_DB_New5_log.ldf

E:\Data\Test_DB_log4.ldf

6.

Test_DB_New6_log.ldf

E:\Data\Test_DB_log5.ldf

 

NOTE: Do not change logical names of any data file of your production database until it is required or needed.

read less
Comments
Dislike Bookmark

Looking for MS SQL Development Training

Find best MS SQL Development Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 18/07/2017 IT Courses/MS SQL Tuition/BA Tuition/Database Management Systems IT Courses/MS SQL/MS SQL Development +1 IT Courses less

SQL SERVER: How To Pass Parameters To The Stored Procedure?

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

SQL SERVER: How To Pass Parameters To The Stored Procedure? After so many years of existence of the stored procedures, I still see developers struggling to execute the stored procedure. The most common mistakes developers do is to execute stored procedures as follows which generates the error. EXEC... read more

SQL SERVER: How To Pass Parameters To The Stored Procedure?

After so many years of existence of the stored procedures, I still see developers struggling to execute the stored procedure.
The most common mistakes developers do is to execute stored procedures as follows which generates the error.

EXEC NameofSP (FirstParam,SecondParam)

I can clearly understand why the above script preferred by developers. As most of the programming languages use the above syntax for calling a function, the developers often think it is the way to call stored procedures. However, if you try to call an SP with above syntax, it will give you an error.

There are two different methods to call stored procedures. Let us see them here, however before we do that, let us first create a sample SP which accepts two parameters.

Creating Stored Procedure

-- Create Stored Procedure

create PROCEDURE [dbo].[ShippedOrder] @salesid VARCHAR(50),@custid VARCHAR(50)
AS
SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]
FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b
ON a.[SalesOrderID] = b.[SalesOrderID]
INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID and a.[SalesOrderID]=@salesid and a.CustomerID=@custid
GO
Now let us see two different methods to call SP.

Method 1:
Let us run following statements, where the order of the parameters is a different order.

-- Execute SP
EXEC [AdventureWorksLT2012].[dbo].ShippedOrder 71780,30113
GO
EXEC [AdventureWorksLT2012].[dbo].ShippedOrder 30113,71780
GO

When you run above script, it will show us result where the order of the columns is changed as SP assigns the first params to the first parameters inside.

However, if you want to assign your passed parameters to specific variable inside SQL Server, you will have to mention that parameter as mentioned in the second method.In the first statement it will show the result but in second statement no output will come.

Method 2:

In this method when we execute the stored procedure, we also include the variable to which we want to assign parameter. This way is the certain way to assign value to parameter inside a SP.

-- Execute SP
EXEC AdventureWorksLT2012.dbo.ShippedOrder @salesid = 71780, @custid = 30113
GO
EXEC AdventureWorksLT2012.dbo.ShippedOrder @custid = 30113,@salesid = 71780
GO

Now when you execute this SP, you will get results in the same order in both the case as we have specified the parameters to assign when we passed variable values.

read less
Comments
Dislike Bookmark

Lesson Posted on 30/05/2017 IT Courses/MS SQL/MS SQL Development Tuition/BA Tuition/Database Management Systems IT Courses/MS SQL

How To Insert The Result Sets Of A Stored Procedure/Functions Into A Temporary Table In SQL Server?

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

How to Insert the Result sets of a Stored Procedure/Functions into a Temporary Table in SQL Server In some cases with SQL Server, there may be an instance where you wish to take the resulting data from a stored procedure and insert it into a temporary table for use in another query. Determining how... read more

How to Insert the Result sets of a Stored Procedure/Functions into a Temporary Table in SQL Server

In some cases with SQL Server, there may be an instance where you wish to take the resulting data from a stored procedure and insert it into a temporary table for use in another query. Determining how to accomplish this task can be somewhat difficult, so we’ll briefly outline a couple options, depending on your specific needs and database configuration.

Using the Openrowset Statement:

One possibility is to use the openrowset statement, which allows you to access remote data from an OLE DB source and can be executed directly from within another SQL statement. Openrowset is a one - time connection and data retrieval method, so it should not be utilized for frequent connections (linking servers is preferable in that case).

Openrowset can be the target of any Insert, Delete or Update statement, which makes it ideal for our purposes of “executing” our stored procedure for us and extracting that data back out to our waiting temporary table. Also supports bulk operations through a built-in bulk provider that enables data from a file to be read and returned as a rowset.

Before using openrowset, it may be necessary to modify some configuration options, specifically by allowing ad hoc access. This can be configured using the following statements:

Now we can utilize Openrowset, which has a particular syntax that must be adhered to:

OPENROWSET (

  ,

  ,

 

)

Step 1: Enable Ad Hoc Distributed Queries:

sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

Step 2: Insert Results of Stored Procedure/Functions into a Temporary Table:

-- Create a Stored Procedure using AdventureWorksLT2012 database

CREATE PROCEDURE GetShippedOrder

AS

SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]

    FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b

        ON a.[SalesOrderID] = b.[SalesOrderID]

        INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID

GO

-- Execute Stored Procedure

EXEC GetShippedOrder

GO

-- Create a Inline Function

CREATE FUNCTION GetShippedOrders()

RETURNS TABLE

AS

RETURN SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]

    FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b

        ON a.[SalesOrderID] = b.[SalesOrderID]

        INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID

    WHERE a.[ShipDate] IS NULL

GO

-- Run the Function

select * from  GetShippedOrders()

Step 3: Insert into Temp Table:

-- Insert into Temp Table from Stored Procedure

SELECT *

INTO #TempTable

FROM OPENROWSET('SQLNCLI', 'Server=Amitava-PC;Trusted_Connection=yes;','EXEC [AdventureWorksLT2012].[dbo].GetShippedOrder')

GO

 -- Select Data from Temp Table

SELECT *

FROM #TempTable

GO

-- Insert into Temp Table from Inline Function

SELECT *

INTO #TempTable

FROM OPENROWSET('SQLNCLI','Server=AMITAVA-PC;Trusted_Connection=yes;','select * from  [AdventureWorksLT2012].[dbo]. GetShippedOrders()')

GO

-- Select Data from Temp Table

SELECT *

FROM #TempTable

GO.

 

 

read less
Comments
Dislike Bookmark

Answered on 27/06/2017 IT Courses/MS SQL/MS SQL Development

Vinod Gummadi

Oracle Fusion Trainer

we will providing training . We do have trainers for SQL server and all.
Answers 3 Comments 1
Dislike Bookmark

Looking for MS SQL Development Training

Find best MS SQL Development Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 25/05/2017 IT Courses/MS SQL/MS SQL Development IT Courses/MS SQL/MS SQL Certification IT Courses/MS SQL +2 Tuition/BTech Tuition Tuition/BTech Tuition/Database Management Systems less

SQL Server Row Count for all Tables in a Database

Amitava Majumder

I am an experienced Trainer and IT professional with over 10 years of experience in IT Sector and more...

ProblemI am a database consultant and one of my tasks involves getting the row counts from all the tables in the source database and comparing it against the corresponding table row counts in the target database. How do I get the row counts from all the tables in a SQL Server Database? What are the different... read more

Problem
I am a database consultant and one of my tasks involves getting the row counts from all the tables in the source database and comparing it against the corresponding table row counts in the target database. How do I get the row counts from all the tables in a SQL Server Database? What are the different approaches to get this information?

Solution
It is a common step in any ETL project to validate the row counts between source and target databases
as part of the testing phase. Getting the row count from each table one by one and comparing and consolidating the results can be a tedious task. Hence any script/solution which can get the row count information from all the tables in a database can be really helpful and effective thereby considerably reducing the effort involved. In this tip we will see four different approaches to get the row counts from all the tables in a SQL Server database

How to Count Rows of Every Table in Database
I try to implement some of the faster ways for counting row number rather than the COUNT() that I'm using right now.I am not able to get the result I'm expecting.

I have table Sales.[Customer] and I take the number of the records inside the table simply by:

SELECT COUNT(*) as Row_count FROM Sales.[Customer]
And the result is:

Row_count
19820

Now I'm trying to count the records from the same table getting use of the sys.partitions

SELECT SCHEMA_NAME(schema_id) AS [Schema_Name],
[Tables].name AS [Table_Name],
SUM([Partitions].[rows]) AS [Total_Rowcount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
GROUP BY SCHEMA_NAME(schema_id), [Tables].name;

sys.partitions Contains a row for each partition of all the tables and most types of indexes in the database.Special index types such as Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.
index_id Indicates the ID of the index within the object to which this partition belongs.

  • 0 = heap
  • 1 = clustered index
  • 2 or greater = nonclustered index

For a single table use WHERE [Tables].name = N'name of the table'

Schema_Name Table_Name                  Total_Rowcount
Person               Address                       19614
Person               AddressType                 6
Production         BillOfMaterials               2679
Person               BusinessEntity              20777
Person               BusinessEntityAddress   19614
Person               BusinessEntityContact    909
Person               ContactType                  20
Person               CountryRegion               238
Sales                 CountryRegionCurrency  109
Sales                 Customer                     19820

 

read less
Comments
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best MS SQL Development Training in India. Post Your Requirement today and get connected.

Overview

Lessons 22

Total Shares  

+ Follow 3,000 Followers

Top Contributors

Connect with Expert Tutors & Institutes for MS SQL Development

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more