SQL Server T-SQL Tips
All Tips
Aggregate
- Concat Aggregates SQL Server CLR Function
- Group By in SQL Sever with CUBE, ROLLUP and GROUPING SETS Examples
- Multi parameter CLR aggregate in SQL Server 2005
- T-SQL: Capture Related Data and Return By Aggregates
Backward Compatibility
- Make your SQL Server database changes backward compatible when adding a new column
- Make your SQL Server database changes backward compatible when changing a relationship
- Make your SQL Server database changes backward compatible when dropping a column
- Make your SQL Server database changes backward compatible when renaming an entity
- Making SQL Server Function Changes Backwards Compatible
Calculations
- Avoiding erroneous results when using T-SQL Trigonometric Functions in SQL Server 2012
- Calculating and verifying financial values in SQL Server
- Calculating Mathematical Values in SQL Server
- Calculating the Pearson Product Moment Correlation Coefficient in T-SQL
- Getting a SQL Server RowCount Without doing a Table Scan
- How to Compute Simple Moving Averages with Time Series Data in SQL Server
- Min-Max Normalization in T-SQL when the Boundaries are not [0,1]
- Mining Time Series Data by Calculating Moving Averages with T-SQL Code in SQL Server
- Mining Time Series with Exponential Moving Averages in SQL Server
- Script to calculate the Median value for SQL Server data
- Select min and max values along with other SQL Server columns using T-SQL
- SQL Server T-SQL Code to Return Consecutive Values Over a Certain Value
- Summarize Metrics with SQL Server T-SQL Code
- T-SQL Bitwise Operators in SQL Server
- The SQL Server Numbers Table, Explained - Part 1
- The SQL Server Numbers Table, Explained - Part 2
- Transforming Cartesian Coordinates to Spherical Coordinates in SQL Server with T-SQL
- Use a CrossJoin to Calculate Multiple Pearson Correlation Coefficients in T-SQL
- Using a SQL Server Case Statement for IF/Else Logic
- Using T-SQL to find the Greatest Common Divisor (GCD) for any number of integers
- Using T-SQL to Perform Z-Score Column Normalization in SQL Server
- Weighted vs Simple Moving Average with SQL Server T-SQL Code
Case
- SQL Server CASE Expression Overview
- SQL Server CASE Statement Example
- SQL Server T-SQL CASE Statement Examples
Coalesce
Column
Comparison
- Compare SQL Server Features - Decimal vs Numeric, Timestamp vs Rowversion, Unique Index vs Unique Constraint
- Easy way to compare SQL Server table schemas
- Simple T-SQL Code Comparison With PowerShell
- SQL EXISTS vs IN vs JOIN Performance Comparison
- SQL Server IN vs EXISTS
Computed Columns
- Change All Computed Columns to Persisted in SQL Server
- Getting creative with Computed Columns in SQL Server
- What Effect Does Persisting a Computed Column Have in SQL Server?
- What Happens When a Computed Column is Persisted in SQL Server?
Concatentation
Configuration
- Determining SET Options for a Current Session in SQL Server
- Limit amount of data returned with the SQL Server TEXTSIZE command
Consolidation
Cursors
- Different Ways to Write a Cursor in SQL Server
- SQL Server Cursor Example
- SQL Server Loop through Table Rows without Cursor
Data
- A T-SQL Model for Contrasting Two Different Sets of Measurement
- Analyze Relationship Between Two Time Series in SQL Server
- Collecting Time Series Data for Stock Market with SQL Server
- Collecting, Storing, and Displaying Geographical Coordinates in SQL Server
- Compare Artificial Intelligence Models Built with T-SQL
- Create, Populate, and Transform Geographic Coordinates in SQL Server Tables
- Exponential Moving Average Calculation in SQL Server
- How to Use SQL Server Coalesce to Work with NULL Values
- Load Time Series Data with SQL Server
- Pass Historical Data for Stocks from Google Finance to SQL Server
- PowerShell and Text Mining Part I: Word Counts, Positions and Libraries
- Query for pair-wise relationships between rows for a SQL Server table
- Reporting Techniques With SQL Server Text Mining (Part II)
- SQL Server Data Mining Strategies for Stock Buy and Sell Recommendations
- SQL Server Min Max Column Normalization for Data Mining
- SQL Server Rowversion Functions min_active_row_version vs. @@DBTS
- String to Numeric Geographic Coordinates in SQL Server with T-SQL
- T-SQL to Calculate Buy and Sell Stock Recommendations via Three Technical Indicators
- Time Series Data Fact and Dimension Tables for SQL Server
- Two T-SQL Alternatives For Tracking Changes
- Using SQL Server Data Analysis for Stock Trading Strategies
- Using SQL Server ROLL UP to Get Similar Results of COMPUTE BY
- Using T-SQL to Detect Golden Crosses and Death Crosses
- Using T-SQL to find the shortest distance between two points
- Using T-SQL to Perform Decimal Scaling Normalization for SQL Server
- Using T-SQL to Refine an Analytical Model for Picking Stocks
Data Mining
- An Overview of Data Science for Stock Price Analysis with SQL: Part 1
- Analyze Historical Weather Data with SQL Server
- Analyzing When to Buy and Sell Stocks Using T-SQL in SQL Server
- Assessing with SQL and Data Science Goodness of Fit to Different Distributions
- Comparing with T-SQL and Excel a Decision Tree Regression to a Bagged Regression
- Contingency Table Analysis via Chi Square using T-SQL: Part 1
- How to Compare and Combine Artificial Intelligence Models with T-SQL
- How to Find Top N Largest Time Gaps in Date Ranges in SQL Server with T-SQL Code
- Implement K Nearest Neighbor Solution with T-SQL
- Mining Stock Price Time Series with MACD in SQL Server
- Multiple Regression Model Enhanced with Bagging
- One-way and Two-way Tabulation Example in SQL Server - Part 2
- T-SQL Code for Decision Tree Regression Algorithm - Part 3
- T-SQL Code for Multi-Level Decision Tree - Part 2
- T-SQL Code for the Decision Tree Algorithm - Part 1
- T-SQL Techniques For Mapping Ceteris Paribus Variables
- Using SQL to Assess Goodness of Fit to Normal and Uniform Distributions
- Using T-SQL to Assess Goodness of Fit to an Exponential Distribution
- Using T-SQL to Detect Stock Price Reversals with the RSI
- Using T-SQL to Quantify Trend Direction and Strength
- Using Two Samples to Validate MACD with T-SQL
- Weather Data Warehouse in SQL Server - Create and Populate
- Weather Data Warehouse in SQL Server - Gather Information
- Weather Station Tracking with SQL Server and Excel
Data Tier
Data Types
- Overview of WITH RESULT SETS Feature of SQL Server 2012
- SQL Server Bitwise operators to store multiple values in one column
Data Validation
Database
Database Configurations
Delete
- Best Practices For Deleting SQL Server Data
- Delete Files with SQL Server 2016 R Logic in T-SQL Code
- Deleting Data in SQL Server with TRUNCATE vs DELETE commands
- Differences between Delete and Truncate in SQL Server
- Script to Delete SQL Server Data in Batches
- SQL Drop Table Examples with T-SQL and SQL Server Management Studio
- SQL Server Stored Procedure to Safely Delete Data
- The T-SQL DELETE statement
Development
Distinct
Document Management
DROP TABLE
Duplicate Data
- Delete duplicate rows with no primary key on a SQL Server table
- Removing Duplicates from Strings in SQL Server
Dynamic
- Dynamically build a multi OR with LIKE query for SQL Server
- Dynamically Build SQL Server Insert, Update and Delete Statements with Excel
Error
Error Handling
Export
Foreign Keys
- Simulating ON DELETE CASCADE in SQL Server
- SQL Server Insert Parent and Child Records with One Statement
Formatting
Functions
- Find MAX value from multiple columns in a SQL Server table
- SQL Server Char Function and Reference Guide
- Write more compact SQL Server code using new features
- Write More Compact SQL Server Code Using New Features - Part 2
Hierarchies
- SQL Server and Excel Hierarchyid Example for Organization Charts
- SQL Server Hierarchyid Data Type Overview and Examples
- SQL Server Hierarchyid Example for Bill of Materials
Insert Into
Locking
- Comparison of SQL Server Serializable and Snapshot isolation levels
- READ_COMMITTED_SNAPSHOT and SNAPSHOT_ISOLATION levels in SQL Server
Merge
- Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE
- Merge parent and child SQL Server tables that use identity keys
- Resolving the MERGE statement attempted to UPDATE or DELETE the same row more than once error
- SQL Server 2008 MERGE More than UPSERT
- SQL Server MERGE statement usage and examples
- Use Caution with SQL Server's MERGE Statement
- Using MERGE in SQL Server to insert, update and delete at the same time
- Using the SQL Server MERGE Statement to Process Type 2 Slowly Changing Dimensions
Multi Server
OUTPUT
- SQL Server 2008 consume output directly from the OUTPUT command
- Using INSERT OUTPUT in a SQL Server Transaction
Overview
- Four SQL Server Syntax Rules I Always Follow
- Introduction to Subqueries in SQL Server
- SQL IN Operator
- SQL Server SELECT Examples
Performance
- Getting Started with SQL Server Indexing for Performance, Covering Indexes, Implicit Conversions and Parameter Sniffing
- Improve SQL Server Query Performance with Searchable Arguments
- Optimize Large SQL Server DML Processes by Using Batches
- Optimize Large SQL Server Insert, Update and Delete Processes by Using Batches
- Script to Capture Performance Stats When Testing SQL Server T-SQL Statements
- SQL Server CTE vs Temp Table vs Table Variable Performance Test
- SQL Server Query Performance for INSERT SELECT vs INSERT EXEC
- SQL Server T-SQL Developer Best Practices Tips- Part 2
- SQL Server T-SQL Performance Best Practices Tips - Part 1
- Testing SQL Server Performance of Database Inserts and Updates
Permissions
Pivot
- Script to create dynamic PIVOT queries in SQL Server
- SQL Server Dynamic PIVOT Query
- Use SQL Server's UNPIVOT operator to dynamically normalize output
- Use SQL Server's UNPIVOT operator to help normalize output
Plan Cache
R Language
Random Data
- A More Versatile SQL Server Random Number Function
- Executing Code Every Nth Percent of the Time with T-SQL
- Generating Random Numbers in SQL Server Without Collisions
- Selecting a Simple Random Sample from a SQL Server Database
- SQL Server Function to Generate Random Numbers
- SQL Server Random Sorted Result Set
- SQL Server stored procedure to generate random passwords
- Using SQL Server RAND Function Deep Dive
Result Set
- Compare SQL Server Results of Two Queries
- Rolling up multiple rows into a single row and column for SQL Server data
- SQL Server 2008 Group By Sets
Rollback
Scripts
- Execute a Command in the Context of Each Database in SQL Server - Part 2
- Execute a Command in the Context of Each Database in SQL Server using sp_ineachdb
- Generic Approach to Identify Modified SQL Server Rows
- Scripts to Automate Creating and Loading a SQL Server Table
- Scripts to Build a Network of Connected Records Using SQL Server
Search
Security
Services
Sorting
- Advanced Use of the SQL Server ORDER BY Clause
- Different Methods to Sort SQL Server Result Sets
- Sort Alphanumeric Values in SQL Server
Statistics
- A One-Way Analysis of Variance Test Add-on for the SQL Statistics Package
- A Two-Way Analysis of Variance Test Add-on for the SQL Statistics Package
- Artificial Intelligence Programming with T-SQL for Time Series Data
- Calculating Median Absolute Deviation with T-SQL Code in SQL Server
- Discovering Insights in SQL Server Data with Statistical Hypothesis Testing
- Finding Spreads in Data Ranges Using T-SQL
- Matrix Multiplication Calculated with T-SQL
- Performing a Sign Test with T-SQL
- SQL Server T-SQL Code for the Tukey Honestly Significant Difference Test After an ANOVA
- Statistical Parameter Estimation Examples in SQL Server and R
- T-Test Add-on for the SQL Statistics Package
- Using T-SQL for Finding Narrowing Ranges of Data
Storage
Strings
- A way to improve STRING_SPLIT in SQL Server - and you can help
- SQL Server LIKE Examples
- SQL Server LIKE Syntax with Wildcard Characters
- SQL Server SUBSTRING
System
- Auto Rename SQL Server Tables and Their References with PowerShell
- Dropping multiple SQL Server objects with a single DROP statement
- Renaming SQL Server database objects and changing object owners
- SQL Server Four part naming
- SQL Server sp_ prefix when and when not to use it
System Objects
T-SQL
Table
Temp Tables
- Create SQL Server temporary tables with the correct collation
- Differences between SQL Server temporary tables and table variables
- Exploring SQL Server 2014 Table Variables
- SQL Server Global Temporary Table Visibility
- SQL Server Performance of SELECT INTO vs INSERT INTO for temporary tables
- SQL Server Temp Table vs Table Variable Performance Testing
Tools
Troubleshooting
- Handling Transactions in Nested SQL Server Stored Procedures
- Problem using DISTINCT in case insensitive SQL Server databases
- Silent Truncation of SQL Server Data Inserts
- SQL Server work around for Msg 2714 There is already an object named #temp in the database
- Top 5 Reasons for Wrong Results in SQL Server
Tutorials
- Advice for Learning T-SQL SELECT Statement Step By Step
- Understand how SQL Server works to write better T-SQL queries
Update
- More efficient SQL Server UPDATE for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types with the WRITE Clause
- SQL UPDATE Statement
- Swap SQL Server column data due to inserting data into wrong columns
Variables
