Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Temporary Tables and Table Variables. As a TSQL Developer or DBA, while writing TSQL code for the business login or DBA maintenance tasks, we come across the situation to work on temporary data. To accomplish this, we generally use temporary table or table [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Stored Procedures. A Stored Procedure is a group of one or more T-SQL statements into a logical unit. Stored Procedure accepts three types of parameters input parameters, output parameters and table-valued input parameters. 1. Input parameters pass data into a stored procedure and [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Set Operators. Transact-SQL set operators allows us to combine the results from two or more queries into a single result set. The number of columns and the data type for the order of columns must be same for all queries. This lab is [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Ranking Functions. Transact-SQL (T-SQL) introduces multiple ranking functions to return a ranking value for each row in a partition result-set. This lab is divided into six exercises explaining different types of ranking functions such as ROW_NUMBER, RANK, DENSE_RANK and NTILE functions. Exercise 1: [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Performance Tuning using Trace Flags. Installation of SQL Server without any change comes with various default settings. Sometimes due to the variety of data or schema of user created objects, the default settings not worked properly. This starts impacting the performance of the [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Joins. Using joins in SQL Server we can retrieve data from two or more tables based on the relationship between those tables. This lab is divided into six exercises explaining different types of joins such as INNER JOIN, OUTER JOIN, CROSS JOIN and [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Execution Plans. An execution plan in simple words is the result of SQL Server query optimizer’s attempt to estimate the most appropriate and efficient way to implement T-SQL queries submitted by users. Execution plan reflects, in other words can tell us how a [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Dynamic SQL. Dynamic SQL statements are the queries that are built at the run time of a program to form an executable SQL string. Even though static SQL is good and easy to build, sometimes data cannot be determined well in advance which [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server DML Triggers. A DML (Data Manipulation Language) trigger is a special type of stored procedure that fires when an Insert, Update, or Delete operation is performed on a database table. DML triggers are used to implement business rules when data is altered and [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server DML Optimization. DML operations are most frequently used operations in SQL Server to perform data insertion or data update. While dealing with huge data, it is necessary to know the ways using which we can optimize the performance of data insertion and data [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server DDL Triggers. DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. This lab is divided into [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Data Types – Part 2. Data types are used to defined what type of data a column can store. As like in any other language SQL server also has data types. This lab is divided into five exercise. In this first exercise we [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Data Types – Part 1. Data types are used to defined what type of data a column can store. As like in any other language SQL server also has data types. This lab is divided into four exercises. The first exercise explains about [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Aggregations. Aggregations functions are used to perform calculations on a group of values to return a single value. In this lab, we will look into various basic and advanced aggregations. In the first exercise, we will look into various basic aggregate functions such [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Advanced Query Tuning. This lab talks about a very common yet difficult problem, finding TOP n rows per group. The problem may seem easy, however in this lab we’ll discuss multiple ways to solve this problem and compare the different ways on performance [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Advanced Aggregations. To meet various business requirements, sometimes we need to calculate cumulative aggregations, sliding window aggregations, Year-To-Date aggregations. This lab is divided into four exercises. In the first exercise, we will learn about cumulative aggregations and will learn to find running total [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: Working With SEQUENCE in SQL Server. In Transact-SQL (T-SQL) 2012 SEQUENCE is a user-defined object to generate sequence of numbers based on the specifications while created. NEXT VALUE FOR function generates sequence number from the specified SEQUENCE object. This lab is divided into six exercises [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: Pivoting in SQL Server. To understand and analyze the data in more efficient way, we may have to rotate rows into columns and columns into rows. The rotation of rows to columns is called pivoting and the reverse operation is called unpivoting. This lab explains [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: Optimizing SQL Server Stored Procedures. Whenever an application is performing slowly because of database, then we need to optimize the stored procedures in that database. This lab is divided into three exercise explaining various techniques that are used to optimize the stored procedure such as [...]
Hello Friends, Here is another popular lab from Hands-On-Lab library: OFFSET and FETCH in SQL Server. In Transact-SQL (T-SQL) 2012 OFFSET-FETCH clause provides us an option to fetch page of results from the result set. This lab is divided into six exercises explaining different usages of OFFSET-FETCH clause such as skip few rows from the [...]
