SQL Server
T-SQL from the ground up, database design, administration and tuning, SSIS, SSRS and SSAS, data warehousing and ETL, and SQL interview prep.
2023 · 2 posts
SQL Interview prep quiz 2 : Explain the difference between CTE and a subquery and give some advantages of using one over the other in OLAP vs OLTP systems
· 5 min read
In this quiz, we are asked to give some differences between a common table expression and a subquery and what are some advantages of using one over the other…
SQL interview prep quiz 1: Write sql code to print the letters of the English alphabet in upper case without explicitly writing the letters.
· 5 min read
Hi guys, I am starting a new series around sql interview prep which will have sql interview like questions and how to approach solving them. People have…
2020 · 3 posts
How to use analytic functions First_value and Last_value introduced in SQL Server 2012
· 3 min read
I want to quickly go over some useful analytic functions introduced in sql server 2012 and how you can use it to accomplish some cool tasks in your data…
How to use SQL Server extension ROLLUP to summarize data
· 2 min read
We have many functions for aggregating data in SQL such as AVG,MIN,MAX etc. Apart from these functions, there are sql server extensions to the group by clause…
Data profiling using SQL Server Integration Services - SSIS
· 7 min read
Well, this is my first post in 2020 and it better be good and helpful to you out there. Happy new year!!!. I want to discuss data profiling using SSIS in this…
2019 · 4 posts
Restore database in sql server
· 5 min read
Restoring database in sql server is one of the important tasks database administrators have to do from time to time. Restoring a database just means to bring…
Back up database in SQL Server.
· 7 min read
Backing up databases is one of the tasks perform by database administrators in many organizations. The aim is to make a copy of the database with data either…
Ping database in sql server.
· 2 min read
This is a question one of my friends asked me today at work and l decided to write it out and share with anyone who might need it. How do you ping a database…
Shrink database in sql Server.
· 5 min read
Have you been in a situation where you wanted to make a backup of a database and restore to another server and realized you couldn't backup and restore to the…
2016 · 5 posts
SQL Script to populate Date Dimension table in the Data warehouse.
· 1 min read
This is a simple script l wrote in SQL server 2012 which can be used to populate the Date table for use in a data warehouse. It provides components of the date…
ETL Process in Data Warehouse
· 9 min read
ETL Extraction Transformation Loading ETL Overview Extraction Transformation Loading – ETL To get data out of the source and load it into the data…
Simple guide to get familiarize with the concept of data warehouse
· 17 min read
What is a data warehouse? A data warehouse is a repository of an organization's electronically stored data. Data warehouses are designed to facilitate…
SQL Procedure to allow registration of only employees of your company for your site.
· 1 min read
Here is a simple procedure to register employees of your company only to a system. For example, l have created a site that l want only users with email ending…
SSRS interview questions with answers.
· 23 min read
Are you preparing to take interview on SQL Server Reporting Services or are you preparing to interview a candidate for the position? If yes to any of the about…
2015 · 4 posts
What difference does it make when you write SQL Stored Procedure with or without Begin and End?
· 3 min read
Remember those college days when your calculus professor will ask questions such as prove that cosine 90 is zero? No? Ok, take it easy, its not coming back to…
Find The End Of Month In SQL Server
· 4 min read
SQL Server 2012 has introduced a nice function EOMONTH which gives end of month for a given date. How do you know how many days are in a month in real life?…
Move Multiple Files From Multiple Folders To One Folder Using SSIS
· 3 min read
This is a real scenario l had to deal with at work today. I had about thousand folders with multiple files and l wanted to move all these files to a folder on…
Using Temp Tables In Stored Procedures To Use In SSIS 2012
· 2 min read
I came across the problem of using temp tables in stored procedures and selecting a result set from the temp table to return in SSIS 2012. I think it is very…
2014 · 1 post
2013 · 59 posts
How to add a new column as a primary key to an existing table with data
· 4 min read
Merry Christmas and Happy new year to all my visitors. Thank you all for the support and continue the good work of liking the blog on facebook and referring…
Save your self by Trimming all white spaces around your data before storing in your data warehouse
· 2 min read
This week l had to show on the fly to a colleague of mine why it is very important to always clean data coming from different sources before storing it in the…
The Danger of using NOLOCK in SQL Server queries
· 2 min read
This is a topic most of us never consider carefully when using nolock in our select queries. This week, one of the database administrators came out with a…
SQL Server Output Clause Illustration Using Texas Smart Meter Interval File
· 6 min read
I am watching the Houston Texans play Oakland while writing this post. Case Keenum, JJ Watt and the rest are all doing their best and Coach Kubiak is up here…
Create Index on a table with Millions of Records in SQL Server
· 2 min read
There are situations where you are faced to create index on a really huge table. This can be really troubling because it's no joke at all and it can cause you…
Data Integrity
· 2 min read
Integrity is all about maintaining a consistent state of your data. Here are some types of integrity that can be defined on data. Entity Integrity Entity…
Data Quality
· 5 min read
The primary goal of Data Quality solution is to assemble data from one or more data sources. However, the process of bringing data together usually results in…
Designing OLTP and benefits of Normalization
· 2 min read
What is an OLTP database? OLTP-(On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE).The…
ETL Process and Data Profiling
· 2 min read
It involved 4 Phases: Planning, Designing, Implementation and Maintenance Planning involves Understanding of business requirements and Identifying the scope of…
Optimizing Stored Procedures
· 5 min read
The process of optimizing a stored procedure is much like the process for optimizing a query, with a few differences. If a sproc was running well, but slowed…
SQL Server Query Execution Plan
· 7 min read
When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines…
SQL Server Replication
· 3 min read
Replication is a server-based tool that you can use to synchronize data between two or more databases. Replication can send data from one SQL Server instance…
SSIS Interview Questions 10
· 2 min read
How do you apply configurations to a package ? Use a package configuration to override selected property values stored in a separate file or configuration…
SSIS Interview Questions 11
· 1 min read
What is Required ? Required is where the container starts a new transaction. If an existing transaction has already been started by the parent container, the…
SSIS Interview Questions 12
· 1 min read
What are Non-Blocking Transformations ? Non-blocking Transformations are transformations that work with only individual field values for rows in the pipeline,…
SSIS Interview Questions 13
· 1 min read
What are Asynchronous Components ? The output of an asynchronous component uses a new buffer. It’s not possible to reuse the input buffer because an…
SSIS Interview Questions 14
· 3 min read
What is BIDS? Business Intelligence development studio, it's a extension of visual studio shipped with sql server in support of business intelligence tools.…
SSIS Interview Questions 15
· 8 min read
What is an event handler? It is a workflow that responds programmatically to events that occur on a package level, container level, task level or change of…
SSIS Interview Questions 2
· 2 min read
What is a Data Flow Task ? A task which enables you to extract and transform data from various sources and load them to an array of destinations such as…
SSIS Interview Questions 3
· 2 min read
What are Data Mapping Documents for? After the Data Warehouse or Datamart has been designed, to properly populate a data warehouse the source data has to be…
SSIS Interview Questions 4
· 2 min read
What are Character Map Transformations ? The Character Map transformation applies string functions, such as conversion from lowercase to upper case to…
SSIS Interview Questions 5
· 2 min read
What are Merge Join Transformations ? The Merge Join transformation will give an output by taking two inputs and performing an INNER, LEFT, or FULL OUTER JOIN…
SSIS Interview Questions 6
· 1 min read
What are the types of Control Flow Elements ? There are 3 different types of control flow elements in SSIS. There are containers, tasks, and precedence…
SSIS Interview Questions 7
· 1 min read
Can you create a Differential Backup with an existing Full Backup ? You use a differential backup along with a full backup. If a full backup does not exist,…
SSIS Interview Questions 8
· 2 min read
What is the difference between running a package in the SSIS development platform and in a production server ? The package designer and development platform…
SSIS Interview Questions 9
· 1 min read
What are the properties of Package Password Protection ? All of the security options are controlled by the protectionLevel property. With the package open in…
What is difference between DELETE & TRUNCATE commands?
· 1 min read
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a…
Hierarchies and attributes in SSAS
· 1 min read
These are notes l'm sharing for any one interested in SSAS 1. Attribute Hierarchy (All level and key level) 2. User Defined Hierarchy (Multiple Levels) By…
KPI and Actions in SSAS
· 2 min read
KPI's are quantifiable measures that represent critical success factors, and analysts use them to measure company performance over time, against a predefined…
Partitions, Aggregations and Optimizations in SSAS
· 3 min read
Partitions -They can be done only for fact tables/measure groups. -A partition stores part of the data for a measure group. -Each partition can pull its data…
Report Creation, management, deployment, administration and viewing in SSRS
· 8 min read
What is report life cycle? The report life cycle is the sequence of activities associated with a report from creation to delivery. Reporting Services fully…
SSAS Cube Processing and Implementing Security
· 2 min read
SSAS Cube processing options. 1. Process Default. Performs the minimum number of tasks required to fully initialize the object. The server converts this option…
SSAS Interview Questions 1
· 1 min read
What is SSAS ? SSAS stands for SQL Server Analysis Services. It allows us to take data from our data warehouse and analyze it for Business Intelligence. What…
SSAS Interview Questions 10
· 2 min read
The process of creating OLAP cubes from an existing structures is BOTTOM UP approach. It's the most common way of creating an OLAP Cube. We can also create a…
SSAS Interview Questions 2
· 1 min read
How can you optimize reading from a Cube ? In order to optimize reading from a cube, you physical normalize the dimensions via attribute hierarchies. It is…
SSAS Interview Questions 3
· 1 min read
What is Conditional Formatting ? Conditional Formatting is done via Calculated Members. They allow us to add custom formatting to our measures based upon…
SSAS Interview Questions 4
· 1 min read
What are the Storage Modes for Dimensions ? The storage modes for dimensions are MOLAP and ROLAP. Dimensions can’t use HOLAP because dimensions do not perform…
SSAS Interview Questions 5
· 1 min read
What is the Process Full mode? Processes an Analysis Services object and all the objects that it contains. When Process Full is executed against an object that…
SSAS Interview Questions 6
· 1 min read
What is the Unprocess mode? Drops the data in the object specified and any lower-level constituent objects. After the data is dropped, it is not reloaded. This…
SSAS Interview Questions 7
· 1 min read
OLAP is a structure for analytical data. The OLAP is there to analyze business processes. What is ERP ? ERP is known as Enterprise Resource Planning.…
SSAS Interview Questions 8
· 1 min read
What is the Trend Expression ? The Trend expression is used to compare the current Status value with the value of Status at a previous point in time. As with…
SSAS Interview Questions 9
· 1 min read
What is an Action Target ? An Action Target is the portion of the cube to which the action relates and is the object that the user clicks to launch the action.…
SSIS Best Practices-Naming conventions
· 3 min read
I hope everyone had a wonderful July 4th. l want to end this week with one nice piece of information on the naming conventions used in SSIS. There are…
SSRS Interview Questions 1
· 1 min read
What is SSRS ? SSRS stands for SQL Server Report Services. It is the component of Microsoft SQL Server 2008 that provides an enterprise-ready and extensible…
SSRS Interview Questions 10
· 1 min read
Can you create Report Models from Cubes in BIDS ? You cannot generate a model from your OLAP systems in BIDS. But if you have to use your Cube as a source to…
SSRS Interview Questions 11
· 2 min read
What are Report Snapshots ? A report snapshot is a report that contains layout information and a dataset that is retrieved at a specific point in time. Unlike…
SSRS Interview Questions 12
· 4 min read
What are the differences between Push and Pull Deliver Methods ? With the Pull delivery method, the user is accessing the file from the Report Manager…
SSRS Interview Questions 2
· 1 min read
What are Ad Hoc Reports ? Ad Hoc Reports are reports that use queries that are made on the fly via drag and drop functionalities. They can be used by…
SSRS Interview Questions 3
· 1 min read
What is the Report Server ? Report Server is a web application that acts as a gateway to your underlying Report Server database. This way you can read the…
SSRS Interview Questions 4
· 2 min read
What is a Report ? A Report is a structured arrangement of information. Typically, the information in a business report comes from data in a business…
SSRS Interview Questions 5
· 2 min read
What are the most important a spects of Reports for the end user ? Accuracy and Performance. Business decision makers want accurate reports so they can…
SSRS Interview Questions 6
· 1 min read
What are the Preparation Tasks for installing Reporting Services? Before you can begin installing Reporting Services, you must make several decisions and…
SSRS Interview Questions 7
· 1 min read
What are the benefits of SharePoint Mode ? Office SharePoint Server provides several additional business intelligence features that enable you to integrate…
SSRS Interview Questions 8
· 1 min read
What is a Shared Data Source ? A Shared Data Source is a data source connection string that is shared by all the reports in the project. What is a Data Set ?…
SSRS Interview Questions 9
· 2 min read
What are Drill Through Reports ? Drill Through Reports are reports which link objects to specific actions. This makes the report more interactive because the…
Storage Modes and Caching in SSAS
· 2 min read
The cube metadata is always stored in the SSAS server, but as an administrator, you can control the storage locations of the cube data and any aggregations.…
Optimizing SQL Queries
· 5 min read
The independence holiday is here. I have half day off today so l just want to spend some time and write a short post on optimizing queries. Have you asked…
Understand, use and modify the system stored procedure SP_SPACEUSED in SQL SERVER
· 7 min read
Its a beautiful Sunday morning. l have a lot of things on my mind to write on the blog but I've been very busy lately and it really bothers me sometimes not…
SSIS Interview Questions 1
· 3 min read
Hello world, I hope everyone is doing great. I've not been able to contribute that much this year on the SSIS Tutorial. In fact, this is my first post on the…
2012 · 42 posts
SSIS Tutorial 4: Creating your first SSIS package.
· 9 min read
Merry Christmas to everyone out there. I hope you all had fun and kept yourselves from trouble during this wonderful season of the year. Today, I want to…
SSIS Tutorial 3: Understanding Basic SSIS Terminologies:Control Flow Tools, Data Flow Tools, Variables and Connection Managers.
· 4 min read
I'm continuing with the basic terminologies to get us going into knowing the details of the SSIS tool in general. In the previous tutorial, I've introduce the…
SSIS Tutorial 2: Understanding Basic SSIS Terminologies: Package, Control flow, data flow, event handler and ETL
· 4 min read
I've started talking about SSIS in the last tutorial and today, l'm moving forwared to introduce the Interface of the SSIS platform. Its very important to know…
SSIS Tutorial 1:Introduction.
· 4 min read
I've been writing sometime now on Transact Sequel (TSQL) which is the microsoft version of SQL and I hope those posts help clarify some major tasks in SQL…
TSQL Stored procedure to get factorial of a given number
· 1 min read
This is one little stored procedure to get the factorial of a given number. Some of this little things make you very good in programming as you develop your…
TSQL Program to determine the day(Name) of the first day of each month in the current year
· 2 min read
In this post, l just want to show some cool programming skills and hopefully make it useful for any one interested in learning TSQL and use of variables and…
TSQL script to print the database ids and their corresponding names for all database on a server.
· 3 min read
This is also some cool tool you can use to retrieve the database ids for all databases on your server. I like writing this type of scripts because they come in…
TSQL Stored Procedure that returns all tables in a given schema
· 1 min read
This is a little post to illustrate how to retrieve all tables in a given schema. This is a handy tool and can be used for various purposes. So lets call this…
TSQL Tutorial 27: Microsoft SQL Server Authentication
· 10 min read
I just want to talk briefly about how we can access sql server instance running either on your personal computer or on a network. Authentication is a big issue…
TSQL Tutorial 26: Pivot and Unpivot
· 6 min read
Hello, I'm briefly going to discuss one topic that can easily throw you under the bridge if you did not take the time to actually practice it. Pivot and…
TSQL Tutorial 25:Common Table Expressions(CTE)
· 7 min read
Hello world, l hope everyone is having peace where ever you are. This week has been a tough one for the United States, I'm talking in regards of the Theater…
TSQL Tutorial 23: Implementing Database Disaster Recovery-Backup and Restore
· 12 min read
I'm going to discuss one important job of Database administrators, that is Disaster management and recovery. This is a critical aspect of the life of a…
TSQL Tutorial 24: Index Maintenance
· 5 min read
Here we go again, guys. Lets take care of our indexes this time. Fragmentation occurs in case of large number of DML Statements on a table which has indexes.…
TSQL Tutorial 22: Partition
· 9 min read
Hello world, welcome to another important tutorial on database programming using TSQL. Today, we are going to talk about Partition. This is a very important…
TSQL Tutorial 21: Triggers
· 8 min read
Ok, here we go again guys/girls, lets try to learn something smart together. In this tutorial, I'm focusing on triggers. So What are triggers? I like to keep…
TSQL Tutorial 20: Index
· 13 min read
In this tutorial, l'll be discussing one of the most used database objects for optimizing the performance of queries. Indexes are very important and every…
TSQL Tutorial 19:Views
· 13 min read
I've been a little busy for the past week which made me fall behind most of the things l wanted to write on the Learning Blog. There are more features added to…
TSQL Tutorial 18:User Defined Functions.
· 3 min read
In the last tutorial, l discussed stored procedures. Stored procedures are used for implementing the complex logic of a business while functions are commonly…
TSQL Tutorial 17: Recompiling Stored Procedures, strength and weak points of stored procedures.
· 4 min read
In TSQL Tutorial 16, l discussed and implemented the various types of user defined stored procedures. I'm going to continue the discussion and probably end my…
TSQL tutorial16: Stored Procedures
· 7 min read
In this tutorial, I'm going to discuss one of the most important database objects that you'll ever come across in SQL Sever Programming, that is Stored…
TSQL Tutorial 14:Subqueries
· 5 min read
In today's discussion, I'm focusing on one important component of T-SQL, that is sub queries. As the name implied, a sub query is simply a query that is made…
TSQL Tutorial 15: Temporary tables, Derived tables and Table variables.
· 5 min read
l want to take on three topics in this tutorial. That is temporary tables, derived tables and table variables. l want to take on all of them at once so l can…
TSQL Tutorial 10:Aggregate Functions, Group By Clause, Having Clause
· 3 min read
Aggregate functions are in built functions that sql server uses to perform some basic tasks. If you need a more complex function to perform a task, then you…
TSQL tutorial 11: System Functions
· 2 min read
System functions are inbuilt functions that can be used to perform some tasks without writing code to perform the task. There are lot of system functions but…
TSQL Tutorial 12: JOINS
· 4 min read
In this tutorial, l'm going to touch on one of the most common statements you'll see on daily basis when writing sql in real time programming environment.…
TSQL Tutorial 13: Set Operators
· 3 min read
In this tutorial, we'll look at the set operators used in TSQL. Set operators and Joins work very similar but the biggest difference is that, in set operators,…
TSQL Tutorial 9: Control Structures(While, If and Case)
· 3 min read
I want to quickly go over the 3 main control structures in TSQL in this tutorial. Control structures are structures that help us control how we want our…
TSQL Tutorial 7: System Variables, Table Variables and User Defined Variables
· 3 min read
I'm going to discuss what variables are in TSQL and how to use them. First of all, if you have ever programmed, then you already know what a variable is. It's…
TSQL Tutorial 8: Dynamic SQL
· 2 min read
One very beautiful feature of sql is the dynamic nature. Dynamic sql referes to code/script which can be used to opereate on different data-sets based on some…
TSQL tutorial 6: Performing search using Wild Card %
· 4 min read
In this lesson, l want to briefly go over how to perform search using a wild card. The symbol used for wild card is the percent sign (%). Lets see how this is…
TSQL Tutorial 4. Delete, Drop and Truncate Statements
· 2 min read
In this lesson, l'm going to explore 3 statements that might sound similar but are different in how they work. The first statement is the Delete statement.…
TSQL Tutorial 5: UPDATE STATEMENT and the WHERE CLAUSE
· 2 min read
In this discussion, we'll explore the update statement and take look at the where clause. The Update statement is used for updating values in a cell/cells of a…
TSQL Tutorial 2:The Insert Statement
· 4 min read
In this discussion, we'll explore different ways records can be added to a table using the insert statement. The insert statement is the only statement that…
TSQL Tutorial 3 :The select and select into statement
· 2 min read
In this lesson, we'll explore the select statement. The select statement is used mostly for reading data from objects in the database. Let's create a table,…
TSQLTutorial 1: Constraints-Primary key, foreign key, check, default,unique key
· 5 min read
I've been discussing general information about database/sql development but l'm now going to concentrate on one flavour of SQL called TSQL, microsoft version…
Sql development. Tutorial 7: DDL,DML and DCL Components of SQL
· 4 min read
In this tutorial, l'm going to quickly go over the languages that exist in SQL. SQL stands for Structured Query Language. It is made up of three components,…
Sql development. Tutorial 5: Relation Schema
· 4 min read
Today, l'm discussing the relation schema. This document comes directly from the Entity Relationship Diagram. However, l don't think you need an entity…
Sql development. Tutorial 6: Normalization
· 4 min read
In this tutorial, l'm going to discuss the topic of normalization. normalization is the technique of minimizing redundancy and anomaly in the data. It leads to…
Sql Development. Tutorial 3:Candidate Key, Primary Key, Unique Key, Composite Key, Alternate Key, Secondary Key
· 7 min read
It's a beautiful Sunday afternoon here in San Diego. Wish l could be on the beach but l'm just home chilling. Ok, l'm going to continue the tutorial on…
sql development. Tutorial 4: Entity Relationship Diagram
· 6 min read
This weeks tutorial is dedicated to one important document in the database design life cycle. This is Entity relationship Diagrams, l'll refer to it as ERD…
Sql Developement. Tutorial 2: Database Design Levels
· 5 min read
In this second week of the tutorial, l'm going to talk about the levels of database development and explain what each of these levels are and how they are…
Sql Development Tutorial 1. Introduction
· 4 min read
This is the beginning of a series of personal tutorials l'll be giving on my blog about Database and Sql development. I'll write this tutorial for the…