Generated by All in One SEO v4.9.9, this is an llms.txt file, used by LLMs to index the site. # Simple SQL Tutorials Your guide to querying and developing SQL Server databases ## Sitemaps - [XML Sitemap](https://simplesqltutorials.com/sitemap.xml): Contains all public & indexable URLs for this website. ## Posts - ["Why should I save my query as a View instead of a .SQL file?" Answered!](https://simplesqltutorials.com/sql-server-view-vs-sql-script-file/) - "If I have a query that I run frequently, why should I save it as a View in the database instead of just simply saving it as a .SQL script file and running the query when I need it?" There are two main benefits to saving your query as a View in the database: It - [SQL Server DELETE vs. TRUNCATE: What's the difference?](https://simplesqltutorials.com/sql-server-delete-vs-truncate/) - The DELETE and TRUNCATE statements are both meant to delete content from a table. Since that's true, it begs the question: What's the difference? In this very brief tutorial, we'll walk through the main differences between DELETE and TRUNCATE and explain when you should choose to use one over the other. Difference # 1: DELETE - [SQL Server COALESCE System Function: How It Works](https://simplesqltutorials.com/sql-server-coalesce/) - The COALESCE system function offers a great way to check expressions for NULL. The syntax for COALESCE is very simple. It looks like this: COALESCE(,,,,....) As you can see, all we do is outline a comma-separated list of expressions to the function. SQL Server will evaluate each expression, from left to right, and see if - [Can we create indexes in table variables? YES WE CAN!](https://simplesqltutorials.com/sql-server-indexes-on-table-variable/) - It is absolutely possible to create indexes within a table variable in Microsoft SQL Server. The key is to add the indexes as part of the table variable definition Take a look at this regular table definition (which does not have any indexes placed on it yet): CREATE TABLE Books ( BookID INT, Author VARCHAR(30), - [SQL Server WINDOW Clause: Explained!](https://simplesqltutorials.com/sql-server-window-clause/) - The SQL Server WINDOW clause is a new feature introduced in Microsoft SQL Server 2022. It can make the syntax of a window query much more readable. In this brief tutorial, we'll walk through how the WINDOW clause works and see how it can be very helpful. Start with a normal window query Let's start - [How to calculate age in SQL: Use THIS simple script!](https://simplesqltutorials.com/sql-server-calculate-age/) - One interesting task you may be given as a data professional is to calculate a person's age based on their date of birth. In this brief tutorial, we'll discuss a simple script you can use to derive the number of years, months, and days that a person has aged since their date of birth! The - [SQL Server ISNULL: Explained with Examples](https://simplesqltutorials.com/sql-server-isnull/) - The SQL Server ISNULL system function is one of the most useful functions you will use in your work as a data professional. The ISNULL function is meant to help you deal with NULLs in SQL Server. It's meant to be used as an error handling tool of sorts. The syntax of ISNULL The ISNULL - [Do you know this FACTOID about Foreign Key Constraints in SQL Server?](https://simplesqltutorials.com/sql-server-foreign-key-on-unique-column/) - There is an interesting factoid you should know when it comes to working with foreign key constraints in SQL Server. Normally, foreign key constraints are linked to a Primary Key column in a parent table Take a look at the following Books and BookSales tables: CREATE TABLE Books ( BookID INT IDENTITY(10,5) PRIMARY KEY, Title - [SQL Server ANSI_NULLS Setting: What does it do?](https://simplesqltutorials.com/sql-server-ansi-nulls/) - The SQL Server ANSI_NULLS setting can change the outcome of comparisons to NULL in your database applications. Consider the following table: What do you think will be returned by the following query?: SELECT * FROM Employees WHERE MiddleName = NULL Here's the result: Nothing at all! By default, anything compared to NULL yields the logic - [How to make a column NOT NULL in SQL Server: Explained with Examples](https://simplesqltutorials.com/sql-server-modify-column-to-not-null/) - You may find yourself needing the change the definition of a table or column from time-to-time while working as a database professional. One change you may need to make is changing a column from nullable to non nullable. It may have been decided that we actually don't want NULL to appear in a column after all. - [Difference between unique constraints and primary key constraints: Answered with examples](https://simplesqltutorials.com/sql-server-unique-constraint-vs-primary-key-constraint/) - A primary key constraint and a unique constraint are both meant to enforce uniqueness in column(s). Since that's true, it begs the question: What's the difference? In this very brief tutorial, we'll discuss the two main differences between primary key constraints and unique constraints. Difference # 1: Primary key constraints cannot contain NULL values. Unique - [SQL Server Week Number: Use this ONE Simple Function!](https://simplesqltutorials.com/sql-server-week-number/) - SQL Server makes it very easy to gather the week number of a specific date. Maybe you need to know what week of the year a certain date falls within. The DATEPART system function is what we can use to gather that information. Using the DATEPART system function The date of the writing of this - [SQL Server QUALIFY: Doesn't Exist, Do THIS Instead](https://simplesqltutorials.com/sql-server-qualify/) - SQL Server has many useful window functions available to us that can make the task of gathering aggregate/ranking/offset data very easy. One tool that you might have heard of is the QUALIFY clause. This tool can be used to filter the result of a window function. The problem is that SQL Server does not have - [DELETE Statement with a JOIN: How it's done](https://simplesqltutorials.com/sql-server-delete-statement-with-a-join/) - It is very easy to write a DELETE statement with a JOIN. In this very brief tutorial, we'll walk through how it's done. We'll go over just these two topics: Write it as a SELECT statement first Convert your query to a DELETE statement Everything in this tutorial can also be found in the following - [UPDATE statement with JOIN: How it's done](https://simplesqltutorials.com/update-statement-with-join/) - It can be tricky to perform an UPDATE statement with a JOIN, but learning how it's done can help you write more accurate UPDATE statements. - [Default Values in Stored Procedure Parameters: Do you know this rule?](https://simplesqltutorials.com/default-values-in-stored-procedure/) - It is very easy to set up a default value for an input parameter in a stored procedure. When we establish a default value for an input parameter, it becomes optional to provide a value for that parameter when calling the procedure. In that event, the value used for the parameter is whatever the default - [SQL Server WAITFOR DELAY: Explained](https://simplesqltutorials.com/sql-server-waitfor-delay/) - The WAITFOR DELAY system function is the best tool we have in Microsoft SQL Server to introduce a pause in our SQL code. The syntax is extremely simple. We just run the WAITFOR DELAY statement on its own, like this: WAITFOR DELAY '00:00:10' You can see how in single quotes, we outline the duration of - [How to Gather User ID Values in SQL Server: Just use this one helpful function.](https://simplesqltutorials.com/sql-server-user-id/) - There may be times where it might be helpful to gather the ID of the person or application that is interacting with the data or objects in your database. Maybe you are trying to audit changes to your data, or maybe you want to get a better idea of how the data is being queried. - [SQL Server Functions VS Stored Procedures: What's the difference?](https://simplesqltutorials.com/sql-server-functions-vs-stored-procedures/) - Two of the most helpful objects you can create in Microsoft SQL Server are user-defined functions and stored procedures. If you are just starting out with SQL Server, you might wonder "What's the difference?". In this very brief tutorial, we'll discuss the difference between SQL Server functions and stored procedures and discuss when you should - [SQL Server Filtered Indexes: Explained for Beginners](https://simplesqltutorials.com/sql-server-filtered-index/) - Filtered indexes in SQL Server can dramatically increase the efficiency of queries against data in your database. They are a fantastic optimization tool you should understand and remember during the design and development of tables in your database! In this brief tutorial, we'll discuss what a filtered index is and how they can help our - [SQL Server Covering Indexes: Explained with Examples](https://simplesqltutorials.com/sql-server-covering-index/) - Indexes are extremely important when it comes to writing queries that return data in an efficient way. We not only want to make sure our queries return exactly the information we need, but we also want that information to be returned to us quickly. In this brief tutorial, we'll discuss the idea of a covering index - [SQL Server Key Lookup: Explained with Examples](https://simplesqltutorials.com/sql-server-key-lookup/) - The SQL Server "Key Lookup" operation is something you may see when looking at the execution plan of a query. Maybe you're trying to optimize the query to run a bit faster and noticing that a lookup operation is taking a LARGE amount of the query processing time. Or maybe you're simply trying to understand - [Don't make this OUTER JOIN mistake!](https://simplesqltutorials.com/sql-server-left-join-mistake/) - The OUTER JOIN operations, such as LEFT JOIN and RIGHT JOIN, are very important to know when it comes to querying Microsoft SQL Server databases. There is one important factoid you should know if you are using an OUTER JOIN in combination with the IS NULL predicate. Let's create a bit of data to work - [Why can't I add a foreign key constraint? ANSWERED!](https://simplesqltutorials.com/alter-table-foreign-key-error-message/) - When attempting to create a foreign key constraint on a table, you may receive an error message very similar to the following: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "constraint_name". The conflict occurred in database "DatabaseName", table "TableName", column 'ColumnName'. This may be happening because you are attempting to create a foreign - [How to remove trailing/leading whitespace in SQL](https://simplesqltutorials.com/sql-server-trailing-leading-whitespace/) - How do we remove whitespace from a character string in SQL Server? It's easy, just use the LTRIM/RTRIM string manipulation function! Take the following silly example: We have a large amount of leading whitespace in our string. To remove that whitespace, we can put this string in the LTRIM system function: The LTRIM system function - [SQL Server TRY_CONVERT: Explained with Examples](https://simplesqltutorials.com/sql-server-try_convert/) - The SQL Server TRY_CONVERT system function is a great alternative to the standard CONVERT function. We use CONVERT and TRY_CONVERT to change an expression from one data type to another "on the fly". This begs the question: What's the difference? TRY_CONVERT will return NULL if the conversion fails. Here is a very simple example of - [SQL Server Nested Transactions: What's the deal?](https://simplesqltutorials.com/sql-server-nested-transactions/) - Here's the deal with nested transactions: There really isn't a concept of "nested transactions" in SQL Server. That is, an inner transaction that can operate independently of an outer transaction. Let's work through an example using the content from an Orders table: The following code is a very simple example of a single transaction that - [SQL Server Ternary Operator: The IIF function](https://simplesqltutorials.com/sql-server-ternary-operator/) - Believe it or not, SQL Server has a ternary operator like many other computer languages out there. Like other ternary operators, it can shorten the syntax of a very simple decision structure to just one line of code. The SQL Server ternary operator is the IIF function. Here is the syntax: IIF(, - [SQL Server CHOOSE: Explained](https://simplesqltutorials.com/sql-server-choose/) - The SQL Server CHOOSE function is a simple decision structure you can use to simplify an otherwise complex expression. The syntax for CHOOSE is extremely simple: CHOOSE(, , ,......) All you do it outline an integer as the first parameter, followed by a comma-separated list of expressions. The expressions can be simple string literals or - [How long can a table name be? Learn this "factoid" and more!](https://simplesqltutorials.com/sql-server-table-name-length/) - There are many, many details one can learn about Microsoft SQL Server over a very long career in data science. This blog, with over 120 posts, only begins to scratch the surface! In this weeks tutorial, I'd like to talk about just a few unique factoids you might find interesting: Factoid # 1: Table names - [How to disable a Foreign Key Constraint: Run this ONE simple statement!](https://simplesqltutorials.com/sql-server-disable-foreign-key-constraint/) - Foreign key constraints are an excellent way to preserve the referential integrity of the data in your database. If you're new to SQL Server and don't fully understand what a foreign key is or how they work, make sure you check out the full beginner-friendly tutorial first to learn everything you need to know: SQL - [SQL Server Logical Query Processing Order: Explained!](https://simplesqltutorials.com/sql-server-logical-query-processing-order-explained/) - When executing a query, SQL Server will process the different clauses of that query in a very particular order. Other programming languages like C++ or C# will process code in a top-down manner. SQL Server does not work that way. Take a look at the following two tables, Products and Orders: Using this data, I'll construct a - [SQL Server Temp Tables: A How-To Guide for Beginners](https://simplesqltutorials.com/sql-server-temp-table/) - SQL Server temp tables are very useful tools available to us when it comes to querying and developing SQL Server databases. Temp tables offer a very easy way to create a table for a singular purpose and then remove that table when we are finished with it. In this very brief tutorial, we'll discuss everything you - [SQL Server Sequence Object: Explained for Beginners](https://simplesqltutorials.com/sql-server-sequence-object/) - One of the most useful tools available to us in Microsoft SQL Server is the sequence object. Sequence objects are a great way to let SQL Server track and update an integer value that can be used to populate a primary key column. In this very brief tutorial, we'll discuss everything you need to know - [Do you know this IMPORTANT RULE about Foreign Key Constraints?](https://simplesqltutorials.com/foreign-key-constraint-set-default/) - Foreign Key Constraints in Microsoft SQL Server are excellent for preserving the integrity of our data. We use a foreign key constraint to basically establish a parent-child relationship between two tables. A foreign key constraint will enforce that a value must first exist in the parent table before it can be referenced in the child - [SQL Server IN Clause: Explained with Examples](https://simplesqltutorials.com/sql-server-in-clause/) - The SQL Server IN clause is a very helpful tool we can use to simplify our queries. In this very brief tutorial, we'll discuss how the IN clause works and provide examples of it's use. Take the following Books table: What if we wanted to write a query where we display book details where the - [How to break a WHILE loop in SQL Server: Explained with Examples](https://simplesqltutorials.com/sql-server-break-while-loop/) - The task of breaking a WHILE loop in Microsoft SQL Server is very simple. We basically break a while loop when we want to terminate the loop early. That is, earlier than the loop would have ended if left to it's natural progression. To break a WHILE loop early, just use the BREAK keyword! Take - [How to find a string within a string in SQL Server: Explained with Examples](https://simplesqltutorials.com/sql-server-find-string-in-string/) - The task of finding a character string within a larger character string in a SQL Server database is very easy. SQL Server has many built-in system functions that can be used for character string manipulation, one of which is the CHARINDEX function. We use the CHARINDEX system function to locate a string within a string! - [How to drop a column in SQL Server: Explained with Examples](https://simplesqltutorials.com/sql-server-drop-column/) - Knowing how to make modifications to the definition of a table is very important when it comes to developing Microsoft SQL Server databases. The task of deleting a column is very straightforward. In this very brief tutorial, we'll discuss how to drop a column from a Microsoft SQL Server table. Run the DROP COLUMN statement - [What does "SET NOCOUNT ON" do in SQL Server? Explained!](https://simplesqltutorials.com/set-nocount-on/) - When we run "SET NOCOUNT ON", it stops the informational message from appearing that tells us how many rows were affected by a T-SQL statement. - [SQL Server Implicit Transaction Mode: Proceed with Caution!](https://simplesqltutorials.com/sql-server-implicit-transaction-mode/) - When IMPLICIT_TRANSACTIONS is enabled, all transactional statements are automatically put in an open transaction. YOU must make sure it gets closed! - [SQL Server NULLIF: Explained for Beginners](https://simplesqltutorials.com/sql-server-nullif/) - The SQL Server NULLIF system function accepts two input values. If those two input values are equal, NULL is returned. Otherwise, the first value is returned. - [Do you know this rule about the CAST function?](https://simplesqltutorials.com/cast-with-user-defined-data-type/) - You must use a system data type as the target data type in the CAST() function. You cannot use a user-defined data type. - [FULL OUTER JOIN: The Ultimate Guide for Beginners](https://simplesqltutorials.com/full-outer-join/) - The FULL OUTER JOIN will return all content from both the left table and right table, regardless of if there is a match in the opposite table. - [RIGHT JOIN in SQL Server: Explained for Beginners](https://simplesqltutorials.com/right-join-in-sql-server/) - The RIGHT JOIN operation will pull all rows from the right table, regardless of if there is a matching row in the left table. - [LEFT JOIN and LEFT OUTER JOIN: What's the difference?](https://simplesqltutorials.com/left-join-and-left-outer-join/) - LEFT JOIN and LEFT OUTER JOIN are the same thing. The word 'OUTER' is optional. You will usually see it omitted because it's more to write! - [Do you understand this rule about SQL Server aggregate functions?](https://simplesqltutorials.com/do-you-understand-this-rule-about-sql-server-aggregate-functions/) - Aggregate functions may return NULL if there is no data to aggregate. If you are always expecting a number to return, this can be a problem! - [How to check if a column exists in SQL Server](https://simplesqltutorials.com/how-to-check-if-a-column-exists-in-sql-server/) - We can use the very handy COL_LENGTH system function to see if a column exists. The function returns NULL if the column does not exist. - [Don't make this mistake when calling your SQL Stored Procedures!](https://simplesqltutorials.com/dont-make-this-mistake-when-calling-your-sql-stored-procedures/) - As soon as you start outlining a parameter name in a call to a stored procedure, all the parameters after it must also outline their parameter name. - [SQL Server IS NULL: Why can't I just use "= NULL"?](https://simplesqltutorials.com/sql-server-is-null/) - The SQL Server IS NULL predicate is used to determine if a value is NULL. We can't use "= NULL" because this yields a result of "unknown". - [SQL IF EXISTS Decision Structure: Explained with Examples](https://simplesqltutorials.com/sql-if-exists/) - The IF EXISTS decision structure will execute a block of SQL code only if an inner query returns one or more rows. - [CROSS JOIN in SQL Server: Explained for Beginners](https://simplesqltutorials.com/cross-join/) - A CROSS JOIN will match all rows from table A with each row from table B. The final result set is basically a Cartesian product of the two tables. - [How to get the data type of a return value in SQL Server](https://simplesqltutorials.com/how-to-get-the-data-type-of-a-return-value-in-sql-server/) - We can use the SQL_VARIANT_PROPERTY system scalar function to get the data type of a return value in Microsoft SQL Server. - [How to create a table variable in SQL Server](https://simplesqltutorials.com/sql-server-table-variable/) - Creating a table variable in SQL Server is very similar to creating a regular variable. We use the DECLARE keyword, followed by the variable name, then the type. - [SQL Server GO: Explained with Examples](https://simplesqltutorials.com/sql-server-go/) - The SQL Server GO keyword is used to separate SQL code into individual batches. A batch is a unit of code that either succeeds or fails in its entirety. - [SQL Server EXISTS: Explained for Beginners](https://simplesqltutorials.com/sql-server-exists/) - The EXISTS predicate is used in a query that contains an inner subquery. It evaluates if the subquery returns rows (True) or no rows (False). - [Correlated Subqueries: A Beginner's Guide with Examples](https://simplesqltutorials.com/correlated-subqueries/) - Correlated subqueries reference a column value from the outer query, which means a correlated subquery cannot be ran independently. - [Are you making these 3 mistakes in your stored procedures?](https://simplesqltutorials.com/are-you-making-these-3-mistakes-in-your-stored-procedures/) - One of the most common objects you'll use in Microsoft SQL Server is stored procedures. Try to avoid making these 3 common mistakes! - [SQL Server CONVERT: A How-To Guide with Examples](https://simplesqltutorials.com/sql-server-convert-a-how-to-guide-with-examples/) - The SQL Server CONVERT function is an extremely handy tool to know when it comes to querying SQL Server databases. CONVERT is a system function we can use to very easily convert a value from one data type to another on-the-fly. In this very brief tutorial, you'll learn everything you need to know about the - [What's the difference between VARCHAR and NVARCHAR? Answered!](https://simplesqltutorials.com/sql-server-difference-between-varchar-and-nvarchar/) - The difference between VARCHAR and NVARCHAR is that VARCHAR will use 1 byte per character to store a value, while NVARCHAR will use 2 bytes. - [SQL Server REPLACE: Explained](https://simplesqltutorials.com/sql-server-replace/) - We use the REPLACE function in a SELECT query to find all instances of a substring within a larger string, and replace it with a new value. - [SQL Server DATEDIFF System Function: Explained](https://simplesqltutorials.com/sql-server-datediff/) - The SQL Server DATEDIFF system function is a very useful function for manipulating date information in your database. In this very brief tutorial, we'll discuss how the DATEDIFF function works and the key topics you should know when working with this function. Here is what we'll discuss: The syntax of the DATEDIFF function The 'datepart' values - [SQL Server DATEADD System Function: Everything you need to know](https://simplesqltutorials.com/sql-server-dateadd/) - We can use the DATEADD system function to calculate what the date will be at some point in the future (or the past!) - [SQL Server Default Constraint: A Guide for Beginners](https://simplesqltutorials.com/sql-server-default-constraint/) - A default constraint is placed on a column to outline a default value to use in an INSERT statement when no value is provided for that column - [SQL Server Check Constraint: Full Beginner-Friendly Tutorial](https://simplesqltutorials.com/check-constraint/) - A Check constraint defines a predicate that any new value must pass in order for that new value to be entered into the column. - [SQL Server NVARCHAR Data Type: Explained](https://simplesqltutorials.com/nvarchar/) - NVARCHAR is a Unicode data type meant to store character string data. We use this data type when the size of the values we want to store will vary greatly. - [SQL CHAR Data Type: Explained](https://simplesqltutorials.com/sql-char/) - The CHAR data type is used to store character string values. It is ideal when the values you want to store will all be similar in size. - [SQL VARCHAR Data Type: The Ultimate Guide for Beginners](https://simplesqltutorials.com/sql-varchar/) - The VARCHAR data type is used to store a character string value. We use VARCHAR when the size of the values we want to store will vary greatly. - [How to change a column type in SQL Server](https://simplesqltutorials.com/sql-server-how-to-change-column-type/) - We use the ALTER COLUMN statement in an ALTER TABLE statement to change the data type of a column in Microsoft SQL Server. - [How to escape a single quote in SQL Server](https://simplesqltutorials.com/sql-server-escape-single-quote/) - If you need to escape a single quote when querying or inserting rows in a SQL Server database, you would simply use ANOTHER single quote. - [How to see the definition of a View in SQL Server](https://simplesqltutorials.com/sql-server-how-to-see-view-definition/) - We can see the definition of a View in SQL Server by either using the SP_HELPTEXT system stored procedure or by using the Object Explorer. - [How to change a table name in SQL Server](https://simplesqltutorials.com/sql-server-change-table-name/) - The task of changing a table name is very easy to do in Microsoft SQL Server. We use the system stored procedure SP_RENAME. - [What's the difference between CHAR and VARCHAR? Answered!](https://simplesqltutorials.com/difference-between-char-and-varchar/) - When storing a value in memory, CHAR reserves the entire amount of memory as defined by the data type. VARCHAR will reserve only as much space as is needed to store the value, and nothing more. - [What is a Candidate Key in SQL Server? Answered!](https://simplesqltutorials.com/candidate-key-in-sql/) - A candidate key is a column or group of columns that can be used to uniquely identify a row in a table. A table can have multiple candidate keys. - [What's the difference between ROWS and RANGE? Explained!](https://simplesqltutorials.com/difference-between-rows-and-range/) - We use the ROWS and RANGE units when using a window function in our queries. The RANGE unit will include peers while the ROWS unit will not. - [SQL LAG and LEAD Window Functions: Explained](https://simplesqltutorials.com/sql-lag-and-lead/) - The SQL LAG and LEAD window functions will return an element that is a specified offset from before of after the current row, respectively. - [FIRST_VALUE and LAST_VALUE: SQL window functions you should know](https://simplesqltutorials.com/first-value-and-last-value/) - The SQL Server FIRST_VALUE and LAST_VALUE window functions will return an element from the first or last row in a window frame, respectively. - [SQL Server Offset Window Functions: FIRST_VALUE, LAST_VALUE, LAG, LEAD](https://simplesqltutorials.com/sql-server-offset-window-functions/) - Offset functions allow us to return an element from a row that is at the beginning or end of a window frame, or is a defined offset from the current row. - [What's the difference between RANK and DENSE_RANK? Answered!](https://simplesqltutorials.com/difference-between-rank-and-dense_rank/) - The difference between RANK and DENSE_RANK is that DENSE_RANK considers distinctness when determining a rank value while regular RANK does not - [NTILE window function: How it works](https://simplesqltutorials.com/ntile/) - NTILE allows us to collect rows in a result set into a specified number of tiles, starting with 1 and ending with N. A tile is just a group of rows. - [DENSE_RANK: Explained with examples](https://simplesqltutorials.com/dense_rank/) - The SQL Server DENSE_RANK function is a simple window function you should know to help gather meaningful information from your data. The DENSE_RANK function is one of four ranking window functions we have available to us in Microsoft SQL Server. You should understand all the ranking window functions if you want to be a great - [SQL Server RANK: A window function you should know](https://simplesqltutorials.com/sql-server-rank/) - RANK will give you an integer value for every row in a partition. It will produce the same rank value for rows with the same ordering value. - [SQL Server ROW_NUMBER: A window function you should know](https://simplesqltutorials.com/sql-server-row-number/) - ROW_NUMBER is a window ranking function in SQL Server. It simply returns a unique incrementing integer for every row in a partition. - [SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE](https://simplesqltutorials.com/sql-server-ranking-window-functions/) - Window functions are very useful when querying data. The four SQL Server ranking window functions are ROW_NUMBER, RANK, DENSE_RANK, and NTILE. - [SQL Server Window Functions: An introduction for beginners](https://simplesqltutorials.com/sql-server-window-functions/) - Window functions in SQL Server allow you to combine aggregate information and also detail information into a single result set. - [SQL Server Custom Data type: A How-To Guide](https://simplesqltutorials.com/sql-server-custom-data-type/) - In SQL Server, we use the CREATE TYPE statement to create our own custom data type. This type can be used anywhere in the database. - [Need a SQL Server Boolean value? Use the BIT data type!](https://simplesqltutorials.com/sql-server-boolean/) - We can use the handy BIT data type as a SQL Server Boolean value. The BIT data type stores a value of either 1 or 0 (or NULL). It's perfect! - [Third Normal Form: A Guide for Beginners](https://simplesqltutorials.com/third-normal-form/) - In order for a table to be in Third Normal Form, it must first be in Second Normal Form, and it must not contain any transitive dependencies. - [Second Normal Form: A Beginner's Guide](https://simplesqltutorials.com/second-normal-form/) - To satisfy Second Normal Form, your table should follow the rules of First Normal Form, and it should not contain any partial dependencies. - [First Normal Form: An introduction to SQL table normalization](https://simplesqltutorials.com/first-normal-form/) - A table is in First Normal Form if all rows are unique, columns are atomic, there are no repeating groups, and data in each column is the same data type. - [SQL Server Foreign Key: Everything you need to know](https://simplesqltutorials.com/sql-server-foreign-key/) - One of the best ways to maintain referential integrity between tables in our database is to introduce SQL Server foreign key constraints. - [SQL Server Primary Key: How it works and 6 rules you need to know](https://simplesqltutorials.com/sql-server-primary-key/) - A proper understanding of primary key constraints is the first step towards understanding database normalization and design. - [Updatable View in SQL Server: A How-To Guide](https://simplesqltutorials.com/updateable-view/) - An updatable view is one we can perform INSERT, UPDATE, or DELETE operations against. There are a few simple rules you should know. - [Cannot update an IDENTITY column? Here's why](https://simplesqltutorials.com/cannot-update-an-identity-column/) - Sometimes we need to update an IDENTITY column value. Through the use of the IDENTITY_INSERT function, it can (sort of) be done. - [How to change a column name in SQL Server: Explained for Beginners](https://simplesqltutorials.com/sql-server-change-column-name/) - We use the SP_RENAME system stored procedure to change a column name in SQL Server. This procedure can be used to change other object names, too. - [SQL DECIMAL vs NUMERIC: What's the difference? (THERE IS NONE)](https://simplesqltutorials.com/sql-decimal-vs-numeric/) - SQL DECIMAL vs NUMERIC: There is no difference between DECIMAL and NUMERIC in SQL Server. They are synonyms and can be used interchangeably! - [SQL Server DECIMAL: Everything you need to know](https://simplesqltutorials.com/sql-server-decimal/) - The SQL Server DECIMAL data type is a very common data type you will see and use in your career as a database professional. - [SQL Server ROLLBACK: Everything you need to know](https://simplesqltutorials.com/sql-server-rollback/) - The ROLLBACK statement in SQL Server allows us to undo work done in error within a transaction, putting the data back in a consistent state. - [Stored procedure with parameters: A guide for beginners](https://simplesqltutorials.com/stored-procedure-with-parameters/) - If you need to write a stored procedure in SQL Server, odds are it will be a stored procedure with parameters. Learn how in this tutorial. - [SQL Server Unique Index: Everything you need to know](https://simplesqltutorials.com/sql-server-unique-index/) - A Unique Index is placed on a column to guarantee there will be no duplicate values within that column. Each value in that column will be unique. - [DATENAME function: Explained](https://simplesqltutorials.com/datename/) - The DATENAME function is a system function in Microsoft SQL Server we can use to present meaningful date information to the user. - [DATETIME2 Data Type: Explained](https://simplesqltutorials.com/datetime2/) - DATETIME2 is very common data type you should understand when it comes to developing databases. There are many differences between it and DATETIME. - [SQL Integer Data Types - Everything you need to know](https://simplesqltutorials.com/integer-data-types/) - Knowing the difference between all the integer data types in SQL Server is very important when it comes to the task of designing databases. - [Are you making this ROLLBACK mistake in your stored procedures?](https://simplesqltutorials.com/rollback-mistake-in-stored-procedure/) - If we need to issue a ROLLBACK in a stored procedure because of an error, how do we make sure only that ONE transaction is rolled back? - [Top 6 tools you should know for writing AWESOME Select statements](https://simplesqltutorials.com/top-6-tools-for-writing-select-statements/) - There are several tools available to us when writing SELECT statements. There are definitely a handful of tools seen and used more than others. - [SQL Server Transactions: Do you understand these 6 rules?](https://simplesqltutorials.com/6-rules-about-transactions/) - You might find yourself in a great amount of confusion if you don't know these basic rules about transactions. - [SQL Server Transactions: An Introduction for Beginners](https://simplesqltutorials.com/sql-server-transactions-for-beginners/) - A transaction contains a block of code we want to succeed in it's entirety. If one part of the code fails, we want all other parts to fail, too. - [SQL Server Index INCLUDE: Explained with Examples](https://simplesqltutorials.com/included-column-in-an-index/) - If a column is INCLUDED in a nonclustered index, a copy of that column data will be included next to the nonclustered index key value in the data structure of the nonclustered index, which helps SQL Server avoid doing a lookup. - [SQL Server Views: A Guide for Beginners](https://simplesqltutorials.com/sql-server-view/) - A SQL Server View is a virtual table object that pulls column data from one or more tables. A View can store a complex SQL query so that it can be ran repeatedly using simple commands. - [How to drop a constraint in SQL Server: Explained with Examples](https://simplesqltutorials.com/sql-server-drop-constraint/) - It's very easy to drop a constraint on a column in a table. This is something you might need to do if you find yourself needing to drop the column, for example. SQL Server simply will not let you drop a column if that column has any kind of constraint placed on it. In this - [How to find foreign key references in SQL Server: Just run this one query!](https://simplesqltutorials.com/sql-server-find-foreign-key-references/) - We can find foreign key references in our database by querying the sys.foreign_keys and sys.foreign_key_columns system tables. - [CAST and CONVERT: A How-To Guide](https://simplesqltutorials.com/cast-and-convert/) - The CAST and CONVERT functions are common tools you should know as a database developer. Which you use depends on the needs of your query. - [The B-Tree: How it works, and why you need to know](https://simplesqltutorials.com/the-b-tree/) - It is important to understand what the B-Tree is and how it is used with indexes to help our queries perform faster. - [Common Table Expression (CTE) in SQL: Everything you need to know](https://simplesqltutorials.com/common-table-expressions/) - Common Table Expressions (or CTE's) are temporary table expression that define an inner query whose result set will be used in the FROM clause of an outer query. - [Derived Table in SQL Server: Everything you need to know](https://simplesqltutorials.com/sql-server-derived-tables/) - A Derived Table is an inner query defined in the FROM clause of an outer query. The Derived Table falls out of scope when the outer query completes. - [SQL Server Table Expressions: The Ultimate Guide for Beginners](https://simplesqltutorials.com/table-expressions-guide-for-beginners/) - Table Expressions are basically named queries whose result set meets all the criteria of being it's own table, but is virtual. - [Nonclustered Index in SQL Server: A Beginner's Guide](https://simplesqltutorials.com/nonclustered-index-guide-for-beginners/) - A nonclustered index will store data in a different order than what is defined for the clustered index. which may make it easier to find rows we're looking for. - [SQL Server Clustered Index: The Ultimate Guide for the Complete Beginner](https://simplesqltutorials.com/sql-server-clustered-index/) - A clustered index is like a collection of DVD's ordered by Title. It's easy to find a specific DVD when they are ordered in this way. - [Scalar Valued Function: The Ultimate Guide for Beginners](https://simplesqltutorials.com/scalar-valued-functions-guide-for-beginners/) - Scalar Valued Functions are functions that return a single value back to the caller. They can be used anywhere a single value is expected. - [User Defined Functions in SQL Server: A Complete Guide](https://simplesqltutorials.com/sql-server-user-defined-functions/) - SQL Server User Defined Functions are database objects used to save a lengthy or complicated query so that it can be ran repeatedly using simple commands. As the name implies, they are created by users, and do not come with your SQL Server installation. - [Multi Statement Table Valued Function: The Ultimate Guide for Beginners](https://simplesqltutorials.com/multi-statement-table-valued-functions-guide-for-beginners/) - Multi Statement Table Valued functions return the result set of a table variable that is created, defined, and populated within the definition of the function. - [Inline Table Valued Function: The Ultimate Guide for Beginners](https://simplesqltutorials.com/inline-table-valued-functions-guide-for-beginners/) - Inline Table Valued Functions return a result set to the caller by way of an inner SELECT statement that is part of the function definition. - [How to Find Text in a SQL Server Stored Procedure: Just run this one query!](https://simplesqltutorials.com/sql-server-find-text-in-stored-procedure/) - There is one simple query we can run to find all references to a specific text within the definition of any database object. - [Stored Procedure Output Parameters: A Guide for Beginners](https://simplesqltutorials.com/stored-procedure-output-parameters/) - The trick to calling a stored procedure with an output parameters is to create an absorbing variable when you call the procedure. - [SQL Server Stored Procedures: The Ultimate Guide for Beginners](https://simplesqltutorials.com/stored-procedures-ultimate-guide-for-beginners/) - Stored procedures are database objects used to store complex SQL code that can be ran easily and repeatedly using simple commands. - [INNER JOIN in SQL Server: The Ultimate Guide for Beginners](https://simplesqltutorials.com/inner-join-ultimate-guide-for-beginners/) - An INNER JOIN is used in a SELECT statement to give you access to columns in two or more tables in order to present meaningful information to the user. - [What's the difference between JOIN and LEFT JOIN? This and other JOIN questions, answered!](https://simplesqltutorials.com/difference-between-join-and-left-join/) - The LEFT JOIN and the INNER JOIN are the most common types of JOINS you will likely see in your career as a database professional. - [LEFT JOIN in SQL Server: The Ultimate Guide for Beginners](https://simplesqltutorials.com/left-join-ultimate-guide-for-beginners/) - The LEFT JOIN will return column data for all rows in the first table of your JOIN (aka the 'left' table) and only column data for matching rows in the second table (aka the 'right' table). - [SQL Server character data types (and the differences between them)](https://simplesqltutorials.com/character-data-types/) - It is important to know the differences between the SQL Server character data types, and when certain data types should be used over others. - [How To Get a Running Total of Your SQL Data: Explained for Beginners](https://simplesqltutorials.com/sql-server-running-total/) - You can use the SUM() aggregate function within a Window Function to gather a running total of your data in Microsoft SQL Server. - [Dynamic SQL: Explained for Beginners](https://simplesqltutorials.com/dynamic-sql/) - There may be times when you need to write a query and you need to make certain parts of that query dynamic, meaning the query might not be exactly the same every time you run it. SQL Server has a great tool we can use to create these dynamic queries. In this very brief tutorial, - [SQL Server CASE Statement: A How-To Guide](https://simplesqltutorials.com/case-expression/) - There are two forms of the CASE expression: Simple and Searched. Both are used to return a single value based on the result of a condition. - [The ONE skill you should have if you want to be a Database Developer](https://simplesqltutorials.com/one-skill-you-should-have-to-be-a-database-developer/) - A person coming from a coding background can easily transfer their knowledge and skills to the field of database querying and development. - [SQL Server Cursors: A How-To Guide](https://simplesqltutorials.com/cursors/) - A cursor will iterate through rows of a result set, one by one, performing work on each row until there are no more rows in the result set. - [SQL Server WHILE loop: A decision structure you should know](https://simplesqltutorials.com/while-loop/) - The WHILE loop is used to execute a block of code repeatedly while some condition is true. The loop stops when the condition becomes false. - [SQL Server Wildcards: The 5 wildcards you need to know](https://simplesqltutorials.com/sql-server-wildcard/) - Wildcard characters are used in SQL Server to specifically look for character strings that match a specific pattern. - [The difference between COUNT(*) and COUNT(column): Answered!](https://simplesqltutorials.com/difference-between-count-and-count-column/) - COUNT(*) counts the number of rows per group, while COUNT(column) counts the number of non-NULL values in a specified column per group. - [SQL Server NULL: Are you making these 7 mistakes?](https://simplesqltutorials.com/7-mistakes-with-null/) - When you're first starting out with SQL Server, you might have these assumptions about NULL that are not quite right, or maybe flat-out WRONG - [UNION and UNION ALL: What's the difference?](https://simplesqltutorials.com/union-and-union-all/) - UNION will remove duplicate rows from your final result set, while UNION ALL will NOT remove duplicate rows from your final result set. - [The IF...ELSE IF....ELSE Statement: Everything You Should Know](https://simplesqltutorials.com/if-elseif-else/) - The IF...ELSE structure and the IF...ELSE IF...ELSE structure are the most basic SQL Server Decision Structures you should know. - [The TOP 10 SQL Server String Functions You Should Know!](https://simplesqltutorials.com/sql-server-string-functions/) - String manipulation is something you will likely find yourself doing at some point in your career as a database professional. Luckily, SQL Server has many great system functions we can use to make the task of string searching and manipulation very easy. In this week's tutorial, we'll cover the TOP 10 SQL Server string functions - [SQL Server DROP IF EXISTS: Explained with Examples](https://simplesqltutorials.com/sql-server-drop-if-exists/) - In SQL Server version 2016, Microsoft introduced an extremely handy tool called DROP IF EXISTS to make our database development lives so must easier. In this very brief tutorial, we'll walk through the syntax of the DROP IF EXISTS tool and show you a few examples of how to use it. We'll discuss these topics: - [The Top 7 SQL Server Best Practices You Should Follow!](https://simplesqltutorials.com/sql-server-best-practices/) - If you are in the process of learning how to query and develop SQL Server databases, congratulations, you are learning a skill that is extremely valuable in today's data driven climate. There are many great tools you can use when it comes to querying and developing Microsoft SQL Server databases, and there are many best - [SQL Server TRUNCATE TABLE: Everything you need to know](https://simplesqltutorials.com/sql-server-truncate-table/) - The TRUNCATE TABLE statement is a simple statement that has a very singular purpose in Microsoft SQL Server. In this very brief tutorial, we'll cover all the important information you need to know about the TRUNCATE TABLE statement. We'll cover these topics: What is the SQL Server TRUNCATE TABLE statement? The syntax for the TRUNCATE - [GROUP BY clause: A How-To Guide](https://simplesqltutorials.com/group-by-clause/) - The GROUP BY clause allows you to collect and summarize data according to whatever groups you decide. - [SQL Server Triggers: The Ultimate Guide for Beginners](https://simplesqltutorials.com/sql-server-triggers/) - SQL Server Triggers are some of the most helpful objects available to us in Microsoft SQL Server. A proper understanding of triggers is essential if you are looking to enter the field of database design and development. In this tutorial, we'll discuss everything you need to know about triggers in SQL Server. We'll discuss these - [IDENTITY Column in SQL Server: Everything you need to know](https://simplesqltutorials.com/sql-server-identity-column/) - The IDENTITY property is set on a column in a table to automatically generate a new integer value every time a row is inserted into that table. - [SQL Server UNION: Everything you need to know](https://simplesqltutorials.com/sql-server-union/) - The UNION set operator will combine the results of two or more SELECT statements into a single result set. - [SQL Server INTERSECT: Everything you need to know](https://simplesqltutorials.com/sql-server-intersect/) - The INTERSECT Set Operator uses SELECT queries to find overlapping data between two or more tables. - [SQL Server EXCEPT: Everything You Need to Know](https://simplesqltutorials.com/sql-server-except/) - The EXCEPT Set Operator will return rows from one result set that specifically are NOT in another result set. - [Set Operator Precedence: Explained](https://simplesqltutorials.com/set-operator-precedence/) - If you have multiple set operators in a query, certain set operators take precedence over others. INTERSECT has the highest precedence, while UNION and EXCEPT have the same precedence. - [HAVING Clause: How it works](https://simplesqltutorials.com/having-clause/) - The HAVING clause is used in combination with the GROUP BY clause. It is used to apply a filter to aggregation values in your result set. - [How to find a SQL Server column name: Run this one simple query!](https://simplesqltutorials.com/sql-server-find-column-name/) - We can use the SYS.ALL_COLUMNS system table to help us find a column name in our database. It's most useful when joining on the SYS.TABLES system table. - [How to reseed an IDENTITY value in SQL Server: A guide for beginners](https://simplesqltutorials.com/sql-server-reseed-identity/) - We can use the CHECKIDENT system function to reseed an IDENTITY value in Microsoft SQL Server. - [UNIQUEIDENTIFIER: Explained for Beginners](https://simplesqltutorials.com/uniqueidentifier/) - The UNIQUEIDENTIFIER data type stores unique values that are truly unique across space and time. It guarantees uniqueness even across databases. - [Set Operators in SQL Server: The Ultimate Guide for Beginners](https://simplesqltutorials.com/set-operators-in-sql/) - SQL Server Set Operators are used to combine the results of two or more SELECT queries into a single result set. - [John Sonmez Blogging Email Course: An Honest Review](https://simplesqltutorials.com/john-sonmez-blogging-email-course/) - If you're thinking about starting a programming blog, but don't know where to start, you should check out John Sonmez's blogging email course. ## Pages - [](https://simplesqltutorials.com/home/) - Maintain your data integrity and write accurate, efficient queries with help from Simple SQL Tutorials training and consulting services! Welcome to Simple SQL Tutorials! I hope I have helped you learn a thing or two about querying and developing SQL Server databases. My name is Josh, and I have been working with databases as an - [Products](https://simplesqltutorials.com/products/) - Need help with a Microsoft SQL Server topic? We have a variety of free and paid products to teach you everything you need to know! 1. Quintessential Queries: A complete guide on beginner to advanced querying tools in Microsoft SQL Server One of the most essential tasks of any data professional is to use SQL to query the data - [Dignified Databases EBook!](https://simplesqltutorials.com/dignified-databases-ebook/) - Understanding how to design and develop databases is crucial in the modern digital landscape. Databases serve as the backbone of information management in various industries. Proficiency in database development allows individuals to design and implement efficient data structures, ensuring the seamless storage, retrieval, and manipulation of information. In a world where data is often considered - [Blog](https://simplesqltutorials.com/blog/) - [Simple SQL Tutorials Data](https://simplesqltutorials.com/simple-sql-tutorials-data/) - Thank you for choosing Simple SQL tutorials as your source for T-SQL training! Click this link to be directed to a GitHub account that contains a backup of a database called SimpleSQLTutorialsData. This backup file was created in Microsoft SQL Server 2019. It can be restored to your local Microsoft SQL Server environment. The content - [Quintessential Queries EBook!](https://simplesqltutorials.com/quintessential-queries-ebook/) - One of the most essential tasks of any data professional is to use SQL to query the data in a database. We query databases to gather information to do things like analyze trends, summarize costs, see spending patterns, or make important business decisions. If you are just starting out with Microsoft SQL Server, you are - [Quintessential Queries Data](https://simplesqltutorials.com/quintessential-queries-data/) - Thank you for your purchase of Quintessential Queries! Click this link to be directed to a GitHub account that contains a backup of a database called QuintessentialQueriesSSQLT. This backup file was created in Microsoft SQL Server 2019. It can be restored to your local Microsoft SQL Server environment. The content in the Quintessential Queries book - [About](https://simplesqltutorials.com/about/) - Simple SQL Tutorials offers simplified, accurate training that breaks down complicated SQL Server topics into terms that are easy to understand. - [Privacy Policy](https://simplesqltutorials.com/privacy-policy/) - SimpleSQLTutorials.com website terms and conditions, as well as our privacy policy, can be found here. - [Subscribe!](https://simplesqltutorials.com/subscribe/) - Join my email list to receive special offers and notifications any time a new tutorial is released! - [Contact](https://simplesqltutorials.com/contact/) - If you need help with a topic related to querying or developing Microsoft SQL Server databases, please don't hesitate to send me a message! ## Categories - [Uncategorized](https://simplesqltutorials.com/category/uncategorized/) - [Stored Procedures](https://simplesqltutorials.com/category/stored-procedures/) - [Indexes](https://simplesqltutorials.com/category/indexes/) - [Views](https://simplesqltutorials.com/category/views/) - [Functions](https://simplesqltutorials.com/category/functions/) - [Identity Property](https://simplesqltutorials.com/category/identity-property/) - [Table Expressions](https://simplesqltutorials.com/category/table-expressions/) - [JOIN](https://simplesqltutorials.com/category/join/) - [Set Operators](https://simplesqltutorials.com/category/set-operators/) - [Data Types](https://simplesqltutorials.com/category/data-types/) - [The Basics](https://simplesqltutorials.com/category/the-basics/) - [Querying data](https://simplesqltutorials.com/category/querying-data/) - [Error Handling](https://simplesqltutorials.com/category/error-handling/) - [Transactions](https://simplesqltutorials.com/category/transactions/) - [Constraints](https://simplesqltutorials.com/category/constraints/) - [Data Definition Language](https://simplesqltutorials.com/category/data-definition-language/) - [Normalization](https://simplesqltutorials.com/category/normalization/) - [Window functions](https://simplesqltutorials.com/category/window-functions/) - [SQL Questions](https://simplesqltutorials.com/category/sql-questions/) - [Triggers](https://simplesqltutorials.com/category/triggers/) - [Developing Databases](https://simplesqltutorials.com/category/developing-databases/)