MS SQL Server

LearnSQL.com Team

SQL Server Cheat Sheet

Welcome to the SQL Server Cheat Sheet! Whether you're just starting out with SQL Server or you're a seasoned developer looking for a quick reference, this guide is tailored for you. This cheat sheet is a comprehensive guide to SQL Server, offering a quick reference to its essential commands. It covers the basics of creating and displaying databases and tables, the commands to modify tables, and the fundamental syntax for T-SQL commands such as SELECT, INSERT, UPDATE, and DELETE.

Jakub Romanowski

Track of the Season: SQL from A to Z in SQL Server

Maybe your company uses MS SQL Server and you’d like to join the data analysis team. Or maybe you want to add a new skill to your CV. Either way, our SQL Track of the Season: SQL from A to Z in MS SQL Server, will help. In this article, I’ll answer common questions about this awesome set of online MS SQL Server courses. If you’re reading this blog post, you probably already know that you should start learning SQL and working with databases.

Jill Thornhill

How to Learn T-SQL Querying

T-SQL is the dialect of SQL used by Microsoft SQL Server databases. Find out the best way to learn T-SQL querying with interactive online courses. In today's data-driven world, increasing your database knowledge is always a good choice. Microsoft SQL Server is one of the most popular databases nowadays. Knowing T-SQL – the query language of SQL Server databases –is often recommended as a worthwhile skill for careers in IT, business, or research fields.

Tihomir Babic

Top 29 SQL Server Interview Questions

Looking for an SQL Server job? Review these 29 must-know SQL Server interview questions and you’ll be well-prepared. And don’t worry about looking up the answers – we provide them, too! Microsoft SQL Server's first version was released in 1989 and has a rich history. It has come a long way since then, becoming one of the most used database engines in the data industry. In the last ten years, it’s been the third most popular database engine.

Jorge Sandoval

15 SQL Server Practice Exercises with Solutions

Enhance your SQL Server proficiency with our SQL Server practice exercises. Each of these 15 practical T-SQL tasks includes a detailed solution to help you improve your querying skills. You know how everyone says, "Practice makes perfect"? Well, it couldn't be truer for SQL. The real learning happens when you start working with queries, tables, and data. If you're looking to sharpen your skills in MS SQL Server, you're in the right spot.

Jill Thornhill

Integrating SQL with Python for Data Analysis

Integrating SQL with Python isn’t difficult. The two tools work together to combine the information-processing power of relational databases with the flexibility of a programming language. In this article, I will discuss the benefits of data analysis using SQL and Python, with real-world coding examples. Why do most of the best data analysis tools on the market – such as Tableau and Power BI – include both SQL and Python in their toolboxes?

Tihomir Babic

SQL Server Date Functions: A Data Analysis Guide

Date functions are the bread and butter of data analysis and reporting with SQL Server. In this guide, I will show you practical examples of the use of SQL Server date functions and how to apply them to your reports. A question for all data analysts: Do you want to be taken seriously? I’m sure you do. In that case, knowing SQL Server date functions is mandatory. Date and time data types are ubiquitous in databases, as they are essential for reporting.

Ignacio L. Bisso

CTE in T-SQL: A Beginner’s Guide with 7 Examples

A common table expression (CTE) is a powerful T-SQL feature that simplifies query creation in SQL Server. CTEs work as virtual tables (with records and columns) that are created on the fly during the execution of a query. They are consumed by the query and destroyed after the query executes. In some cases – like when the query expects data in a specific format and the source tables have the data in another format – a CTE can act as a bridge to transform the data in the source tables to the format expected by the query.

Nicole Darnley

How to Write a Recursive CTE in SQL Server

This guide will help you understand how to write and use recursive CTEs in SQL Server. Recursive CTEs are powerful tools for handling hierarchical data, and we'll break down their syntax and applications step by step. SQL Server offers a lot of powerful tools for working with data, including Common Table Expressions (CTEs). A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement, often referred to as a CTE query.

How to Divide Columns in SQL Server, PostgreSQL, or SQLite

Problem You want to divide one column by another in SQL Server, PostgreSQL, or SQLite. Example An online store has an orders table with data in the columns order_id, total_order_payment, and item_count. order_idtotal_order_paymentitem_count 124 2154 3562 Let’s say we want to extract the average cost per item for each order, i.e., the total payment for the order divided by the item count.

How to Delete Duplicate Rows in a Table in SQL Server

Problem You want to delete duplicate rows from an existing table in SQL Server. Example A company has its employee data stored in the table employees with the columns empName and dept. empNamedept Jack RusselSales Jan KowalskiHR John DoeSales Jack RusselSales John DoeSales Marta WilsonHR Jack RusselSales Let’s say you want to delete duplicates from this table and leave only one record among the duplicates.

Tihomir Babic

6 Useful Examples of CTEs in SQL Server

How can you use CTEs in SQL Server in your everyday professional life as a data pro? We’ll answer this question by giving you six examples. CTE is short for Common Table Expression. This is a relatively new feature in SQL Server that was made available with SQL Server 2005. A CTE is a temporary named result. This result is available only for the query that runs it. It isn’t stored, so it doesn't take up disk space.

Tihomir Babic

What Is a CTE in SQL Server?

What is a CTE, and how do you write a CTE in SQL Server? Join us on a journey where we’ll see all the typical usage of a CTE in SQL Server. CTEs (or Common Table Expressions) are an SQL feature used for defining a temporary named result. You can think of it as a temporary table whose output is available only when the main query is run. This is practical because the CTEs result isn’t stored anywhere but can always be referenced inside the query like any other table.

Martyna Sławińska

How to Export Data From Microsoft SQL Server to a CSV File

When working with data and databases, it is common to export data for further processing or transport to another database. Follow this article to learn how to export data from Microsoft SQL Server to a CSV file. In this article, we first recall what a CSV file is and why you would want to export data in this format from an MS SQL Server database. Then, we cut to the chase and export data to a CSV file using both SQL Server Management Studio (SSMS) and SQL Command Line (SQLCMD).

How to Group by Month in T-SQL

Problem You'd like to group records by month in a SQL Server database. Example Our database has a table named furniture with data in the columns id, name, and production_timestamp. idnameproduction_timestamp 1double bed2024-02-01 11:45:23 2coffee table2024-02-01 11:46:13 3chest of drawers2024-01-22 17:22:05 Solution You can use two DATEPART() functions to group records in a table by month and year.

How to Get the Previous Month in T-SQL

Problem You would like to display the previous month (without time) in a SQL Server database. Solution SELECT MONTH(DATEADD(MONTH, -1, CURRENT_TIMESTAMP)); Discussion To get the previous month in SQL Server, subtract one month from today's date and then extract the month from the date. First, use CURRENT_TIMESTAMP to get today's date. Then, subtract 1 month from the current date using the DATEADD function: use MONTH as the date part with -1 as the parameter.

Lisandro Fernigrini

What's New in MS SQL Server 2022

Microsoft SQL Server 2022 is almost here! In this article, we’ll review what new enhancements and features will be coming to this popular relational database. The new version of MS SQL Server will become available almost three years after SQL Server 2019 was released. (You can learn more about the origins and different versions of SQL Server in A Brief History of MS SQL Server.) If you need to start implementing a software solution, there are plenty of changes and new features in SQL Server 2022 that makes it a great option.

Alexandre Bruffa

Switching from PostgreSQL to MS SQL Server

What are the differences in PostgreSQL vs. MS SQL Server? We discuss the similarities and the differences between these two popular SQL dialects. Many of us developers and database specialists began working with free relational databases like PostgreSQL or MySQL for personal projects or small organizations. Some of us have since moved on to organizations using MS SQL Server and have had to learn to use the new tool. You may be wondering how challenging that is and whether you would struggle with SQL Server under similar circumstances.

Tihomir Babic

An Overview of Microsoft SQL Server Text Functions

Data analysts use text values when creating SQL reports. You can create your own labels with functions, classify rows based on text value, or do computations based on it. This article covers how to do all that. The text values in databases are not just for reading. As with numeric values, we perform operations on them and manipulate the text. SQL Server has a fair number of text functions.

How to Get Day Names in SQL Server

Problem You want to extract the name of the day of the week from a date in SQL Server. Solution 1: Get Day Name Using DATENAME() You can extract the day name from a date using the DATENAME() function. The first parameter is the interval (e.g. year, month, day, etc.) and the second is the date itself. To extract the day name, the interval must have one of the following values: weekday, dw, or w.

Jakub Romanowski

Top 5 MS SQL Server Courses for Beginners

Every journey, including a career change, begins with the first step. If you're thinking about learning data analysis or strengthening your MS SQL Server skills arsenal, you've come to the right place. Here are the top 5 MS SQL Server online courses. Enjoy! You've probably heard that today’s organizations rely heavily on data analysis. You heard right. They are looking for employees who can navigate the world of databases and draw conclusions from data.

How to Split a String in SQL Server

Problem: You want to split a string in SQL Server. There are several methods you can use to accomplish this. Below, we will go over a few practical approaches. Example 1: Split a sentence by space You have a sentence, and you'd like to split it by the space character. Solution 1: SELECT value FROM STRING_SPLIT('An example sentence.', ' '); The result looks like this: value An example sentence.

Kateryna Koidan

SQL Date and Time Functions in 5 Popular SQL Dialects

Are you confused by all the date and time functions used across different SQL dialects? In this article, I summarize the date and time data types used in PostgreSQL, Oracle, SQLite, MySQL, and T-SQL. I also provide examples with the key SQL date and time functions used across these dialects. It’s time to become date and time gurus! Do you want to calculate how often employees are running late for work?

How to Get the Current Date in SQL Server

Problem You’d like to get the current date in an SQL Server database. Solution SELECT CAST(GETDATE() AS DATE); Result: 2021-03-11 Discussion GETDATE() is a function that returns the current date and time. Arguments are not required. If you use just the GETDATE() function, you will get: 2021-03-11 22:28:17.280 If you want to get only the date and not the time, you need to use another function, CAST().

How to Find the Name of a Constraint in SQL Server

Problem You want to find the names of the constraints in a table in SQL Server. Example We want to display the names of the constraints in the table student. Solution SELECT table_name, constraint_type, constraint_name FROM information_schema.table_constraints WHERE table_name = 'student'; Here is the result: TABLE_NAMECONSTRAINT_TYPECONSTRAINT_NAME studentPRIMARY KEYPK__student__3213E83F357C7D1D studentUNIQUEUQ__student__24E2EDD2AC2A7D87 studentCHECKCK__student__age__4E88ABD4 studentFOREIGN KEYFK__student__country__4F7CD00D Discussion To find the constraint name in SQL Server, use the view table_constraints in the information_schema schema.

Dorota Wdzięczna

How to Export a CSV File From a T-SQL Query

Knowing SQL queries to select data from databases allows you to obtain information easily. However, there are many situations in which we need to export data to another platform or application. The CSV file is a solution to this problem. If you'd like to master complete SQL, try out our interactive SQL from A to Z track. It contains 7 hands-on SQL courses that will take you from a beginner to an advanced SQL user.

How to Order by Date in T-SQL

Problem: You want to sort the rows by date in SQL Server. Example 1: The exam table has two columns, subject and exam_date. SubjectExamDate Mathematics2019-12-19 English2020-01-08 Science2020-01-05 Health2020-01-05 ArtNULL You want to sort the rows by exam_date. Solution: SELECT * FROM Exam ORDER BY ExamDate; The result looks like this (the rows are sorted in ascending order by ExamDate):

Martyna Sławińska

An Overview of MS SQL Server Data Types

SQL Server data types define what can be stored in a column, local variable, expression, or parameter. It is essential to pick the right data type. Ultimately. your choice of data types affects the whole database. Read on to learn about all of the data types available in MS SQL Server. In this article, we’ll cover numerical, text, and date and time data type categories in detail. We’ll go through their syntax, storage size, and typical use cases.

Jakub Romanowski

Course of the Month: Creating SQL Reports in SQL Server

This is another in our series of free SQL Courses of the Month. This time, you can learn SQL Reporting in MS SQL Server for free. Do you want to be able to prepare even better SQL reports? Or maybe you have some basic knowledge of SQL Server (one of the major SQL dialects) and would like to go a step further? This is the course for you.

How to Extract a Substring From a String in T-SQL

Problem You would like to extract substrings from a text column in SQL Server. Example 1 In the emails table, there is an email column. You'd like to display the first seven characters of each email. The table looks like this: email jake99@gmail.com tamarablack@zoho.com notine@yahoo.fr jessica1995@onet.pl Solution 1 SELECT email, SUBSTRING(email, 1, 7) AS substring FROM emails; The result is:

Karolina Niewiarowska

A Brief History of MS SQL Server

Microsoft SQL Server is one of the best database management systems in the world. It’s constantly improved and widely used. But do you know how it was created? In this article, I’ll show you the history of MS SQL Server, a solution that changed the world of databases. We’ll explore the stages of MS SQL Server development, find out how the current version differs from the original, and talk about where you can learn or practice SQL Server.

Dorota Wdzięczna

How to Install MS SQL Server 2019 on Windows

Do you want to install SQL Server 2019 on your Windows PC? How about the latest version of SQL Server Management Studio? I’ll walk you through the process. Soon you’ll be writing T-SQL queries and operating on SQL Server databases! As I write this article, Microsoft SQL Server 2019 is the latest version of SQL Server. It’s the 15th version of one of the most popular database servers in the world.

Jakub Romanowski

Is MS SQL Server Still Worth Learning in 2024?

MS SQL Server is an in-demand skill in today’s job market. Should you learn  MS SQL Server in 2024? In this article, I’ll answer this question. Thinking about whether you should dive into Microsoft SQL Server in 2024 – especially with so many free database alternatives available? MS SQL Server has been a go-to for handling data for ages, thanks to its solid features and reliability. But now, with a bunch of new options on the block, some people wonder if it's still the champ.

Jakub Romanowski

Where Can I Find Good Courses to Practice MS SQL Server?

Begin your journey to mastering MS SQL Server with the finest practice exercises out there. Whether you are a beginner or seeking to improve your T-SQL skills, explore the top destinations for the most engaging and interactive MS SQL practice courses online. You've started learning MS SQL Server. Maybe you've done a course, watched some YouTube tutorials, and feel pretty confident in your knowledge. What now? Let me tell you, you need to practice.

Martyna Sławińska

MS SQL Server vs. PostgreSQL: Which to Choose?

Choosing the DBMS you will use for a new project is a very important and difficult decision. Two popular choices are MS SQL Server and PostgreSQL. To help you decide which would be best for you, I'll compare their features, list pros and cons, and give you some examples. Your selection of database management system (DBMS) depends on the type of business or project you're implementing. First, you need to know:

How to Find the Difference Between Two Datetimes in T-SQL

Problem You have two columns of the type datetime and you want to calculate the difference between them. Example In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure. The travel table looks like this: iddeparturearrival 12018-03-25 12:00:002018-04-05 07:30:00 22019-09-12 15:50:002019-10-23 10:30:30 32018-07-14 16:15:002018-07-14 20:40:30 42018-01-05 08:35:002019-01-08 14:00:00 Solution 1: Difference in Seconds SELECT id, departure, arrival, DATEDIFF(second, departure, arrival) AS difference FROM travel; The result is:

How to Calculate the Difference Between Two Dates in T-SQL

Problem You have two columns of the date type and you want to calculate the difference between them. Example In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between arrival and departure, or the number of days from arrival to departure inclusively. The travel table looks like this: iddeparturearrival 12018-03-252018-04-05 22019-09-122019-09-23 32018-07-142018-07-14 42018-01-052018-01-08 Solution SELECT id, departure, arrival, DATEDIFF(day, departure, arrival) AS date_difference, DATEDIFF(day, departure, arrival) + 1 AS days_inclusive FROM travel; The result is:

How to Group by Year in T-SQL

Problem You want to group your data by year in SQL Server database. Example One of the columns in your data is transaction_date. It contains a date. You would like to group all your data by year and calculate the total money earned each year. The data table looks like this: transaction_datemoney 2022-03-251700 2023-09-12100 2022-07-141200 2022-01-05400 2023-06-082000 2021-03-061500 Solution 1: Displaying the year and the money earned SELECT YEAR(transaction_date) AS year, SUM(money) AS money_earned FROM data GROUP BY YEAR(transaction_date); The result is:

How to Get Yesterday’s Date in T-SQL

Problem You would like to display yesterday's date (without time) in an SQL Server database. Solution SELECT DATEADD(day, -1, CAST(GETDATE() AS date)) AS YesterdayDate; Assuming today is 2020-09-24, the result is: yesterday_date 2020-09-23 Discussion To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime) and cast it to date.

How to Format a Date in T-SQL

Problem You’d like to display a date value in another format in SQL Server. Example Our database has a table named company with data in the columns id (primary key), name, and start_date. idnamestart_date 1Lisa Bank2019-01-202Credit Financial Institute2018-03-143Williams Holding2019-10-28 For each company, let’s convert their start date to a new format, YYYY/MM/DD, where YYYY is a 4-digit year, MM is a 2-digit month, and DD is a 2-digit day.

How to Convert an Integer to a Decimal in SQL Server

Problem You’d like to convert an integer value to a DECIMAL data type in SQL Server. Let’s convert an integer to the DECIMAL data type. Solution 1 We’ll use the CAST() function. Here’s the query you’d write: SELECT CAST(12 AS DECIMAL(7,2)) AS decimal_value; Here is the result: decimal_value 12.00 Discussion Use the CAST() function to convert an integer to a DECIMAL data type.

How to Convert Time Data from Strings in SQL Server

Problem You’d like to convert a string containing a date and time to a TIME value in SQL Server. Example We need to convert a string containing a weekday name, date, and time to a TIME value. Solution 1: Using PARSE() function We’ll use the PARSE() function. Here’s the query you would write: SELECT PARSE('Sunday, 2 February 2020 11:23:11.1134505' AS TIME) AS time_value; Here is the result:

How to Concatenate String and NULL Values in SQL Server

Problem You’d like to concatenate text columns in SQL Server, when some of the columns contain NULL. Example Our database has a table named children with data in the following columns: id, first_name, middle_name, and last_name. idfirst_namemiddle_namelast_name 1LindaNULLJackson 2MaryAliceThomson 3NULLStevenNULL 4NULLNULLBrown We want to display the first name from one column, the middle name from the next column, and the last name from the last column as one string, even if one of the columns stores a NULL.

Agnieszka Kozubek-Krycuń

How to Get the First Day of the Week in SQL Server

This is additional content for the LearnSQL.com course Customer Behavior Analysis in SQL Server. This interactive course will teach you how to analyze customer lifecycle in a SQL database in over 70 hands-on exercises. You will learn how to find out compare registration rates between periods of time, compute conversion rates in SQL, compare customer cohorts, analyze customer activity and customer churn over time. This course is essential for anyone doing customer analysis in their job.

Agnieszka Kozubek-Krycuń

How to Group Data by Week in SQL Server

This is additional content for the LearnSQL.com course Customer Behavior Analysis in SQL Server. In this course, we showed you how you can analyze the customer lifecycle (customer acquisition, conversion, activity, retention, and churn) with SQL. We discussed customer registration cohorts, or groups of customers who registered during the same period (e.g. same week, same month). Analyzing customer registration cohorts lets you see registration trends and relate registration cohorts with marketing campaigns.

How to Replace Part of a String in T-SQL

Problem: You’d like to replace part of a string with another string in T-SQL. Example 1: Our database has a table named life_insurance with data in the following columns: policy_ID, last_name, and first_name. policy_IDlast_namefirst_name v-01Anstruther - GoughGary V-23Elliot - Murray - StewartMary 3A-vSmith - DorrieAlex As you can see, some policyholders have hyphenated surnames. We’d like to change the separator between the parts of these policyholders’ last names.

How to Limit Rows in a SQL Server Result Set

Problem: You’d like to limit the rows in a SQL Server result set. Example: Our database has a table named toy with data in the columns id, name, and price. idnameprice 161Bike BMX200.00 121The Robot Tobie185.50 213Fishing game25.00 102Animal Puzzles45.80 111Race Track Set126.70 233Easy Bricks21.00 Let’s select toys’ names and prices.

How to Find Text by Start Characters in T-SQL

Problem You’d like to select rows that contain a string starting with one of a given group of characters. Example Our database has a table named gamer with data in the id and user columns. iduser 1superman 2magnus 3lucy 4stan 5ali Let’s find all the data for gamers whose user names start with 'a', 'b', 'r', or 's'.

How to Subtract 30 Days from a Date in T-SQL

Problem: You’d like to get the date 30 days before a given date in T-SQL. Example: Our database has a table named Computer with data in the columns Id, Name, and PurchaseDate. IdNamePurchaseDate 1Sony GX10002019-01-20 2Samsung LX20002019-04-15 3Dell K802019-08-30 Let’s get the name of each computer and the date 30 days before its purchase date.

How to Get the Year from a Date in T-SQL

Problem You’d like to get the year from a date field in a SQL Server database. Example Our database has a table named Children with data in the columns Id, FirstName, LastName, and BirthDate. IdFirstNameLastNameBirthDate 1JaneSmith2018-06-20 2GaryBrown2010-02-02 3LoraAdams2014-11-05 Let’s get the year from each child’s birthdate. Solution We’ll use the YEAR() function. Here’s the query you would write:

How to Get the Month from a Date in T-SQL

Problem You’d like to get the month from a date field in a SQL Server database. Example Our database has a table named Furniture with data in the columns Id, Name, and ProducedDate. IdNameProducedDate 1sofa2018-01-10 2chair2018-01-05 3desk2018-06-20 4bookcase2018-11-15 Let’s get the month from each product’s ProducedDate and find out which furniture was produced in a given month.

How to Get the Last Day of the Month in T-SQL

Problem You’d like to find the last day of the month for a specific date in T-SQL. Example Our database has a table named Furniture with data in the columns Id, Name, and PurchaseDate. IdNamePurchaseDate 1sofa2019-02-10 2desk2019-04-01 3bookcase2019-05-20 Let’s get products’ names and purchase dates and the last day of the month when these items were purchased.

How to Get the Day from a Date in T-SQL

Problem You’d like to get the number of day from a date field in a SQL Server database. For example, from the date of February 27, 2023, you'd like to get the number 27. Example Our database has a table named MedicalVisit with data in the columns Id, FirstName, LastName, and VisitDate. IdFirstNameLastNameVisitDate 1JaneMiller2019-11-17 2AlexSmith2019-11-20 3WilliamBrown2019-11-20 4AliceThomas2019-11-05 For each patient, let’s find the day of the medical visit.

How to Get the Current Date (Without Time) in T-SQL

Problem When working with SQL Server, you often need to get the current date without the time component. To achieve this in T-SQL, you can use several straightforward methods. Here, we will show you the most efficient and easiest way. Solution We’ll use the GETDATE() function to get the current date and time. Then we’ll use the CAST() function to convert the returned datetime data type into a date data type.

How to Get Current Date & Time in T-SQL (No Time Zone)

Problem You’d like to get the current date and time in T-SQL, but you don’t want the time zone offset. Solution We’ll use GETDATE(), CURRENT_TIMESTAMP, and SYSDATETIME() to get the current date and time without the time zone offset. The first two functions allow us to get the current time with a lower precision. (GETDATE() is a T-SQL function, while CURRENT_TIMESTAMP is a SQL standard function; both functions return the same data type).

How to Change Date and Time Formats in T-SQL

Problem You’d like to change the format of a date field or value in a SQL Server database. Example Our database has a table named Patient with data in the columns Id, FirstName, LastName, and RegistrationDate. IdFirstNameLastNameRegistrationDate 1JaneWilliams2019-06-20 2GabrielBrown2019-02-02 3LoraFolk2016-11-05 Let’s change the format of each patient’s registration date. We’ll put the name of the weekday first, followed by the month day and name and a 4-digit year (e.

How to Add Days to a Date in T-SQL

Problem You’d like to add a given number of days to a date in T-SQL. Example Our database has a table named Flight with data in the columns Code and DepartureDate. CodeDepartureDate LT20302023-02-20 GH11002023-03-01 SR54672023-12-30 Let’s change the departure date for all flights, adding two days to the current departure date. Solution We will use the DATEADD() function to specify the unit of time to add, define how much to add, and select the date to change.

How to Remove Leading and Trailing Spaces in T-SQL

Problem: You’d like to remove a spaces or a specific characters from the beginning and end of a string in T-SQL. Example: Our database has a table named company with data in two columns: id and name. idname 1' Super Market ' 2'Green shop ' 3' Modern Bookshop' Let’s trim the name of each company to remove the unnecessary space at the beginning and end.

Belma Mesihovic

Grouping Data in SQL Server

People who work with data know that data grouping can sometimes be a chore. If you struggle and waste hours with Excel, OpenOffice, or some other tool trying to group various sets of data, take a look at this article and learn how to do the work much quicker and easier by using SQL. You often find yourself in a situation where you need to analyze data and present the results in a form other than the one in which the data is stored.

Dorota Wdzięczna

14 Differences Between Standard SQL and Transact-SQL

In my last article, I roughly described how standard SQL differs from T-SQL and who should learn which. Now I'd like to focus on the syntax differences and illustrate these differences with examples. If you think T-SQL is an extension implementing all the features from standard SQL, you aren't right. However, in SQL Server you will find almost all the features of the SQL standard. In this article you will find examples of some of the differences in syntax between standard SQL and Transact-SQL.

Dorota Wdzięczna

What's the Difference Between SQL and T-SQL?

If you are beginning to learn SQL and are confused by the differences between standard SQL and other similar languages like T-SQL, this article will help make things clear. You’ll not only learn about the difference between SQL and T-SQL but also find explanations concerning which topics would be better to start learning first: standard SQL or something more specific like MS SQL Server. What is Standard SQL? SQL (Structured Query Language) is a basic ANSI/ISO standard programming language designed to operate on data stored in relational databases.

Roman Pijacek

Microsoft SQL Server Pros and Cons

Thinking about using Microsoft SQL Server? If so, you’re in the right place. In this article, we’ll go over the pros and cons of Microsoft SQL Server and evaluate the platform from both a company’s and a data specialist’s perspective. What Is Microsoft SQL Server? Before diving into the pros and cons of Microsoft SQL Server, I’ll explain what it is. In technical terms, it is a relational database management system (RDBMS) developed by Microsoft.

Dorota Wdzięczna

Microsoft SQL Server 2017 Installation Step by Step

Microsoft SQL Server is one of the most popular professional database servers on the market. In this guide, I’ll show you all SQL Server 2017 installation steps to help you install it on the Windows operating system together with SQL Management Studio. If you want to learn T-SQL, the dialect of SQL used in SQL Server, check out our SQL from A to Z in MS SQL Server track.

Jeffrey J. Keller

Grouping, Rolling, and Cubing Data

The first two articles in this series highlighted SQL Server's ability to organize data into user-defined windows and its aggregate functions. Part 3 will focus on other methods of aggregating and organizing data using built-in SQL Server features – specifically, its grouping functions. For consistency, the same base data will be used as in the first two parts of this series. CREATE TABLE WindowTable (WindowID INT IDENTITY, House Varchar(32), FullName Varchar(64), PhysicalSkill Decimal(4, 2), MentalSkill Decimal (4, 2)) GO

Jeffrey J. Keller

Window Functions: Part Two: Aggregating Data

In part one of this series on SQL Server window functions, the functionality of the OVER() clause was discussed. This article will focus on aggregate functions. The majority of these can be used in conjunction with OVER. Some, like SUM() and AVG(), are widely used. Others — including VARP() and CHECKSUM_AGG() — are not as well-known, but are potentially quite useful. OVER defines the user-specified range on which a function is applied.

Jeffrey J. Keller

Window Functions in SQL Server: Part One: The OVER() Clause

Window functions were first introduced in standard SQL 2003 and then extended in SQL 2008. Since SQL Server 2005, Microsoft has been steadily improving window functions in the database engine. These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row. Well-known aggregate functions include SUM, AVG, MIN, MAX, and many others. Recent versions of SQL Server have introduced ranking and analytic functions such as LAG, LEAD, RANK, CUME_DIST, and many others.

Patrycja Dybka

Basic Date and Time Functions in MS SQL Server

As a follow up to our article “The Most Useful Date and Time Functions in Oracle Database”, let’s review what date and time functions look like in MS SQL Server. Let’s start with functions that extract a year, month and day from a given date. declare @dt date = '2014-10-20' select year (@dt) as year, month (@dt) as month, day (@dt) as day SQL Server uses GETDATE() and SYSDATETIME() to get a current date and time.