Transact-SQL and SQL programming, in-memory OLTP processing, Machine Learning, Reporting Services, Analysis Services (OLAP), SQL Server 2019, SQL Server 2017, SQL Server 2016, SQL Server 2014, Microsoft SQL Server 2012, SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005 Articles and Tutorials

Microsoft released free SQL Server 2017 download for Windows, Linux and macOS.

Latest release of SQL Server Data Platform from Microsoft is SQL Server 2017 RTM release. Database administrators, SQL developers, Business Intelligence developers and Data Scientists can download SQL Server 2017 free and install on Windows, macOS or Linux distributions including Red Hat, Ubuntu and SUSE

Download SQL Server 2017 Developer Edition is full-featured free edition which can be used to learn, develop and test SQL Server features and capabilities

Free Microsoft SQL Server 2016 Download

Final release of SQL Server 2016, most recent data platform tool from Microsoft is released on 1st of June, 2016. Public available free SQL Server 2016 download (SQL Server 2016 Evaluation Edition and Developer Edition) is ready at Microsoft TechNet Evaluation Center and for MSDN Subscribers.

Download SQL Server 2014 Free Trial Version

Download SQL Server 2014 new memory optimized database for in-memory processing OLTP data applications for administrators, programmers and Business Intelligence developers

SQL Server 2019

Download SQL Server 2019 Free Edition

Download most recent data platform SQL Server 2019 as the number one unified data platform for companies with PolyBase, Apache Spark and Hadoop Distributed File System (HDFS) feature.

SQL Server and AWS CLI

Export SQL Server Data as CSV Files and Migrate to Amazon S3 Bucket using AWS CLI

Database tutorial shows how to export SQL Server database table data in CSV format into multiple files and migrate to Amazon S3 buckets with AWS CLI copy command in SQL job using xp_cmdshell

SQL Server and AWS CLI

Execute AWS CLI Command using xp_cmdshell on SQL Server Database

SQL Server tutorial shows how database programmers can run AWS CLI commands using SQL xp_cmdshell procedure to copy local files into Amazon S3 bucket folders

SQL Server to Amazon Redshift Database

Provider Does Not Expose The Necessary Interfaces to Use a Catalog or Schema

Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema

SQL Server to Exasol Database

SQL Server Linked Server to Exasol MSDASQL Provider Errors

When executing SQL queries on Exasol database from SQL Server using Linked Server with MSDASQL OLE DB provider, different types of SQL errors can be experienced by database developers.

SQL Server

SQL Server Remote Procedure Time Out of 600 Seconds Exceeded

For solving the remote procedure time out error, SQL Server database administrators can configure system parameters as shown in this SQL tutorial.

SQL Server to Exasol Database

Connect SQL Server to Exasol Database using Linked Server

SQL Server database tutorial shows where to download Exasol ODBC Driver, how to install Exasol ODBC driver and create Linked Server to connect Exasol on SQL Server data platform

Linked Server ODBC Connection

Conversion of parameter/column from data type VARCHAR1 to ASCII failed

ODBC connection CHAR_AS_UTF8 property setting for conversion of data type of VARCHAR1 to ASCII errors on SQL Server Linked Server query connected to SAP HANA database

SQL Server OpenQuery

Workaround to Pass Parameter to OpenQuery using Linked Server on SQL Server

SQL tutorial shows a method to create a SQL View based on stored procedure which is fetched by a loopback LinkedServer and calling an OpenQuery code and enables use of system parameters with OpenQuery

SQL Server 2019

Install and Run SQL Server 2019 on MacOS using Docker Container

This tutorial shows how to install SQL Server 2019 database on Mac OS using Docker Container image and to connect to MSSQL database using Azure Data Studio and query SQL data

SQL Server 2019

MacOS Üzerine Docker ile SQL Server 2019 Kurulumu ve Çalıştırılması

Bu yazıda Microsoft SQL Server 2019 veritabanı uygulamasını Docker kullanarak Mac üzerinde kurmayı ve Azure Data Studio uygulamasını indirerek Docker container vasıtası ile kurduğumuz MSSQL veritabanına bağlanmayı göreceğiz

Power BI Desktop

Analyze Power BI Query Performance using SQL Server Profiler

Especially for self-service BI or Power BI is one of the most common reporting tools used by data professionals. This Power BI tutorial shows how report developers can use Power BI tools for performance analysis of their reports especially for SQL query performance on data source.

SQL Server Port Number

Find the Port Number for SQL Server Instance

Default port number for SQL Server connections is 1433. On the other hand, on purpose SQL Server administrators may change the default port number for their SQL Server instances.

Data Virtuality

Connect SQL Server on Data Virtuality Studio

This tutorial shows how to connect to SQL Server database from Data Virtuality Studio, logical data warehouse software.

SQL Server Linked Server

EXECUTE permission was denied on the object xp_prop_oledb_provider

When SQL programmer tries to extend the Catalogs node, SQL engine may raise exception "The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'."

Linked Server to Amazon Redshift

Connect to Amazon Redshift from SQL Server using Linked Server with ODBC Data Source

Database developers can integrate SQL Server and Amazon Redshift databases using SQL Server Linked Server configuration via System DSN with ODBC drivers.

SQL Query Tips

SQL Min() and Max() Aggregation Functions with Partition By Clause

SQL tutorial shows how SQL aggregation functions Max() and Min() are used with Partition By clause in a Transact-SQL query for SQL Server database developers

SQL Server Machine Learning Services - R and Python Script

SQL Server R and Python Script Write to File Folder Permission Error

Using R script or Python script, I want to write data or text to a file folder. But when I execute my R script or Python script using sp_execute_external_script, I always get an error message indicating cannot open the connection, PermissionError or Permission denied, etc.

SQL Server User-Defined Data Type - Default

Default Values for SQL Data Types in SQL Server Database

SQL Server programmers can create user defined data types, create defaults (database default value objects) and bind default values to user defined data types.

SQL Server Machine Learning Services - R Script

List Installed R Packages on SQL Server

Data scientist can get list of all installed R packages on SQL Server instance using installed.packages function with sp_execute_external_script and Rterm tool

SQL Server Machine Learning Services - R Script

Install R Packages on SQL Server for Data Scientists

This SQL Server Machine Learning Services tutorial shows how database programmers and data scientists can install R packages on a SQL Server instance

SQL Server Data Islands of Nodes and Edges

Detect Data Islands of Nodes and Edges using SQL on SQL Server

SQL tutorial providing a solution for SQL Server database developers showing how to group nodes which are connected via edges into data islands

SQL Server UDF Functions

User Defined SQL Split String Function for Database Developer

SQL Server database developers can use given SQL user defined scalar function and table valued function codes to split string variables in their developments

SQL Server Machine Learning Services - R Script

SQL R Library RODBC Script to Connect to SQL Server

One of the methods for R developers on SQL Server to connect to SQL Server databases in order to execute SQL commands and query data is using RODBC R library. RODBC package simply provides an ODBC database connectivity from R scripts to data developers

SQL Server Machine Learning Services - R Script

SQL Server was unable to communicate with the LaunchPad service for request id

When I execute an R Script on SQL Server I got following SQL error message: SQL Server was unable to communicate with the LaunchPad service for request id

FileStream and FileTable

SQL Server FileStream Errors and Configuration Steps for FileTable Creation

SQL Server FileStream feature and FileTable for storing binary files in SQL database which enables data consistency with database tables and binary image or files is one of the most enjoyed features of SQL Server

SQL Server Tools

Export Data From One Database Table to Other Database

It is possible to transfer table data from one SQL Server database to another database easily using SQL Server Management Studio tool Import and Export Wizard

SQL Sum Aggregation Function

SQL SUM function to Calculate Sum of Top N Rows using Sum Aggregation Function with Over and Rows Clause

SQL tutorial shows how SQL Sum() function is used with OVER clause and ROWS clause to calculate sum of a table column for a number of data rows like TOP 10 rows, etc.

SQL Query for Overlapping Time Periods

SQL Queries for Overlapping Time Periods on SQL Server

SQL developers working on overlapping time periods can use SQL queries and query codes shared in this SQL Server database tutorial to determine boundary limits of time intervals forming an overlapping chain

Azure Data Studio

Azure Data Studio for SQL Server, Features and Installation

Just like SQL Server Management Studio, Azure Data Studio is an alternative data management tool for data sources like SQL Server, Azure SQL DB

SQL Server 2019

SQL Server 2019 Installation to Setup a New Instance

This setup guide introduces SQL Server 2019 installation steps for SQL developer and database administrators.

Parse JSON on SQL Server 2017

Parse JSON Response of Amazon Transcribe Service using SQL OpenJSON

On SQL Server 2017 using SQL OpenJSON command query database developer can parse JSON response of AWS Amazon Transcribe service used to convert speech to text and return JSON string data in tabular format

Data Islands and Gaps in Data

SQL Query to Detect Data Islands and Gaps in Data with Boundary Values

To detect data islands and gaps and their boundaries is a popular problem for SQL database developers. In this SQL tutorial, I want to show how data islands can easily identified by database programmers using Row_Number() function and Min() and Max() With Partititon By clause.

SQL Server XML Query

Query Comma Seperated List using SQL XML

Using SQL XML query Exist() function, SQL Server database developers can search for specific values in a concatenated comma seperated list stored in a table column. Although splitting string list and filtering concatenated values is an option, SQL Server XML Exist function is an advanced solution.

SQL Server JSON Support

Query JSON Data using OpenJSON on SQL Server

With SQL Server 2016 and SQL Server 2017, database programmers can query JSON data in their SQL codes using build-in OpenJSON SQL table valued function.

Longest Path using SQL

Find The Longest Path Between Two Nodes using SQL Server Recursive CTE Query

Database SQL developers can model to find the longest path in a graph problem on SQL Server using nodes and edge lengths and build a SQL Server recursive CTE query to find the routes from start node to end node as shared in this SQL tutorial.

SQL Server CLR

Check If SQL Server Database is TrustWorthy for CLR Creation

To create CLR objects like User-Defined Functions, Stored Procedure, Triggers on SQL Server database, enabling TrustWorthy property of the database is one option for passing security issues for SQL developers. This guides shows how SQL Server professionals can check whether the database is set as TrustWorthy or not.

SQL Server Contained Databases

sp_configure Contained Database Authentication for SQL Server Database Attachment

The sp_configure value 'contained database authentication' must be set to 1 in order to create a contained database or attach data files of a contained database on SQL Server 2017

SQL Server and R Scripts

Generate Random Numbers using R Script on SQL Server

For database developers it is possible to use R script on SQL Server to generate random numbers. With SQL Server 2016 and SQL Server 2017 Machine Learning Services (either In-Database or as Standalone), data engineers are able to run R scripts and Python (new in SQL Server 2017) within T-SQL codes

R and Python Scripts

Enable sp_execute_external_script to Run Python or R Script on SQL Server

Data engineeers can execute R scripts or Python scripts after they enable sp_execute_external_script stored procedure on SQL Server by using sp_configure to modify "external scripts enabled" system configuration variable

Transact-SQL

Get Concatenated Column List of Database Tables on SQL Server

Transact-SQL developers working on SQL Server 2017 can use String_Agg function for aggregating string table column values grouped by using "Within Group" option and create SQL Create Table scripts for database tables

Transact-SQL

Check If Temporary Table or Temp Table Exists in SQL Server Database

SQL Server database programmers frequently create temporary tables and before creating temp table, T-SQL developer has to drop temp table after they validate temp table already exists on the database.

SQL Server DDL Trigger

Prevent File Growth Database Property Changes using SQL Server DDL Trigger

SQL Server database administrators can prevent for example developers to change file growth property of a database using server based DDL trigger which SQL source codes are shared in this SQL Server tutorial.

SQL Server 2017

String Concatenation in SQL Server 2017 with String_Agg Function

For string concatenation in SQL Server, developers can use string aggregation String_Agg function with SQL Server 2017. Before SQL Server 2017 String_Agg string function to concatenate string values, either CONCAT string function or "+" expression was used. If the SQL developer wants to concatenate text column values of different rows of a table, then user-defined string concatenation SQL functions were developed

SQL Server 2017

Installing SQL Server 2017

Within this SQL Server setup guide I will demonstrate step by step SQL Server 2017 installation as a stand-alone server also as a named instance only for Database Engine setup

SQL Trigger

Correct Invalid or Misspelled Values using SQL Server Trigger

On SQL Server using a database table for invalid forms and typos and correct values of string entries, I managed to update table entries dynamically with correct values using a database table trigger as I share SQL codes in this tutorial.

SQL Server System Views

Download SQL Server System Views Poster

SQL Server database administrators or T-SQL developers download SQL Server system views poster because data platform professionals frequently use system views map for relations between systems views and for view columns.

SQL Programming

Select Combinations of Data using SQL Query

Database developers can build SQL Select query to generate and return all possible combinations of data in SQL Server by using method shown in this SQL tutorial with cross joins and applying a simple logic.

SQL Server 2016 Sample Databases

Restore SQL Server 2016 Sample Database WideWorldImporters

Database professionals who want to try SQL Server 2016 can download sample database WideWorldImporters and install sample database or restore it from backup file as shown in this SQL tutorial.

SQL Server 2016 Reporting Services

Create SQL Server 2016 Reporting Services Reports on SQL Server Data Tools

This SQL Server Reporting Services tutorial shows business intelligence developers how to create their first report using SQL Server Data Tools 2015 for SQL Server 2016 Reporting Services.

SQL Server 2016 Sample Databases

Download SQL Server 2016 Sample Databases

Download sample databases for SQL Server 2016 including AdventureWorks and WideWorldImporters sample databases.

SQL Server Tutorial

Create Schema and Table on other SQL Server Database

SQL developers can create database schema and table by using Transact-SQL scripts on SQL Server databases other than current database. In this SQL tutorial, I will show how to create database schema on an other database automatically by running a SQL script easily.

Fully Qualified Name

Get Fully Qualified Name of Database Object in SQL Server

To find fully qualified name of a database table in SQL Server, developers can use getFullyQualifiedName SQL function to read FQN formed of schema, database and server name together with table name.

Partition Database Table

Partition Table Monthly Bases using Computed Column in SQL Server Database

Database developers can partition a SQL Server database table according to months using computed column in table and partition scheme and partition function as shown in this SQL tutorial.

Create Database from Backup

Create Database from Backup on SQL Server

This SQL Server tutorial shows how SQL database administrators and Transact-SQL developers can create new database from backup file using Restore task on SQL Server Management Studio.

SQL Cursor Sample

List Count of Rows in All Tables in Database using SQL Server Cursor

This tutorial shows T-SQL developers SQL cursor example code to list number of rows (record counts) in all database tables in a SQL Server database

User Defined SQL Function

Remove Numeric Characters in String using SQL

SQL programmers can remove numeric characters in string expressions using SQL to fetch only remaining character values shown in this tutorial.

Prevent SQL Injection

Prevent SQL Injection using Parameterized Query in SQL Command

SQL Server database applications are vulnerable by SQL injection methods if SQL commands are build dynamically without parameterized SQLCommand is used.

SQL Server Database Snapshot

Create Snapshot for Databases with Multiple Data Files

To create snapshot for databases with multiple data files could be difficult at least for the SQL syntax for SQL developer and SQL Server database administrators.

SQL Server System Views

Check Existence of Snapshot for a Database on SQL Server

SQL tutorial shows how to find if a database snapshot is already created for a specific SQL Server database and shares SQL codes showing the source database for a snapshot.

SQL Server System Views

List Data Files for All Databases on SQL Server

sys.master_files lists all data files of all databases on current SQL Server instance on the other hand sys.database_files lists only database data files for current database where the query is executed on.

SQL Server Database Snapshot

Create Database Snapshot for all Databases on SQL Server

SQL Server tutorial shares SQL script to create database snapshots for all databases on a SQL Server instance for database administrators. If you require to create snapshots for each database created on a given SQL Server, you can use SQL script which use sp_Msforeachdb undocumented stored procedure with an other stored procedure which is used to create database snaphot for a specific database.

SQL Server Database Snapshot

Refresh Database Snapshot on SQL Server

SQL Server database snapshots are readonly storing data which was on source database at the creation time of snapshot. To refresh snapshot database on SQL Server with changed data on source, database administrators have to drop and re-create the snapshot database.

SQL Server CLR Stored Procedure

Read File Properties using SQL Server CLR Stored Procedure

SQL Server CLR tutorial shows how to read file properties using SQL CLR stored procedure created by using given Visual Studio CLR project code samples for database programmer and administrators

SQL Server Management Objects

SQL Server Management Objects SMO to Generate Database Table Scripts

SQL Server Management Objects (SMO) provides a collection of objects like databases, tables, scripter, etc. required for managing Microsoft SQL Server through programming like in this SQL tutorial shows to generate database table scripts.

SQL Server DDL Trigger

Log Who Drops Table in SQL Server Database with DDL Trigger

SQL Server database administrator can log dropped tables for auditing to find out who drop database table using DDL triggers on database level for DROP_TABLE event.

SQL Server Cursor Sample

Sample SQL Cursor Code on SQL Server

SQL Server cursor sample code to loop through all records as a result of SQL Cursor select query enabling developer to execute stored procedure for each row

SQL Server CLR Function

Generate Random Integer Number using SQL Server CLR Function

SQL Server developers can use SQL CLR function to create random number as random generation. This SQL CLR tutorial shows how to create a CLR project using Visual Studio.

SQL Server CLR Function

SQL Server CLR Split String Function for 2-Dimensional Array

Transact-SQL programmer can develop assemlies in VB.NET or in C# and create CLR functions in SQL Server to split string expressions with better performance. This SQL Server CLR tutorial shows how to create a SQL CLR function which returns a table by splitting a two dimensional input string.

SQL Server 2016

SQL Server 2016 Split String Function STRING_SPLIT

Transact-SQL STRING_SPLIT function is used to split string expressions using defined seperator character which is new with SQL Server 2016

SQL Server 2016

Download and Install SQL Server 2016 Management Studio (SSMS)

With SQL Server 2016, SQL Server Management Studio is not part of the stand-alone SQL Server 2016 installation process anymore. In order to install SQL Server Management Studio (SSMS), download and install SQL Server Management Tools.

Excel Import from SQL Server

Import Data from SQL Server in Excel Document using Microsot Query

Microsoft Query enables Excel users to import data from SQL Server into Excel document using a wizard easily in a few steps.

SQL Server Jobs

Create SQL Server Job to Run Periodic Tasks

To execute periodic tasks on SQL Server, database administrators and T-SQL developers create SQL jobs using SQL Server Management Studio as shown step by step in this SQL tutorial.

Connect SAP Lumira to SQL Server Database

Download JDBC Drivers to Connect SAP Lumira to SQL Server

Download JDBC drivers to connect SQL Server databases from SAP Lumira, for reports with data coming from SQL Server and created by using Query with SQL source type

SQL Server Database Collation

Could not find stored procedure 'sp_executeSQL'.

SQL developers may experience Could not find stored procedure 'sp_executeSQL'. error when executing sp_executeSQL procedure on a SQL Server database with case sensitive collation.

SQL Tools for Decryption

Decrypt Encrypted Stored Procedure using SQL Server Tool

To decrypt an encrypted stored procedure, trigger, SQL view or user defined function on SQL Server database is not possible without using third-party tools like dbForge SQL Decryptor.

SQL Server Encryption

Encrypt SQL Stored Procedures, SQL Views and User Functions on SQL Server

SQL programmers may require to encrypt SQL source code of stored procedures, SQL views, user defined function in their databases to prevent the sensitive data structures easily be obtained by unauthorized users. Sometimes even database administrators encrypt SQL objects so that the SQL Server developers will not be able to see the code running within that SQL stored procedure or user defined function, SQL views, etc.

SQL Server Tools

SQL Server Data Quality Service Client Tool

SQL Server Data Quality Service provides tools for database administrators, BI professionals and SQL developers to maintain the data quality, like preventing duplicate values or protecting singularity of a specific value against different forms of it, or against mistypings, etc.

SQL Server FileTable

FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL

To create a FileTable in the database, set the DIRECTORY_NAME option to a non-NULL value using ALTER DATABASE. Otherwise, SQL Server engine will throw an error.

Memory-Optimized Tables

Cannot create memory optimized tables in a database on SQL Server 2014

SQL Server 2014 enables database administrators to create memory optimized table if memory-optmized database file group and filestream database file is created appropriately.

SQL Server Tutorial

Find Foreign Key Names created for SQL Server Database Table

SQL Server database administrator and T-SQL programmers can query sys.foreign_keys SQL system view to list and find foreign key check constraints on a database table. SQL Server system catalog view sys.foreign_keys is the right place for identifying and listing foreign keys and foreign key names created on a SQL database table or referencing to a specific SQL table

SQL Server 2016 JSON Support

SQL Server 2016 OpenJSON Error

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

SQL Server 2016 JSON Support

SQL Server 2016 JSON Support for Database Developers

SQL Server 2016 introduce JSON support for SQL Server developers by extending SQL SELECT queries with FOR JSON AUTO and FOR JSON PATH options.

SQL Server Triggers

List of SQL Server Triggers created on Database Tables

SQL Server database administrators and T-SQL developers can query sys.triggers system catalog view for database table triggers and table names. This SQL tutorial shares the source codes of a SQL query that list all triggers created on database tables with the table names that they are created on

Prevent Truncate Table

Prevent Truncate Table using Foreign Key Constraint on SQL Server

To prevent Truncate Table command to delete all data in a database table SQL Server database administrator and SQL developer can create Foreign Key Constraint referencing master table from a dummy table.

SQL Server Instead of Delete Trigger

SQL Server Instead of Delete Trigger to Prevent Data Deletion

SQL Server database administrators or SQL programmers use Instead of Delete trigger to prevent data deletion from database tables like look-up table or master data table. In this SQL tutorial, I want to share how to create a SQL Server Instead of Delete trigger on a database table which prevents accidentally data deletions by executing "Delete From table" command.

Order By in SQL Server View Object

How to Create SQL View with Order By Clause

SQL Server The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. error occurs when database developer tries to use Order By clause in the definition of a SQL view. This SQL tutorial shows how to create SQL views with Order By clause using TOP 100 Percent or Offset 0 Rows for Transact-SQL programmers and SQL Server database administrators.

SQL Server Analytical Lag Function

SQL Server Lag Function to Group Table Rows on Column Value Changes

This SQL Server tutorial shows database developers how to use SQL Lag() function in order to group subsequent table rows on changes of a specific column value. And then database programmers will use SQL Server aggregate functions like max(), min(), sum() and count() with "partition by" clause to find count or rows, minimum or maximum value of a row data, or sum of a column of each group.

SQL Server 2014 Semantic Search

Full-Text Index and Semantic Search in SQL Server 2014

SQL Server 2014 Statistical Semantic Search functions use indexes created by Full-Text Search. SQL Server semantic search queries can be used to figure out key phrases in a database table text column. Administators and SQL developers can create Transact-SQL statements to identify key words in a table row column and find similar or related data by using identified keyphrases or keywords.

SQL Server 2016 Temp DB Files

Set Number of Processor Cores for Number of Temp DB Files on SQL Server 2016

VMWare Player enables users to set the number of processor cores on a virtual machine configuration which limits the maximum number of tempdb files. SQL Server database administrators and developers can define the number of temp db file during SQL Server 2016 setup. Multiple Temp DB file is a new enhancement for database professionals introduced with SQL Server 2016.

Microsoft .NET Framework 3.5 Service Pack 1 is Required for SQL Server 2016 Installation

How to install Microsoft .NET Framework 3.5 Service Pack 1

Microsoft .NET Framework 3.5 Service Pack 1 is required for SQL Server 2016 installation on Windows 10. This tutorial shows how to install Microsoft .NET Framework 3.5 Service Pack 1 by downloading Framework 3.5 SP1 web installer and execute it successfully for completing the installation of SQL Server 2016.

SQL Server 2016 Setup Error

Oracle JRE 7 Update 51 Required for SQL Server 2016 Setup

Microsoft SQL Server 2016 installation, the next generation database platform of Microsoft SQL Server vNext setup requires Oracle Java Runtime Environment 7 update on Windows 10. Before proceeding with setup process during controlling of the requirements based on selected SQL Server 2016 features on Feature Rules steps of the installation wizard, Rule "Oracle JRE Update 51 (64-bit) or higher is required" failed. error may prevent a successfull setup.

Visual Studio SQL Server Database Project Tools

Create SQL Server Database Object Script using Visual Studio 2015

Visual Studio SQL Server Database Project enables to generate database object scripts as an alternative to SQL Server Management Studio built-in tools to generate scripts of database objects including database tables, views, procedures, etc for database administrator and T-SQL developers.

SQL Server 2016 Temporal Table

Create Test Data for Temporal Table on SQL Server 2016

In this SQL tutorial, database developers will find a method how to create test data for temporal table (system-versioned table) and history table in SQL Server 2016 database.

SQL Server 2016 Temporal Table

Create SQL Server 2016 Temporal Table and History Table

SQL Server 2016 introduce temporal table aka system-versioned temporal table which contains current data and historical data to query data which changes with time. SQL Server 2016 temporal features enable SQL developers to query dynamic data at a particular point of time.

SQL Server 2016 Installation

SQL Server 2016 Installation on Windows 10

This guide shows how to install SQL Server 2016 on Windows 10 using VMWare Player step by step. SQL Server 2016 installation is straight-forward except Microsoft .Net Framework 3.5 SP1 setup especially where internet connection does not exist.

SQL Server Tutorial

Find SQL Server Views Where Table is used and List Tables in a View

To query database objects metadata in which SQL views a table is used or which tables are used in a SQL Server database view SQL Server database administrator and developers can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE system view.

Transact-SQL Tutorial for SQL Server Date Functions

Create Monthly Calendar using SQL in SQL Server

This T-SQL tutorial shares SQL codes to create monthly calendar using SQL Server datetime functions like emonth, datepart,dateadd, datename, recursive CTE queries, etc.

SQL Tutorial

Get Day Names and Month Names in Different Language

Set Language SQL command and DateName built-in SQL datetime function enables developers to get day names and month names in specific language.

SQL Server Tutorial

Enable Resource Governor on SQL Server 2014

To enable Resource Governor SQL Server database administrators can use SQL Server Management Studio or execute SQL commands to manage Resource Governor to enable or disable.

Transact-SQL for SQL Server Database Objects

Get Table Column Names List in SQL Server by Code

SQL tutorial shows database developers how to get columns names list of a database table programmatically in SQL Server using various methods like executing queryies on system catalog views like sys.columns or information schema views, or running system stored procedure sp_columns

SQL Server Data Tools for BI Development on Visual Studio 2013

SQL Server Data Tools Installation for BI Development on Visual Studio

Business Intelligence developers require Microsoft SQL Server Data Tools installation for BI development with Visual Studio 2013 on SQL Server 2014 instance. With SQL Server Data Tools setup, Business Intelligence developers can use the BI project templates for SQL Server 2014 Analysis Services, Integration Services and Reporting Services with Visual Studio 2013 IDE.

Import Text File Data to SQL Server

Upload Text File to SQL Server Database Table

This SQL tutorial shows how to upload a password list stored in text file to SQL Server database table. Tutorial uses SQL Server Bulk Insert command to insert text data into sql table as rows

SQL Server 2014 Data Compression Tool

Compress Table Data using Data Compression Tool or SQL Scripts in SQL Server 2014

This SQL tutorial will be showing SQL Server Management Studio tool, "Data Compression Wizard" and sharing the SQL scripts created by this SQL Server tool that can be executed for other database tables with different data compression options like Row or Page based data compression.

Download Sample Database AdventureWorks2014 for SQL Server 2014

Drop failed for ResourcePool: Remove all bindings

SQL Server 2014 "Drop failed for ResourcePool" error with description "Cannot drop resource pool 'Pool_DatabaseName' because it is bound to a database. Remove all bindings to this resource pool before dropping it." is one of the problems I experienced while setting up SQL Server in-Memory database on SQL Server 2014 using the sample AdventureWorks2014 database

Download Sample Database AdventureWorks2014 for SQL Server 2014

Download SQL Server 2014 Sample Database AdventureWorks2014

Download sample database AdventureWorks2014 for SQL Server 2014 after you install SQL Server 2014 in-Memory Database Server. Sample database for SQL Server 2014 will enable T-SQL developers and database professionals to test the new features of the most recent SQL Server version with pre-configured data.

SQL Server 2014 Features

SQL Server 2014 Buffer Pool Extension

Buffer Pool Extension is one of new features of SQL Server 2014 to increase SQL Server database performance by increasing amount of cache that SQL Server can use.

SQL Random Password Generator

SQL Password Generator to Create Random Password

To create random password in SQL Server, T-SQL developer can use random password generator stored procedure source codes shared in this SQL tutorial. If programmers require to initialize or create random passwords for applications (like in registration), by customizing password generation process in Generate_Password stored procedure they can cover different password complexity requirements for randomly generated passwords

SQL Server Tools

SQL Server Export Query Results to Excel with Column Names

This SQL Server tutorial shows how to export query results to Excel with column names in SQL Server Management Studio 2014

SQL Server Tools

SQL Server Dedicated Administrator Connection DAC Tool for Database Administrators

SQL Server dedicated administrator connection DAC tool for database administrators enables them to connect a SQL Server instance when standard SQL Server database connections fail due to an error on the server.

SQL Server Programming

Use SQL to Find Missing Numbers and Gaps in Sequence of Numbers like Identity Column

This SQL tutorial shows how to use SQL to find missing numbers in a sequence column or find gaps in numbers like the gaps in an identity column of a SQL Server database table.

SQL Server In-Memory Database in SQL Server 2014

Create In-Memory Database in SQL Server 2014

This SQL Server 2014 tutorial shows to create SQL Server in-memory database and issues to consider like MEMORY_OPTIMIZED_DATA file group and *_BIN2 collation for indexes on memory optimized tables

SQL Server 2014 - SQL Server In-Memory Database

Nullable columns in the index key are not supported with indexes on memory optimized tables

SQL Server 2014 in-memory database supports memory optimized table creation with indexes on columns which are NOT NULL. A primary key constraint or a SQL index on a nullable column can not be created on memory optimized table

SQL Tutorial on Row_Number() and SQL CASE Statement

Display Data in Multiple Columns using SQL

Displaying data in multiple columns is not a difficult task if you know how to approach the problem. This SQL tutorial will share a method using SQL Row_Number() function and SQL CASE conditional statements in order to fulfill this task.

SQL Function for Turkish Developers to Convert Numbers to Words

Sayıyı Yazıya çeviren SQL fonksiyonu

This SQL tutorial is in Turkish and provides a SQL function to convert numbers into words in Turkish.

Bu yazıda örnek bir sayıyı yazıya çeviren SQL fonksiyonu kodlarını SQL Server üzerinde geliştirme yapan SQL programcıları ile paylaştım.

SQL Table-Valued Function for SQL Split

Split String Into Fixed Length Pieces in SQL using SQL Split Function

This SQL tutorial will provide a user defined SQL function which splits given input string in desired length pieces and returns within a table structure. SQL split string function is created as a table-valued function which returns a table structure with lines populated with string parts.

Transact-SQL Programming

SQL COUNT() and ROW_NUMBER() Function with PARTITION BY for 1 of n Items

This SQL tutorial demonstrates usage of SQL COUNT() and SQL ROW_NUMBER() function to select a row with its order number and total number of rows in a group in the format "1 of n" items, like first page of a total 3 pages, 1/3, 1 of 3, etc.

SQL Stored Procedure Tutorial

Execute Stored Procedure passing Parameter value to another Procedure

SQL tutorial shows how to call a stored procedure within an other stored procedure and pass values from one to another. And I'll also create a solution for choosing lucky lotto numbers for Turkish lotto game Sayisal Loto using two SQL stored procedures

Transact-SQL Programming

Sort Data according to Order of IN clause Like SQL Order By

Transact-SQL tutorial shows to sort data filtered with SQL IN clause in WHERE criteria using the order of In clause without an ORDER BY clause

SQL Server Full-text Search

Full-Text Search in Different Languages on SQL Server

This SQL Server tutorial shows how to query a text table using full-text search where texts in different languages are stored with the help of different word breaker languages on SQL Server 2014.

SQL Server Tools

SQL Period Calculation for Total Downtime per Month

Period calculation in SQL or time calculations where developers sum specific events durations based on start and end times require coding by Transact-SQL developers.

SQL Server Tools

Create Composite Primary Key with Multiple Columns in SQL Server

In your database design on table level, database administrators can create composite primary key which is consists of two or more columns in that table. In SQL Server database developers or administrators can choose to use composite keys (composite primary key or indexes on multiple columns) instead of defining a primary key on a single table column.

SQL Server Tools

Get List of Database User Role Memberships for All Databases in SQL Server

SQL Server database administrators frequently require a list of SQL logins or Windows users granted access on a SQL Server instance with the databases and database roles they're mapped for that database.

SQL Server Tools

DBCC PAGE to Display Contents of Data Pages in SQL Server

DBCC PAGE command is used to display contents of data pages where table rows data are stored in SQL Server database tables. Database administrators and SQL developers can use DBCC PAGE statement for displaying data in certain data page

T-SQL Development

Create Store Procedure that will Run in All Databases

In order to create a stored procedure that will run in all databases on a SQL Server instance, create stored procedure in master database and name it starting with "sp_"

T-SQL Programming and User Defined Functions

Leap Year Function in SQL Server

SQL developers can use T-SQL functions to determine whether a year is a leap year or not

SQL Server 2014 Setup

Incorrect Function Setup Error during SQL Server 2014 Installation

After the release of SQL Server 2014 CTP 2, I download and install SQL Server 2014 on my test machine. Incorrect function setup error was the first issue I had to resolve during SQL Server 2014 setup

Transact-SQL Tutorial - SQL Merge and Instead Of Insert Trigger

Prevent Duplicate Rows in Table using Merge in SQL Trigger

This SQL tutorial shows how Transact-SQL Merge command can be used in a SQL Instead Of Insert trigger for maintaining unique combination of selected row columns.

SQL Server Tools SQLCMD Utility

List of SQL Servers using sqlcmd Utility

sqlcmd Utility enables database administrators to list servers where SQL Server is installed in local network. List of SQL Servers in your network can be prepared by using running sqlcmd utility sqlcmd.exe with -Lc option easily.

SQL Server BCP Utility

Unable to open BCP host data-file SQL Server error

SQL BCP utility can be used to export data from SQL Server database tables to a text file on local folders or network shares. If required write permission on the target file folder is not granted to SQL Server service, the SQL BCP error "Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file" is thrown.

SQL Server sp_configure Stored Procedure

Enable Ole Automation Procedures in SQL Server 2012

SQL Server database administrator must enable Ole Automation Procedures using sp_configure for SQL developers to use sp_OACreate or sp_OAMethod like Ole Automation Procedures in their SQL scripts

Restore Database from Backup File

How to Restore Database from Backup File in SQL Server 2012

To restore database in SQL Server 2012 from database backup file is a method to move a database from a SQL Server instance to another SQL Server 2012 instance.

Transact-SQL Tutorial

Get Current Database Name in SQL Server using DB_NAME() Function

Using built-in DB_NAME() SQL function, developers can get current database name that they are executing their scripts on. If you pass database id parameter to DB_NAME() sql function, it will return the name of the SQL database with given database id property in sys.databases system view.

SQL Server Tutorial

sys.dm_db_index_physical_stats to Rebuild Index or Reorganize Index on SQL Server Database

Using sys.dm_db_index_physical_stats dynamic management function to rebuild index or reorganize index on a SQL Server database can easily be managed with a scheduled task by database administrators. A SQL Server database administrator should periodically take action on index maintenance by using reorganize index or rebuild index commands

SQL Server Tutorial

Drop SQL Server Check Constraint without Constraint Name

To drop SQL Check Constraint where constraint's name is unknown, sys.check_constraints system view can be used. This SQL Server tutorial show how developers can create sql procedure that drop check constraint with only table name and column name parameters.

SQL Server 2012 Contained Databases

sp_configure Contained Database Authentication to Create Contained Database in SQL Server 2012

The sp_configure value 'contained database authentication' must be set to 1 in order to create a contained database. You may need to use RECONFIGURE to set the value_in_use. (Microsoft SQL Server, Error: 12824)

XML in SQL Server

Query XML in SQL Server for Different Hierarchy Levels using Cross Apply

SQL Server XML tutorial shows to query XML data using Cross Apply SQL join for XML nodes, attributes with different hierarchy levels. SQL XML query for complex XML in SQL Server 2012 frequently use Cross Apply to get desired output result from XML data.

SQL Server XML Tutorial

Query SQL Server Database Table XML data using Cross Apply Join

SQL programmers can SELECT from XML data nodes stored in SQL Server table column with XML data type. Although it is very similar to query XML variable, querying XML data column requires the use of SQL CROSS APPLY join. Cross Apply enables to execute SQL XML query on all rows of the database table instead of running SQL Select query on single XML data.

SQL XML Tutorial

Query XML data using SQL XML in SQL Server

This SQL XML tutorial shows to query XML data in SQL Server for SQL programmers to import XML to SQL Server and store XML data in SQL Server database table in table columns with SQL XML data types

SQL Server Management Studio

Remove SQL Server Management Studio Text Editor IntelliSense Error Underlines

Using SQL Server Management Studio IntelliSense Settings options for Text Editor, database administrator or SQL programmer can disable intellisense errors marked by red underlines of SQL scripts

SQL Tutorial - Add SQL Unique Constraint

Add SQL Server Unique Constraint on Multiple Columns

This SQL tutorial shows how to add SQL Server unique constraint on multiple columns in a database table. SQL unique constraint on multiple columns ensures that in SQL Server table there will not be a second row sharing the same data on defined columns with an other table row.

SQL Tutorial - Select Count Distinct

SQL Count Distinct Select Query Example

To count distinct values in a database table or sql view, Transact-SQL developers can use SQL Count Distinct Select command as demonstrated in this SQL tutorial

SQL Tutorial - SQL Server Row_Number() Function

SQL Row_Number() Function Example Queries

Using SQL Row_Number() OVER (Partition By partitioncolumn Order By sortcolumn) SQL developers can create an ordered list of records grouped by a column

SQL Tutorial - SQL Order By Clause

SQL Order By Query Examples

SQL Order By clause is used to sort data returned by the execution of SQL queries developed by SQL programmers or database administrators

Insert Image to SQL Server Database

Save Image to Database Table in SQL Server

To save image in SQL Server database table in binary format, the easiest method is to execute an SQL OPENROWSET command with BULK and SINGLE_BLOB options

Check SQL Server Version

Download SQL Server 2012 Service Pack 1 (SQL Server 2012 SP1)

SQL Server 2012 Service Pack 1 download is available at Microsoft Downloads portal as a standalone SQL Server 2012 SP1 download and also as bundled with SQL Server 2012 named as Slipstream download

Check SQL Server Version

Check SQL Server Version using Registry Editor

To check SQL Server version on a server, there are different methods like using RegEdit Registry Editor tool or executing T-SQL commands like SELECT @@Version

SQL Server Restore Database Fail

SQL Server Restore Database fail because of incompatible backup and restore server version

Microsoft SQL Server Management Studio

Restore of database 'SQL Server database name' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

System.Data.SqlClient.SqlError: The database was backed up on a server running version 8.00.0534. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)

SQL Server Integer Data Types

Max Integer Values for SQL Server Numeric Data Types

SQL Server database design requires good knowledge of data types besides many other details. Max integer values for sql numeric data types is important if you don't want to spare more database space than required for integer or numeric data

SQL Server Table Valued Parameters and Table Type

Pass Multiple Values using Table Valued Parameters to SQL Stored Procedure

Using table valued parameters in SQL stored procedure, SQL developers can provide a solution to pass a list of parameter values to the SQL stored procedure. Passing multiple values is now possible with table-valued parameters in SQL Server stored procedure programming

SQL Server Attach Database Error

Attach database failed for Server

An error occurred when attaching the database(s).

Unable to open the physical file. Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)

SQL Function Example

Select from Stored Procedure in SQL Function using OpenQuery

T-SQL programmers can select from stored procedure in sql function code using OpenQuery and linked server pointing to itself. SQL tutorial shows how to execute SQL stored procedure within SQL function codes and return SELECT data as a table-valued function table

SQL Server 2012 Linked Server

How to Add SQL Server Linked Server on SQL Server 2012

This SQL Server 2012 tutorial shows how to add linked server to SQL Server 2012 instance

Create SQL Server Loopback Linked Server

You cannot create a local SQL Server as a linked server

It is possible to create SQL Server linked server on a SQL Server instance pointing to itself. This is called loopback linked server in SQL Server. By creating local SQL Server instance as a linked server to itself enables Transact-SQL developers to use SQL Server OpenRowset or OpenQuery commands for special purposes

Create SQL Server Linked Server Error

SQL Server Linked Server creation require sysadmin Role

While creating new linked server on SQL Server 2012 using Microsoft SQL Server Management Studio, I got the following error message: A required operation could not be completed. You must be a member of the sysadmin role to perform this operation. (SqlManagerUI)

SQL Server Troubleshooting

SQL Server Model Database Error and Download Model Database

SQL Server error while "Starting up database model" requires a fresh model database data and log file replacement. You can download model database data and log file for various SQL Server versions here

SQL Tools for SQL Server Administrators and Database Developers

Execute SQL Scripts against multiple databases using Script Executor

SQL tool Script Executor from xSQL Software helps database administrators and developers to deploy sql scripts to multiple databases esily making this complex administrative task managed in a secure way.

SQL Tutorial - FIFO Example in SQL Server

FIFO Example Query in SQL Server

This SQL tutorial includes FIFO example query in SQL Server. SQL FIFO method example query is based on two database tables SalesOrder and ProductionOrder sql tables

SQL Server Integration Services Tutorial - SSIS Package

Export Data to Flat File using SSIS Package

This SQL Server SSIS tutorial shows how to export data stored in database table into a flat file by using SSIS package. Using SQL Server Business Intelligence Development Studio (BIDS), SQL Server BI developers can easily create SSIS package to transfer table data to a text file

Transact-SQL Tutorial

Compare 3 Numbers using SQL Script

This SQL tutorial shows how to compare 3 numbers using SQL script for Transact-SQL beginners. By changing the below sql codes, developers can easily sort 3 numbers given as variables from biggest to smallest values.

Transact-SQL Tutorial

SQL Repeat Rows N Times According to Column Value

To repeat table rows in different number of times according to a number column SQL developers can use given T-SQL codes in this SQL tutorial

SQL Server Data Compare Tool

xSQL Data Compare – data comparison and synchronization tool for SQL Server

In this SQL data compare tool guide, we review xSQL Software's Data Compare tool. SQL Server database administrators and SQL developers can use this SQL Server tool for data comparison and data synchronization.

SQL Server Schema Compare Tool

xSQL Object – SQL Schema Compare tool for SQL developers and DBA

SQL Server database schema compare tool review includes xSQL Software's SQL schema compare tool and its schema comparison and create synchronization script features

Transact-SQL Merge Command

T-SQL Merge in SQL Server Trigger for Summary Table Maintenance

In order to provide a detail and summary table solution in a SQL Server database, SQL Merge command can be used in SQL Server trigger codes as demonstrated in T-SQL tutorial

SQL Server Auditing Tool

Failed Login Attempts Auditing using SQL Server Audit Tool

To log failed login attempts to SQL Server database instance is a best practice for database administrators to keep data platform safe and secure from unauthorized users

SQL Server Auditing Tools

SQL Server Login Auditing using SQL Server Audit Tool

Database administrator can log successful logins to SQL Server using new SQL Server auditing tool SQL Server Audit. In this SQL Server tutorial, administrators can configure an SQL Server login auditing mechanism using SQL Server Audit tool step by step

SQL Server Tools

SQL Server Activity Monitor Tool for Database Administrators

A new SQL Server tool for database administrators with SQL Server 2008 is SQL Server Activity Monitor for monitoring SQL Server performance and for troubleshoot SQL Server performance problems

Transact-SQL Tutorial

List Month Names using SQL Functions

SQL developers may need to list month names using SQL functions during their T-SQL programming tasks. This Transact-SQL tutorial will demonstrate sql codes with DATENAME() function that can be used in order to list names of months in SQL

SQL Mail XPs

Configure SQL Mail XPs sys.xp_readmail by using sp_configure

SQL Server blocked access to procedure 'sys.xp_readmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server

Database Mail

SQL Server Email using sp_send_dbmail with File Attachment

T-SQL programmers can easily attach files to database mails in SQL Server sent by using sp_send_dbmail by passing file physical paths into @file_attachments parameter

Transact-SQL Tutorial

SQL paging using ROW_NUMBER() SQL Server Function

SQL paging in web applications like ASP.NET web sites, etc is a common requirement for SQL Server developers. For SQL Server versions before SQL Server 2012, for example paging in SQL Server 2005 can be implemented using ROW_NUMBER() SQL Server function easily

SQL Server FileStream

FILESTREAM feature is not supported on WoW64

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

FILESTREAM feature is not supported on WoW64. The feature is disabled. (Microsoft SQL Server, Error: 5593)

SQL Server 2012 Installation

There was a failure to validate setting CTRLUSERS in validation function ValidateUsers

During SQL Server 2012 RC0 evaluation version installation, I got the following setup error : Error code 0x85640004.

There was a failure to validate setting CTRLUSERS in validation function ValidateUsers. Error code 0x85640004

SQL Server BCP

SQL Server BCP Utility with sp_HelpText to Generate Script File for Each Stored Procedure in a Database

SQL Server BCP utility is used to write sql query results to text files into a file folder. If T-SQL developers use SQL BCP with sp_HelpText, it is possible to generate script files for SQL Server objects too

Transact-SQL Development in SQL Server 2012

New SQL IIF Boolean Function in SQL Server 2012

New SQL IIF function introduced with SQL Server 2012 evaluates a boolean expression and according to the being True or False of the boolean expression, returns one of the input values

SQL Server Tools

How to Display SQL Server Job History

SQL Server developers and database administrators can list SQL Server job history by querying MSDB database. SQL Server MSDB database stores data related with SQL Server Agent service processes like database jobs, SQL Server job scheduling, job history, etc.

SQL Server Mean Value Calculation

Calculate Mean Value in SQL Server

SQL programmers can calculate mean value of a numeric array in SQL Server by using SQL Server AVG() aggregate function. Transact-SQL developers or database administrators can use mean value calculation method on any numeric table column in a SQL Server database as shown in this SQL tutorial

SQL Server Median Function

Calculate Median Value of a Numeric List in SQL Server

SQL programmers can calculate median value of a numeric array in SQL Server by using Row_Number(), Count() and other SQL Server aggregate functions. Transact-SQL developers or database administrators can use median value calculation method on any numeric table column in a SQL Server database as well as the can calculate the median value of a given list as an input parameter to the SQL median function

SQL Server FileStream Configuration

Default FileStream filegroup is not available in database 'DatabaseName'

Msg 1969, Level 16, State 1, Line 1

Default FILESTREAM filegroup is not available in database 'DatabaseName'

SQL Server Management Studio

Hide Results Pane Shortcut Ctrl+R in SQL Server Management Studio CTP3

SQL Server Management Studio (SSMS) Hide Results Pane shortcut Ctlr + R is not defined on my SQL Server 2012 CTP 3 installation at home

SQL Server Recursive Query

SQL Server Recursive Query with Recursive CTE (Common Table Expession)

SQL Server Recursive Query structure is new with SQL Server 2005 Common Table Expression improvement. What makes CTE indispensable in SQL development is its recursive query creation features

SQL Server Computed Columns

Computed Column Sample in SQL Server Database Table

A computed column in SQL Server is an expression field which uses other columns in the table as input in the expression.

SQL Server Backup

SQL Server Backup using Cmd Windows Command Line Backup with SQLCmd

This SQL Server 2008 tutorial show how to back up SQL database using command line tool SQLCMD

T-SQL Time Data Type Calculations

How to Calculate Time Operations in SQL Server on Time Data Type

Frequently, t-sql developers sum time fields and then return total time variable as and expression in hour, minute and seconds

SQL Server 2012 Express LocalDB Management

SQL Server Express LocalDB Management Utility SqlLocalDB.exe

A SQL Server Express LocalDB database is managed by the SqlLocalDB.exe management utility. In BOL (Books OnLine), you may also read about LocalDBManager.exe management utility, too

New SQL Server Functions

SQL Choose() Function in SQL Server 2012

SQL Choose() function returns an input argument from a list of input values at the desired index which is specified as an input arguement as well

New SQL Server Datetime Functions

Calculate SQL Last Day of Month using End of Month EOMonth() Function in SQL Server 2012

SQL Server EOMonth() function returns the last day of the month that the input argument start_date is in that month. There is an optional input parameter offset, which helps T-SQL developers to find the end of month that is N months later or before the input start date argument

SQL Server Analytic Functions

SQL LAG() Function in SQL Server 2012 for Calculating Previous Values

SQL Server LAG() function is a new SQL Analytic Function introduced with SQL Server 2012 for T-SQL developers. SQL Lag() can be used to return previous row value in an ordered list of rows

SQL Server Analytic Functions

SQL LEAD() Function in SQL Server 2012 for Calculating Next Value

SQL LEAD() function is one of the recent enhancements among SQL Analytic Functions introduced with SQL Server 2012 for developers. What SQL Server Lead() function does is returning simply the next Nth row value in an order

Install SQL Server 2012 Express LocalDB

SQL Server 2012 Express LocalDB Installation

SQL Server 2012 Express LocalDB is a new edition for SQL Server Express product especially targeting application developers. Tutorial is showing SQL Server Express LocalDB installation step by step with screenshots

SQL Server Factorial Function

SQL Factorial Function to Calculate Factorial of an Integer in SQL Server

In SQL Server, developers can use SQL factorial function given in this tutorial to calculate factorial for a given integer value

Install SQL Server 2012

How to Install SQL Server 2012

This tutorial is showing how to insall SQL Server 2012 CTP3 on a database server. The above described setup process may vary according to the SQL Server feature selection you configured for the instance. I hope the setup guide will be helpful for developers and SQL Server database administrators

SQL Server 2012 Sample Database

Download Sample Database AdventureWorks for SQL Server 2012

Microsoft SQL Server 2012 database administrators and T-SQL developers can download sample database AdventureWorks for SQL Server 2012

SQL Server Analytic Functions

First_Value SQL Analytic Function in SQL Server 2012 T-SQL

First_Value SQL analytic function returns the first value of an ordered set of values

SQL Server Analytic Functions

Last_Value SQL Analytic Function in SQL Server 2012 T-SQL

Last_Value() function is one of the newest enhancements in T-SQL just like other new SQL Server analytic functions introduced with SQL Server 2012 CTP3 release

SQL Server Express LocalDB

What is SQL Server Express LocalDB enhancement with SQL Server 2012

The SQL Server Express 2012 LocalDB enables developers directly connect to a SQL Server database. And programmers can easily write to SQL database files and read from SQL Server database tables without dealing with the management of a SQL Server instance on their local machine

SQL Server Reporting Services 2012 Enhancements

What is New in SQL Server Reporting Services 2012 with CTP3

Microsoft has released CTP3 version of next Data Platform tool, SQL Server 2012 aka Denali. And there are good news for Business Intelligence developers who are using SQL Server Reporting Services for BI development

T-SQL Enhancements in SQL Server 2012

SQL Analytic Functions new in SQL Server 2012 for T-SQL Developers

Microsoft SQL Server 2012 introduces a list of SQL Analytic Functions for T-SQL developers

SQL Programmability Enhancements in SQL Server 2012

New T-SQL Functions in SQL Server 2012 for T-SQL Developers

Microsoft SQL Server 2012 introduces new 14 built-in functions as a programmability enhancement for T-SQL developers with the latest release of SQL Server 2012 CTP3 release

SQL Server Reporting Services

SQL Server Reporting Services: Create Data-Driven Subscription

In this SQL Server Reporting Services tutorial, business intelligence developers will create data-driven subscription in Reporting Services. Data-driven subscriptions enable SQL Server Business Intelligence developers deliver database reports in various formats in mass numbers with single task

SQL Server Tools

SQL Server Copy Database Wizard

SQL Server Copy Database Wizard provides a handy SQL Server tool for database administrators and developers to copy sql database from one SQL Server instance to another SQL Server

SQL Server Tools

Define Keyboard Shortcuts in SQL Server Management Studio to Simplfy T-SQL Commands

Why don't you use SQL Server Management Studio Query Window keyboard shortcuts? Just highlight the SQL Server object name on Query Editor window and press shortcut keys

SQL Server Reporting Services

Subscriptions cannot be created because the credentials used to run the report are not stored

Reporting Services subscription error : Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid

T-SQL

Last Update Date using SQL Trigger in SQL Server Database Table

It is important to keep last update date on a database table row to see the last time when the current record is updated. Database administrators and SQL developers generally put InsertDate and UpdateDate columns in table definitions during their CREATE TABLE scripts. I use SQL Serve Update Trigger or SQL Server Instead Of Update Trigger to keep last update date field up-to date

SQL Server 2012

Cannot connect to SQL Server 2012

I'm running SQL Server 2012 CTP 1 on my local computer. Yesterday I got an error message "Cannot connect to SQL Server Denali instance" when I tried to connect to SQL Server 2012 instance using SQL Server Management Studio (SSMS)

SQL Split String Function

TSQL Character Split Function in SQL Server

SQL Server split function in this t-sql tutorial returns each alpha-numeric character of the input string in different rows in order back to the user as output. One important task for this string function is it adds additional null values between characters, if there is a numeric character in the input string

T-SQL Tutorial

SQL Count Function with Partition By Clause

SQL Count with Partition By clause is one of the new powerful syntax that t-sql developers can easily use which is introduced with SQL Server 2005

SQL Formatter SQL Server Tools

SQL Assistant SQL Formatter tool for SQL Server Database Developers

Using SQL formatter tool for formatting sql code is very important to make sql code reading easier. I believe many sql developers searched for a formatting sql code tool. I ended to suffer from unformatted sql codes within hundreds of lines of t-sql code in stored procedures with SQL Assistant SQL Server tool

SQL Server Performance Optimization

SQL Query Performance Optimization using SET Statistics in SQL Server

It is a vital task for SQL Server developers and database administrators to optimize SQL query performance especially if the database application is showing poor performance

SQL Running Totals

SQL Running Total Sample in SQL Server 2008

Calculating sql running total in SQL Server queries is a common task for most of t-sql developers. In order to display running totals for a quantity or amount column, easiest method in SQL Server is using t-sql CTE (Common Table Expression) structures

T-SQL Date Table

Create Date and Time Intervals Table in SQL Server 2008

Time schedule table or date table is frequently required by sql developers during t-sql coding. In this t-sql tutorial I want to give some sql hints that SQL programmers can use in their daily works. You will see that the following sql samples use SQL Server numbers table code in order to create a periodic time blocks or time intervals for different purposes

SQL Cursor Tutorial

T-SQL Nested Cursor in SQL Server 2008

SQL developers can create nested cursor in SQL Server by defining an outer cursor and within the cursor code a new cursor is defined for each row in main cursor select. The inner cursor is created, executed, closed and deallocated each time in the outer cursor for each row

SQL Server Instead Of Trigger

SQL Server Instead Of Trigger with Sequence Table Sample

Using SQL Server Instead of Trigger defined on a table in combination with a sequence table and T-SQL features like SQL Output clause and T-SQL Row_Number function I will show how to insert the identity column value of a table from the sequence table

Oracle Linked Server

Create Oracle Linked Server to Query data from Oracle to SQL Server

This SQL Server tutorial shows how to create SQL Server linked server to Oracle database, for SQL Server administrators and t-sql developers to execute sql statements over the target Oracle database tables

SQL Server BCP Command

SQL Server BCP Command Example for SQL Output to File

In this sql tutorial, t-sql developers will find SQL BCP example to write SQL output to file. Using SQL BCP command, developers can write output to text file

SQL Server Import Data using SSIS Package

SQL Server Import Data using SQL Server Import Export Data Wizard SSIS Package

SQL Server Import and Export Wizard, a SQL Server tools that enables SQL Server import data easily for ETL developers and database administrators.

SQL Server Logon Trigger

Logon Triggers - Logon failed for login due to trigger execution

While trying to connect SQL Server using SQL Server Management Studio Object Explorer, I got the following error message "Logon failed for login 'sqluser' due to trigger execution.". When I see the error message occured during connect to SQL Server 2008 R2 database, I realized that the cause of the sql exception is the SQL Server logon trigger which I created recently for SQL Server login audit purposes

T-SQL Programming

SQL Waitfor Delay and SQL Waitfor Time in SQL Server

T-SQL developers use SQL Waitfor statement to postpone or delay the execution of a t-sql script block or to set the execution time of the sql code block

T-SQL Programming

How to Calculate SQL Last Day of Month in SQL Server

In order to return the last day of the month of a specific date the sql last day of month code scripts that are given in this T-SQL tutorial can be used

T-SQL Programming

Use xp_dirtree SQL Server Stored Procedure and Recursive CTE Query to List Hierarchical Structure of File Folders

T-SQL programming enables developers to use xp_dirtree SQL Server stored procedure with SQL recursive CTE query to display subdirectory list as a hierarchical structure of file folders

SQL Server Export to Excel

Export Data to Excel using SQL Server Integration Services SSIS Package

SQL Server SSIS packages can be used for sql data export to Excel file and deploy SSIS package to SQL Server Integration Services

T-SQL Tutorial

How to Prevent DROP Table using SQL Server View With SchemaBinding

SQL programmers can create SQL Server database VIEW objects using With SchemaBinding option to prevent changes in the dependent database objects.

T-SQL Trigger

How to Prevent DROP Table using SQL Server DDL Trigger

T-SQL developers and SQL Server administrators can prevent unauthorized users to drop sql tables using sql DROP TABLE command on their databases with the use of SQL Server DDL trigger

T-SQL Functions

Case Sensitive SQL Split Function

In this T-SQL tutorial, SQL developers can find a sample sql case sensitive split string function. This sample SQL split string function identifies upper case letters and behaves as the start of a new word

Transact-SQL DateAdd

SQL Server DateAdd Function T-SQL Example

SQL Server DateAdd function returns date which is added a time inverval identified by "basic datetime interval units times a given number of times" to a specific input date or datetime parameter

Transact-SQL Programming

Fuzzy String Matching using Levenshtein Distance Algorithm in SQL Server

The Levenshtein distance algoritm is a popular method of fuzzy string matching. replacements or chararacter insert or character deletion required to transform one string to other

SQL Server Dynamic SQL Query

How to Build SQL Server Dynamic SQL Query Example

SQL tutorial shows how to build a dynamic sql query in SQL Server for sql database developers

SQL Server Tools

LiteSpeed Backup SQL Server Backup Compression Tool

LiteSpeed Backup SQL Server Backup Compression tool is a backup compression software and sql backup compression tool for database administrator

SQL Server 2012

How to Install SQL Server 2012 - Microsoft SQL Server 2012 Installation Guide

Microsoft SQL Server 2012 Installation Guide

SQL Server 2012

Microsoft SQL Server 2012 Requirements

During Microsoft SQL Server 2012 installation, in order to install SQL Server 2012 without a problem and an interreption please take care to the Microsoft SQL Server 2012 Requirements list

SQL Server 2012

Download SQL Server 2012 Sample Databases including AdventureWorks Database

Download sample databases including AdventureWorks database for SQL Server 2012

T-SQL sp_MSForEachDB Stored Procedure

List Database Size using SQL Server sp_MSForEachDB Stored Procedure

SQL Server sp_MSForEachDB stored procedure can be used by SQL Server database administrators and SQL developers to get a list of SQL Server databases and the database sizes

SQL Server sp_rename Stored Procedure

How to Rename Dabase Name using T-SQL sp_rename in SQL Server 2008

In order to rename database name in SQL Server, sp_renamedb stored procedure can be used

SQL Count Over Partition By

SQL Select Count of ResultSet Rows using COUNT(*) OVER PARTITION BY

SQL SELECT statement used to return total rows count beside the rows data

SQL Tutorial - COALESCE

SQL COALESCE Function Example

SQL developers use SQL COALESCE function to return the first NOT NULL value of the function arguments

SQL Pad Leading Zeros

SQL Tutorial - SQL Pad Leading Zeros

In order to sql pad leading zeros, sql developers use sql string concatenation and sql string functions LEFT and RIGHT

SQL Server 2005 Service Pack 2 is now available

Microsoft has announced SQL Server 2005 Service Pack 2 (SP2) on 16th of February 2007

For more information on SP2 read article at SQL Server 2005 SP2 titled article.

SQL Server 2005 Service Pack 1 is now available

Microsoft has announced SQL Server 2005 Service Pack 1 (SP1) on 18th of April

For more information on SP1 read article at SQL Server 2005 SP1 titled article

SQL Trigger Tutorial

SQL Tutorial : Log Price Changes using SQL Server Trigger in SQL Server 2008

In this SQL Server trigger example, database sql trigger is used to keep history of changes on price column in a table

T-SQL Tutorial - Table-Valued Function

SQL Server Table-Valued Function Example

A SQL Server table-valued function is a user-defined function which returns a table data type as output and also accepts parameters

SQL Server Trigger

SQL Trigger Example in SQL Server 2008

An SQL trigger can contain sql codes that are executed automatically by SQL Server engine when a certain event occurs. Since this sql tutorial is concentrated on DML (Data Manipulation Language) concepts right now, our sql code examples will be a DML sql trigger. The events that trigger SQL Server triggers which are actually stored t-sql codes are sql INSERT, UPDATE and DELETE statements executed on a sql database table

SQL Server Analysis Services

Service 'MSSQLServerOLAPService' start request failed

After I had install Microsoft SQL Server 2008 R2, while trying to start MS SQL Server Analysis Service I got the following error message : The service could not be started. Reason: Service 'MSSQLServerOLAPService' start request failed.

Change Data Capture

SQL Server Log Tool for Capturing Data Changes by building Change Data Capture Structure for MS SQL Server 2005

If you are using Microsoft SQL Server 2005 as your relational database and you neeed to log data changes in sql server databases in order to log changes or for recovery data, you can use the logging changes solution described here in this article built like Change Data Capture structures for MS SQL Server 2005 databases.

SQL Server Export to Excel

SQL Server Export to Excel using SQL Server Import and Export Wizard

Data export to Excel from SQL Server has always been a problem for T-SQL developers and SQL Server administrators. Microsoft SQL Server professionals have now advanced sql tools for solution to SQL Server export to Excel within SQL Server Management Studio. The latest SQL tool to export data to Excel from SQL Server is SQL Server Import and Export Wizard

SQL Programming

Declare and Assign Value to a Variable in-line in SQL Server

SQL Server 2008 introduced sql variable declaration and inline value assignment for T-SQL developers. This new feature enables SQL programmers to create a new sql variable using DECLARE command and directly assign it to a value using "=" (equal) sign in the same statement

SQL Tutorial

How to drop Default Constraint on a Table Column without knowing the Constraint Name for MS SQL Server 2005

How to drop Default Constraint on a Table Column without knowing the Constraint Name for SQL Server 2005

SQL Tutorial

How to Search SQL Text Anywhere in SQL Server Database

Many developers face the problem to search SQL text anywhere in the sql database objects definition. If sql developers have an sql search tool which can be used to find specific keywords in SQL Server object definitions, then the solution to search string in sql is easy

SQL Tutorial

Find Tables Where a Column is Used in Primary Key Definition

On SQL Server, to find the list of database tables where a specific column name is used in primary key definitions, Transact-SQL developers can use the SQL Select script shared in this tutorial

SQL Tutorial

How to Debug Stored Procedure in SQL Server 2008

How to debug stored procedure in SQL Server 2008 with given t-sql code and execute it to create stored procedure in sample SQL Server database

SQL Tutorial

SQL Tutorial - How to Map Payments to Expenses using T-SQL Query

In sample SQL Server database two database tables are used to store expenses and payments for closing expense amount. SQL query matches payments done by the same customer to close expense records

SQL CLR String Split Function

SQL Server String Split T-SQL CLR Function Sample

SQL CLR enables developers to create SQL CLR stored procedure and SQL Server CLR function to build complex processes easier and perform better using VB.NET or C#. String split is one of the sample applications where SQL Server CLR functions will perform better in SQL Server 2005 and SQL Server 2008

T-SQL xp_cmdshell

List Directory Files using T-SQL xp_cmdShell Stored Procedure

SQL tutorial with sql script to list directory files and display contents as a file list using the t-sql xp_cmdShell stored procedure

SQL Server Partition Table

How to Partition Table (a Non-Partitioned Table) using T-SQL in SQL Server 2008

"Can we partition existing table in SQL Server ?", or "How can we add partitioning to a non partitioned table ?" are frequently asked questions about sql partitioning and partitioned tables in MS SQL Server among t-sql developers and SQL Server professionals. Especially partitioning non-partitioned tables may become a difficult task to partition an already existing table if it contains a lot of data and has many constraints and relations with other sql tables in the database.

SQL Server Backup

How to Create SQL Server Backup Job

All SQL Server database administrators create SQL Server backup job to take database backup of their SQL Servers. It is of course possible to execute SQL Server backup scripts periodically, or to execute t-sql backup commands from a scheduled SQL Server job, but Microsoft SQL Server 2008 R2 has advanced Maintenance Plan options to manage sql backup tasks for you.

SQL XML Query Example

Microsoft SQL Server XML Query Example

In this SQL XML Query example, t-sql developers will see enhancements introduced first with Microsoft SQL Server 2005 related with SQL XML features

SQL Server Backup Script

SQL Server Backup Script for All Databases in SQL Server Instance

Undocumented stored procedure sp_Msforeachdb can help SQL administrators and t-sql developers to repeat tasks on each of sql databases running on the SQL Server instance. SQL Server backup script using sp_Msforeachdb undocumented stored procedure will make it sql backups easier for sql professionals

SQL Backup Script

MS SQL Server Backup Script

Microsoft SQL Server database administrators and T-SQL programmers can use SQL Server backup script to take sql backup of their sql databases

SQL Backup

SQL Backup - SQL Server Backup Database Tool in SQL Server Management Studio

SQL Server backup is copy of the data in your database which is used to restore database in case of a failure in production environment

SQL VBScript

How to Run VBScript from SQL Server 2008

SQL developers can run vb script files from SQL Server t-sql codes by using xp_cmdshell procedure. Of course calling vbscript from sql can be solved by using CLR in SQL Server after the release of SQL Server 2005

SQL Server E-Mail

Send SQL Mail using CDONTS.NewMail in SQL Server 2008

SQL developers can send e-mail from SQL Server using CDONTS or CDOSYS.

Although SQL Server 2005 introduced Database Mail for sending emails from SQL Server, let's remember how CDONTS can be used along with SQL Server OLE Automation stored procedures

SQL Server 2008 Database Mail Setup

How to configure Database Mail in SQL Server 2008 R2 and Use sp_send_dbmail

What is SQL Database Mail ? Database Mail Configuration in SQL Server 2008 R2 and how to send eMail using Database Mail sp_send_dbmail T-SQL Command

SQL XML Query in SQL Server

SQL XML Query in SQL Server 2008

With t-sql enhancements introduced with Microsoft SQL Server 2005, developers are now enable to use more powerful T-SQL XML commands during their developments.

Import XML into SQL Server

How to Import XML into SQL Server 2005 or SQL Server 2008

In this MSSQL XML tutorial, I will try to give t-sql xml query samples that can be used with dealing sql xml data and import XML to SQL Server

T-SQL

A correlation name must be specified for the bulk rowset in the from clause.

While inserting image files into a SQL Server database table using the OPENROWSET function, "A correlation name must be specified for the bulk rowset in the from clause." might occur

SQL Server Database Mail

SQL Server Database Mail sp_send_dbmail Example

Here is a sample t-sql code using sp_send_dbmail system stored procedure to send email from SQL Server.

SQL Server 2008 Certification

Microsoft SQL Server 2008 Certification Exams

Certification guide for candidates listing Microsoft SQL Server 2008 Certification Exams giving credits for SQL Server 2008 Certifications

T-SQL

sysindexes System View - List of Indexes Defined on a SQL Database Table

SQL developers and SQL Server database administrators frequently require to list of indexes defined on a sql database table. In SQL Server 2005 and later versions, T-SQL developers and sql administrators can use given SQL script to list SQL Server indexes defined on a database table

SQL Server 2008 R2

AdventureWorks Database - Download Microsoft SQL Server 2008 R2 Sample Databases

Download SQL Server 2008 R2 sample databases including AdventureWorks database download from CodePlex

T-SQL TRY CATCH

SQL Try Catch in SQL Server 2005

One of the tsql enhancements in Microsoft SQL Server 2005 is SQL Try Catch improvement. T-SQL Try Catch in SQL Server 2005 enables database developers exception handling on SQL Server databases

SQL Server 2008 R2

Microsoft SQL Server 2008 R2 Upgrade Advisor Download

If you plan to upgrade to SQL Server 2008 R2, you should better choose Install Upgrade Advisor first.

SQL Server 2008 R2

What is new in Microsoft SQL Server 2008 R2 Reporting Services

Business Intelligence (BI) developers can find a short list of new features in Microsoft SQL Server Reporting Services 2008 R2

T-SQL NEWID()

Use SQL NEWID in SQL Functions as SQL Random Generator

SQL NewID function is used for selecting random row from a resultset in SQL Server databases. Using with TOP N keyword in SELECT statements where SQL NEWID is in the ORDER BY statement, random records are selected from a table or from a set of rows. "SELECT TOP 1 FullName FROM Customers ORDER BY NEWID ()" is a sample sql NEWID usage.

SQL Extended Stored Procedure

How to Create Folder from SQL Server sys.xp_create_subdir Extended Stored Procedure

SQL Server tutorial for sql developers to create file folder using SQL Server sys.xp_create_subdir extended stored procedure

SQL Multiple CTE Example

SQL Multiple CTE Syntax and T-SQL CTE Example in SQL Server 2008

This t-sql tutorial will give t-sql examples to show hot to use sql multiple CTE queries in one SQL Select statement

SQL Select from Stored Procedure

How to SQL Select from Stored Procedure using SQL Server OPENQUERY or OPENROWSET

Database tutorial showing SQL developers to select from SQL stored procedure execution results using OPENQUERY and OPENROWSET

SQL Server Trigger

SQL Trigger : SQL Server Trigger Example to Log Changes History of a SQL Table

In this t-sql tutorial sql administrators and tsql developers will find SQL Trigger example code created for logging of updated or deleted records into history tables.

The SQL Server trigger will be created as sql update / delete trigger on the target database table. For example when the web application or users create/insert record into sql table or delete record from sql database table, the sample sql trigger will execute. The sql trigger will insert the deleted or updated version of the table row into history table

sp_Msforeachdb SQL Procedure

How to Find a SQL Table in All Different Databases on a SQL Server Instance using sp_Msforeachdb

SQL Server database administrators sometimes need to search for a sql table in all sql databases on a SQL Server instance. Using sql sp_Msforeachdb undocumented stored procedure, the SELECT statement or any t-sql statement passed as an argument will be executed on every SQL database existing on the SQL Server instance

xp_regread SQL Procedure

Read Registry using SQL Server xp_regread SQL Stored Procedure

xp_regread sql stored procedure can be used to read registry key values in t-sql codes on a SQL Server database server. xp_regread sql extended stored procedure is one of the undocumented stored procedures

T-SQL Utilities

How to use SQL DROP Table If Table Exists in SQL Database

To drop a table in SQL database, developers use sql DROP TABLE command. If sql table does not exist in SQL Server database, then sql DROP TABLE command will cause an sql engine error

T-SQL Utilities

SQL Object Where Used List by a Search SQL Database Objects Text

A common requirement for SQL Server database administrators and sql developers is to search in which sql objects a stored procedure or a view or a table is used. One way of finding sql objects like sql procedures, views and sql functions which uses a specific object is to run a t-sql SELECT statement on the text definitions of all database objects like SPs, views and tsql functions. The text definitions of all objects are stored in text column of syscomments system table

SQL Server 2008

SQL Server Management Studio 2008 SSMS : "Saving changes is not permitted" error

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

SQL Functions

How to Extract Filename from Path using SQL Functions

SQL tutorial for t-sql developers to find sql codes for extracting file name from fullpath of a file

SQL Master Data