The problem is: how to get one million numbers to table with less time? We can solve this problem using different approaches but not all of them are quick. Let’s go now step by step and see how different approaches perform.
Using WHILE
First idea for many guys is using WHILE. It is robust and primitive approach … Read More →
SUMMARY : In this mini-tutorial, you will learn how to query data from multiple tables using T-SQL JOINS statement.
A SQL JOIN combines records from two tables.A JOIN locates related column values in the two tables.A query can contain zero, one, or multiple JOIN operations.INNER JOIN is the same as JOIN; the keyword INNER … Read More →
Gaps and islands problems involve missing values in a sequence. Solving the gaps problem requires finding the ranges of missing values, whereas solving the islands problem involves finding the ranges of existing values. The sequences of values in gaps and islands problems can be numeric, such as a sequence of order IDs, some of which … Read More →
The Query’s goal is :
Find the top 5 Locations
List the users who live in those top 5 Locations, alphabetized by their DisplayName
There are a LOT of ways I could write this query, but for the purpose of this post, I’m only going to contrast common table expressions (CTEs) versus temp tables:
We’re talking about a relatively … Read More →
Introduction
In this article (Italian), we have already discussed Continuous Integration and DLM. We described the main reasons for putting a database under source control and for testing our databases. In this article, we are going to discuss a real scenario of SQL Server unit testing with the tSQLt framework.
What Is Unit Testing
In computer programming, unit … Read More →
https://sql.sh/
Apprendre le SQL
Le SQL (Structured Query Language) est un langage permettant de communiquer avec une base de données. Ce langage informatique est notamment très utilisé par les développeurs web pour communiquer avec les données d’un site web. SQL.sh recense des cours de SQL et des explications sur les principales commandes pour lire, insérer, modifier et … Read More →
DOTNET exposes several ways to achieve mutual exclusion in code such as Mutex, Lock, SynLock, Manual reset event or Thread wait etc. The same thing can be achieved even in SQL Server using application locks. It is achieved by using 2 system stored procs (sp_getapplock and sp_releaseapplock)
Permissions:
However to be able to call sp_getapplock a user … Read More →
LOCK_TIMEOUT is not only a session-level setting, it also needs to be set in a separate batch. But, it cannot be set via a variable. So, this can be accomplished by using Dynamic SQL in the "master" Stored Procedure. This will allow for setting LOCK_TIMEOUT and then executing whatever other Stored Procedure that should run … Read More →
Here are some SSIS interview questions that you can expect if you interview for the job of ETL Developer. I will be adding more questions and different small scenarios. Good luck with your SQL Server Integration Services interview.
For Video Answers for SSIS Interview Questions , Please check THIS playlist.
General
What is ETL? (Video Answer)
What is Business … Read More →
All Tips
Additional Optimizations for Type 2 Slowly Changing Dimensions in SQL Server Integration Services - Part 3
Backwards Compatibility in SQL Server Data Tools for Integration Services
Data Sampling in SQL Server Integration Services
Debugging Control Flow in SQL Server Integration Services - Part 1
Debugging Control Flow in SQL Server Integration Services – Part 2
Defining Workflow in SSIS … Read More →
Clustered and nonclustered indexes share many of the same internal structures, but they're fundamentally different in nature. Watch Microsoft Certified IT Professional Jon Seigel explain the similarities and differences of clustered and nonclustered indexes, using a real-world example to show how these structures work to improve the performance of SQL queries.
Blog post on primary key … Read More →
While the practice of Continuous Integration (CI) started with application code, you can apply the same principles to databases. Database CI is the process by which we build, test and deploy the database on commit of every database change, and report any errors. In this way, CI reduces your feedback loop such that changes that … Read More →
Resources
The page is dedicated to presentations, scripts, and other resources that are available for download.
Presentations:
SQL Server Audit - Evidence Never Lies - PowerPoint slide deck
SQL Saturday #692 - Atlanta, GA - Jul 15, 2017
SQL Saturday #610 - Richmond, VA - Mar 18, 2017
SQL Saturday #592 - Raleigh, NC - Mar 11, 2017
Policy Based Management - Beyond the Basics … Read More →
xp_dirtree has three parameters:
directory - This is the directory you pass when you call the stored procedure; for example 'D:\Backup'.
depth - This tells the stored procedure how many subfolder levels to display. The default of 0 will display all subfolders.
file - This will either display files as well as each folder. The default of 0 … Read More →
PIVOT Operator
UNPIVOT Operator
A collection of premium SSIS components to enable greater developer productivity and increase the power of SSIS.
The HTTP/Web Services components is a grouping of SSIS add-on components from the SSIS Productivity Pack which facilitate working with HTTP and SOAP or REST based web services within SSIS.
There are currently 6 SSIS components within this category:
HTTP Connection … Read More →
Scripts those can be used by SQL Server DBAs and SQL Server Developers on daily basis.
Data Analysis / Data Validation / Data Cleansing Scripts
How to Search in all Columns for all tables in a database for Date Value in SQL Server
How to Find Percentage of Null Values in every Column of all the Tables in … Read More →
If you are looking for the real time scenarios/ Examples or type of work developer perform on Job as ETL developer. You are looking at the right place. The videos explain different scenarios those can answer your so many questions and also helpful if you are going for SSIS/ETL developer interview.
Starting with SSIS
How to Create … Read More →
In the following example we will looking for the LOCAL FAST_FORWARD parameter in to the declaration of cursor code.
LOCAL
Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by … Read More →
Free SQL Server video tutorial
Connecting to SQL Server
Working with databases
Creating and working with tables
Default Constraint
Cascading referential integrity
Check Constraint
Identity Column
Retrieving identity column value
Unique key constraint
All about select statement
Group By
Basic Joins
Advanced Joins
Self join
Different ways to replace NULL
Coalesce function
Union and Union All
Stored Procedures
Stored Procedures with output parameters
Difference between output … Read More →
SQL Server APPLY operator has two variants; CROSS APPLY and OUTER APPLY
The CROSS APPLY operator returns only those rows from the left table expression (in its final output) if it matches with the right table expression. In other words, the right table expression returns rows for the left table expression match only.
The OUTER APPLY operator … Read More →