PeterAvila
How to Use Values from Previous or Next Rows in a Query in SQL Server
I recently came across a problem that required having to perform a calculation in a query that involved a value in the current row and a value in the previous row. In SQL Server versions prior to 2012, you need … Continue Reading
Alternating Row Colors in SQL Server SSRS
Here is a simple report with alternating row colors; in this case, PaleGoldenRod and LightSteelBlue: In order to create alternating colors, we start by going to the Design tab and selecting the detail row in the tablix data region. Next, … Continue Reading
How to Create a Simple Find Feature for your SQL Server Database
As we see in the SQL100:Introduction to Transact-SQL and SQL250:Transact-SQL for Developers courses, SQL Server Management Studio (SSMS) has a view (aka panel or window) called Object Explorer that contains all the metadata of a database and much more. But … Continue Reading
Using TEXTSIZE to limit the amount of text displayed by the Select statement
My students sometimes ask me how they might limit the amount of text displayed in large text columns, like nvarchar(max), for example. Data types like nvarchar(max), varchar(max), varbinary(max), text, ntext, and image (the last three are deprecated and should not … Continue Reading
How to select multiple SQL Server database objects in SSMS
My students sometimes ask me how they can script multiple tables, views, or other database objects at once without having to do it one at a time. They correctly point out that you can select only one table at a … Continue Reading
How to Create a .Net Stored Procedure in SQL Server
One of the most exciting features of SQL Server is its ability to leverage the power of .Net. With .Net, SQL Server can more efficiently perform procedural tasks that are so inefficient in SQL, or perform non-database tasks, such as … Continue Reading
SQL Server -The Self-Join Query
There are times when it is necessary to join a table to itself. Let’s take a look at the Employee table in the AdventureWorks database to understand the need for a self-join and then how to put one together. We’ll … Continue Reading
Understanding Database Building Blocks in SQL Server
Every house has a kitchen, at least one bathroom and a bedroom, a front door, a plumbing system, and other things. These things can be arranged in different ways and in different numbers to produce different houses. So it is … Continue Reading
Understanding Isolation Levels in SQL Server 2008 R2 and 2012 through Examples
On your way to work, you stop by an ATM. Just as you are punching in your secret code, so is a family member punching in their secret code at an ATM across town. You are going to transfer $400 … Continue Reading
Using parameters with multiple values in SQL Server Reporting Services
In one of my SQL courses; 6236: Reporting Services, we learn how to create an “All” option in the values dropdown list of a parameter and see how to “wire up” the All option so that, when selected, the report … Continue Reading
