SQL Server on-premises BULK INSERT is a very popular method to import data from a local SQL file to SQL Server. The BULK INSERT feature also could be used to import data from a file stored in an Azure storage account to SQL Server on-premises. Because Azure is growing each day and SQL Server is improving the features to connect SQL Server on-premises, BULK INSERT could be a powerful tool to import data because it is fast and it can be easily combined with T-SQL code. In this article, I will demonstrate with examples how developers can use the traditional BULK INSERT statement to load data from a local CSV file or Azure blob to Azure SQL DB.
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Thursday, April 04, 2019
Wednesday, December 26, 2018
Connecting to an Azure SQL Database and Query Data from VS Code
Azure Service provides SQL relational database-as-a-service with high performance, reliability, and security. Developers can write C# code to connect to an Azure SQL database by using the .NET code framework from Visual Studio or Visual Studio Code editor. As a prerequisite, you need to install the latest version of Visual Studio or Visual Studio Code and you should have created the server level firewall rule in Azure to access the database from your development environment.
Monday, April 09, 2018
Create SQL Server Database Unit Tests Using Visual Studio
Writing database unit test cases are complimentary to the software development life cycle created by software developers. A developer can write unit tests cases to evaluate the success or failure of database design changes and check the results of stored procedures and functions. Unit test cases can ensure any changes applied on the database are as expected and do not introduce new errors. Unit tests, in addition, serve as documentation for users of the methods under test. Developers quickly can review unit tests to determine exactly how a particular database object should be consumed. By developing database unit tests, developers can create a collection of tests and run them during development to make sure all features work as expected.
Friday, December 09, 2011
Working with Dundas Charts in SQL Server Reporting Service
Dundas data visualization provides third party chart controls for developers used for creating embedded chart based RDL reports with SQL server reporting service. Dundas also provides gauge, map and other visual controls for different Microsoft development platforms (ASP.NET, Windows Forms, SQL Reporting Services and SharePoint). Dundas charts for reporting services can be very easily incorporate into RDL reports and easy to use. These chart control offers more features, additional graph types and more reporting abilities. For more details read my article in Codeguru.
http://www.codeguru.com/vb/article.php/c17821/
Tuesday, April 12, 2011
Improving query performance SQL server
Lead Paragraph
This article provides best practices and guidelines for TSQL developers for improvement of the performance of SQL server database.
Introduction
Writing TSQL queries has become extremely easy for developers and that can be easily learned by anyone. But TSQL simplicity has also makes it easy to write poorly performing unstructured queries. In this article I have mentioned few important tips and guidelines for TSQL developers for improving the query performance for SQL server database.
Parameterized Queries
When you are executing series of TSQL queries from frontend application, use parameterized queries for better performance. Parameterized queries gives better performance by compiling the query once and executing the same compiled query multiple times. But you should also use the same command object to cache the query plan. Following is an example of parameterized query to return student information based on student roll number.
SELECT Student_Name, Class, Subjects FROM Students WHERE Roll_No =?
Creating Necessary Indexes
Create useful indexes to improve TSQL query performance. Before creating indexes, you must know how the data is used, the types of queries you would like to run on top of SQL server data so that the query processor can use indexes to find your data quickly. Index creation rules are simple and straight forward, if your queries are mostly SELECT statements; more indexes can be helpful for faster execution. But DML (INSERT, UPDATE, and DELETE) operations will get slow down if you have more indexes. Also try to avoid creating indexes on very small tables with just a few data pages. This saves the cost of loading and processing index pages.
Create indexes on primary keys and frequently used foreign keys because primary keys and foreign keys are mostly used in join conditions. If your query joins tables by using other columns, create indexes on those columns for the same reason.
To improve performance you can create Indexes on columns used in the WHERE clause of your TSQL statement. Performance of query depends on the selectivity of index. Selectivity is the ratio of qualifying rows to total rows. If the ratio is low, the index is highly selective. An index that is not selective is not as useful. All unique indexes are selective. You can evaluate the selectivity of an index by running the sp_show_statistics stored procedures on SQL Server.
Multiple-column indexes are useful for faster executing of filter expressions that match a prefix set of key columns. For example, the index CREATE INDEX Idx_Student_Name ON Student ("Last Name" ASC, "First Name" ASC) helps faster execution of the following queries:
... WHERE "Last Name" = 'Pal'
... WHERE "Last Name" = 'Pal' AND "First Name" = 'Tapas'
... WHERE "First Name" = 'Tani' AND "Last Name" = 'Paul'
Replace Subqueries With Joins
Sometimes JOIN gives better performance compared to subquery. Using JOIN you can manually control the order that data is selected. You can also decrease query execution time by forcing SQL server to pre-filter data in the table. The following query displays student details with mark obtained in different subjects.
SELECT stu.Student_Name, stu.Class, stu.Subject, stu.Marks,
(SELECT Markset.PassMark
FROM dbo.[Marks Details] AS Markset
WHERE stu.Subject= Markset.Subject) AS PassMark
FROM Students AS stu
The above nested query can be replaced by following INNER JOIN query for better performance.
SELECT stu.Student_Name, stu.Class, stu.Subject, stu.Marks,
Markset.PassMark
FROM Students AS stu
INNER JOIN dbo.[Marks Details] as Markset
On stu.Subject= Markset.Subject
Using Temporary Tables
SQL Server DBAs and TSQL developer’s loves to write SELECT INTO statement for data transfer from system tables, like this:
SELECT *
INTO #MyTempTable
FROM sysobjects
But the above query will create table locks when dealing with large number of records. As a result, other queries and procedures those need to use sysobjects object will have to wait until long-running query execution is complete. You can avoid system table locking by manually creating the temporary table with the CREATE TABLE command and load the data afterwards with another SQL query.
For example…
CREATE TABLE #MyTempTable
(All the Necessary Columns)
INSERT INTO # MyTempTable
SELECT *
FROM sys.objects
The above query will have same number of locks as the first one; but the time of the table lock period will be shorter for the second one compared to the previous query. This will allow other processes and objects to use system table.
Typically, when developing SQL code the development server has only a single user or few users. When working on SQL code, it's important to know when the code will impact sessions other than the current session. And unexpected interaction can cause major performance issues.
Stored Procedure Performance Improvement
Most of the time database developers follow standard naming convention while creating database objects. Like, while creating a stored procedure we start with prefix sp_. But this naming convention is not correct. All the system stored procedures begins with sp_. So when you write a custom stored procedure with prefix sp_ SQL server execution engine first searches definition of the stored procedure in master database before looking into the database it is called in. This may result slow performance.
Use the SET NOCOUNT ON statement on top of stored procedures to prevent SQL server from sending the message to the caller for each statement in a stored procedure. Each message contains the number of affected rows for the respective statement.
Conclusion
If you think a query is taking too much time to execute and not giving you expected performance use SQL server profiler for creating trace and viewing performance of your query in live environment. SQL developers usually can obtain best performance of TSQL queries by following correct development techniques.
This article provides best practices and guidelines for TSQL developers for improvement of the performance of SQL server database.
Introduction
Writing TSQL queries has become extremely easy for developers and that can be easily learned by anyone. But TSQL simplicity has also makes it easy to write poorly performing unstructured queries. In this article I have mentioned few important tips and guidelines for TSQL developers for improving the query performance for SQL server database.
Parameterized Queries
When you are executing series of TSQL queries from frontend application, use parameterized queries for better performance. Parameterized queries gives better performance by compiling the query once and executing the same compiled query multiple times. But you should also use the same command object to cache the query plan. Following is an example of parameterized query to return student information based on student roll number.
SELECT Student_Name, Class, Subjects FROM Students WHERE Roll_No =?
Creating Necessary Indexes
Create useful indexes to improve TSQL query performance. Before creating indexes, you must know how the data is used, the types of queries you would like to run on top of SQL server data so that the query processor can use indexes to find your data quickly. Index creation rules are simple and straight forward, if your queries are mostly SELECT statements; more indexes can be helpful for faster execution. But DML (INSERT, UPDATE, and DELETE) operations will get slow down if you have more indexes. Also try to avoid creating indexes on very small tables with just a few data pages. This saves the cost of loading and processing index pages.
Create indexes on primary keys and frequently used foreign keys because primary keys and foreign keys are mostly used in join conditions. If your query joins tables by using other columns, create indexes on those columns for the same reason.
To improve performance you can create Indexes on columns used in the WHERE clause of your TSQL statement. Performance of query depends on the selectivity of index. Selectivity is the ratio of qualifying rows to total rows. If the ratio is low, the index is highly selective. An index that is not selective is not as useful. All unique indexes are selective. You can evaluate the selectivity of an index by running the sp_show_statistics stored procedures on SQL Server.
Multiple-column indexes are useful for faster executing of filter expressions that match a prefix set of key columns. For example, the index CREATE INDEX Idx_Student_Name ON Student ("Last Name" ASC, "First Name" ASC) helps faster execution of the following queries:
... WHERE "Last Name" = 'Pal'
... WHERE "Last Name" = 'Pal' AND "First Name" = 'Tapas'
... WHERE "First Name" = 'Tani' AND "Last Name" = 'Paul'
Replace Subqueries With Joins
Sometimes JOIN gives better performance compared to subquery. Using JOIN you can manually control the order that data is selected. You can also decrease query execution time by forcing SQL server to pre-filter data in the table. The following query displays student details with mark obtained in different subjects.
SELECT stu.Student_Name, stu.Class, stu.Subject, stu.Marks,
(SELECT Markset.PassMark
FROM dbo.[Marks Details] AS Markset
WHERE stu.Subject= Markset.Subject) AS PassMark
FROM Students AS stu
The above nested query can be replaced by following INNER JOIN query for better performance.
SELECT stu.Student_Name, stu.Class, stu.Subject, stu.Marks,
Markset.PassMark
FROM Students AS stu
INNER JOIN dbo.[Marks Details] as Markset
On stu.Subject= Markset.Subject
Using Temporary Tables
SQL Server DBAs and TSQL developer’s loves to write SELECT INTO statement for data transfer from system tables, like this:
SELECT *
INTO #MyTempTable
FROM sysobjects
But the above query will create table locks when dealing with large number of records. As a result, other queries and procedures those need to use sysobjects object will have to wait until long-running query execution is complete. You can avoid system table locking by manually creating the temporary table with the CREATE TABLE command and load the data afterwards with another SQL query.
For example…
CREATE TABLE #MyTempTable
(All the Necessary Columns)
INSERT INTO # MyTempTable
SELECT *
FROM sys.objects
The above query will have same number of locks as the first one; but the time of the table lock period will be shorter for the second one compared to the previous query. This will allow other processes and objects to use system table.
Typically, when developing SQL code the development server has only a single user or few users. When working on SQL code, it's important to know when the code will impact sessions other than the current session. And unexpected interaction can cause major performance issues.
Stored Procedure Performance Improvement
Most of the time database developers follow standard naming convention while creating database objects. Like, while creating a stored procedure we start with prefix sp_. But this naming convention is not correct. All the system stored procedures begins with sp_. So when you write a custom stored procedure with prefix sp_ SQL server execution engine first searches definition of the stored procedure in master database before looking into the database it is called in. This may result slow performance.
Use the SET NOCOUNT ON statement on top of stored procedures to prevent SQL server from sending the message to the caller for each statement in a stored procedure. Each message contains the number of affected rows for the respective statement.
Conclusion
If you think a query is taking too much time to execute and not giving you expected performance use SQL server profiler for creating trace and viewing performance of your query in live environment. SQL developers usually can obtain best performance of TSQL queries by following correct development techniques.
Sunday, August 29, 2010
Top 10 SQL Server 2008 Security Features
Protecting database information from threats and vulnerabilities is vital for any organization. This article discusses 10 new security features introduced in SQL server 2008 that facilitate effective management of the database by using server surface area configuration (policy-based management configuration), full database encryption (TDE), enhanced auditing features and more.
Read full article..
http://www.databasejournal.com/features/mssql/article.phpr/3899866/article.htm
Read full article..
http://www.databasejournal.com/features/mssql/article.phpr/3899866/article.htm
Sunday, August 08, 2010
Top 10 SQL Server 2008 Development Features
Many new developer features were introduced in SQL Server 2008 database to facilitate robust database development. SQL server 2008 improves developer productivity by providing seamless integration between frameworks, data connectivity technologies, programming languages, Web services, development tools and data. This article discusses the new top 10 developer features introduced in SQL server 2008. Read my full article.
http://www.databasejournal.com/features/mssql/article.php/3897251/article.htm
http://www.databasejournal.com/features/mssql/article.php/3897251/article.htm
Friday, August 06, 2010
Developing Reports for ASP.NET Web applications Using SQL Server 2008 Reporting Services
Crystal Report was the most recommended reporting tool used with ASP.NET web application before SQL server reporting service (SSRS) was first introduced in 2004 as an add-on to SQL server 2000 by Microsoft. An enhanced version of this SQL server reporting service was released with SQL server 2005 and the latest version of SSRS is launched with SQL Server 2008. A SSRS report is an XML file with a .RDL extension (Report Definition Language). After installation of the business intelligent development studio (BIDS) tool , developers can create a RDL report file using Microsoft Visual Studio editor. SSRS also provides a web service (server) interface for custom reporting applications. Read my full article in codeguru
http://www.codeguru.com/csharp/.net/article.php/c17661
http://www.codeguru.com/csharp/.net/article.php/c17661
Friday, May 21, 2010
Transparent Data Encryption (TDE) in SQL Server 2008
As part of the SOX (Sarbanes-Oxley Act) regulatory compliance implementation, one of my banking customers asked me to encrypt their database for an already existing application. It was a business critical web application developed using Visual Studio 2005 (ASP.NET 2.0 framework) and SQL Server 2000 as the backend database. Like any other standard software development project, the delivery schedule for this was also very tight and the client requested that I implement database encryption as quickly as possible, with minimal impact on the existing application. I completed that assignment on time and within budget using Transparent Data Encryption (TDE), a new full database encryption technique introduced in SQL Server 2008. In this article, I will demonstrate the implementation of TDE.Read the full article in Databasejournal.com.
http://www.databasejournal.com/features/mssql/article.phpr/3883391/article.htm
http://www.databasejournal.com/features/mssql/article.phpr/3883391/article.htm
Sunday, May 02, 2010
Free Microsoft Azure SQL Tools For Cloud Application Development
The relation database (RDBMS) provided in Microsoft Azure is known as SQL Azure. The SQL Azure database can be easily integrated with your local SQL Server and tools provided in Microsoft Visual Studio 2008 and 2010. Azure Developers can use T-SQL script for queryring data as they presently do for any on-premises SQL database. It's a highly available and scalable service can be obtained by registering to SQL.Azure.com site. Microsoft Azure doesn't provide any Off-premises SQL Azure development tools or Management studio for developers. You need to develop a local database and migrate it to SQL Azure durin production deployment. To know more check my article..
http://www.codeguru.com/vb/mobile/article.php/c17147/Free-Microsoft-Azure-SQL-Tools-For-Cloud-Application-Development.htm
http://www.codeguru.com/vb/mobile/article.php/c17147/Free-Microsoft-Azure-SQL-Tools-For-Cloud-Application-Development.htm
Subscribe to:
Posts (Atom)