SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




Microsoft SQL Server Tutorials, SQL Server Tips, T-SQL Programming Tutorials

Transact-SQL and SQL programming, in-memory OLTP processing, Reporting Services, Analysis Services (OLAP), SQL Server 2016, SQL Server 2014 (aka Hekaton), 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 Release Candidate 2 aka SQLServer 2017 RC2. Database administrators, SQL developers, Business Intelligence developers and Data Scientists can download SQL Server 2017 and install on Windows, macOS or Linux distributions including Red Hat, Ubuntu and SUSE


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 2016


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

download SQL Server 2014 free trial version

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 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
In this SQL tutorial, I will demonstrate 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
This Transact-SQL tutorial show 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. Generally SQL time duration calculation is difficult if you have to report time durations per pre-defined time blocks or time periods. And in those cases using DATEDIFF SQL function is not enough to get the result easily
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. It is easy to find leap years in SQL Server but first what is leap year and what is the rule to determine if a year is leap year.
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. In this SQL Server tutorial, I'll show how to detect SQL Server version using Registry Editor
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. This 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 SQL developers can use given T-SQL codes in this SQL tutorial. Sometimes developers are required to repeat records according to a number column in that table rows.
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, Denali CTP3
SQL Server Management Studio (SSMS) Hide Results Pane shortcut Ctlr + R is not defined on my SQL Server 2012, Denali 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, Denali
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, Denali, CTP3 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 Denali, CTP3 for SQL Server 2012 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. This document 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 Denali CTP3
This tutorial is showing how to insall SQL Server 2012 Denali 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, Denali CTP3
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, Denali 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, Denali 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 Denali
Cannot connect to SQL Server 2012 Denali
I'm running SQL Server 2012, aka Denali 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 MS SQL BCP example to write SQL output to file. Using SQL BCP command, developers can write output to text file.
Here t-sql developers can find the basic sql BCP command syntax.
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, one of powerful SQL Server tools enables SQL Server import data easily for ETL developers and database administrators. It also enables step by step sql data import task, save sql import task as an SQL Server Integration Services SSIS package and run ssis package to complete data import into SQL Server database.
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
In this SQL Server tutorial, I want to show how SQL developers can create SQL Server SSIS package 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 create view with SchemaBinding enables sql developers create database view objects that will prevent changes in the dependent database objects. This will result in more stable sql View 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. Levenshtein distance algorithm has implemantations in SQL Server also. Levenshtein distance sql functions can be used to compare strings in SQL Server by t-sql developers. The term Levenshtein distance between two strings means the number of character 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
In this SQL tutorial, sql developers will find an sql dynamic query example. I will try to build a dynamic sql query in SQL Server which can be altered and placed in a SQL Server stored procedure
SQL Server Tools
LiteSpeed Backup SQL Server Backup Compression Tool
In many companies SQL Server database administrators in IT departments prefer to use third-party SQL Server backup compression software and sql backup compression tools for SQL Server. LiteSpeed Backup SQL Server Backup Compression tool is one of the mature tools in the market that makes the sql backup and restore database tasks easier for database administrators.
SQL Server 2012
How to Install SQL Server 2012 - Microsoft SQL Server 2012 Installation Guide
Microsoft has released the next version of SQL Server, code-name SQL Server Denali, Microsoft SQL Server 2012 CTP 1 recently. I also download SQL Server 2012 CTP1 from Microsoft downloads and while installing SQL Server 2012 I tried to take some notes. I hope SQL Server database professionals like administrators and t-sql developers find my SQL2012 setup notes useful.
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. It is better to install the Microsoft SQL Server 2012 requirements before starting MS SQL2012 installation or before you download SQL Server 2012 Denali Setup files.
SQL Server 2012
Download SQL Server 2012 Sample Databases including AdventureWorks Database
Microsoft has released the Microsoft SQL Server 2012 CTP1, the first Community Technology Preview (CTP) of next version of MS SQL Server. The release of Microsoft SQL Server 2012 CTP1, and the the coming closer of the RTM release date SQL Server 2012 sample databases are being more required for SQL Server administrators and T-SQL programmers.
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
T-SQL developers frequently require the sql select count of the resultset beside the rows data itself. Here is a small SELECT statement trick to return the total rows count within the returned result set
SQL Tutorial - COALESCE
SQL COALESCE Function Example
T-SQL developers sometimes require to test if a group of sql parameter values are NULL or not. SQL developers can use the SQL COALESCE function which returns 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. LEFT and RIGHT sql string functions enable sql developers to take left part of a string variable or column, or extract right part of a string
Microsoft SQL Server 2008 R2 Released to Manufacturing
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 tutorial, I want to give SQL Server trigger example in SQL Server 2008 R2. SQL developers can also use this SQL trigger example code with a minor change in MS SQL Server 2008 and in MS SQL Server 2005. In this SQL Server trigger example, the aim of the sql trigger is to keep history of changes on price column in a table. When a new record is inserted the SQL Server trigger will also create a new row in sql log table. When the data row is updated in the sql table, sql trigger will check if certain column is also updated. Here in this example, sql trigger will check if price column is updated. If certain column is updated or changed, sql trigger will also update the log table. If data row in main sql database table is deleted, then log table will also be updated to reflect this sql delete operation in log 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. What makes SQL Server table-valued functions more useful in some situations than sql views or sql stored procedures is like every sql function, a t-sql table-valued function 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 MS 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
How to Debug Stored Procedure in SQL Server 2008
Just to make a short introduction for how to debug stored procedure in SQL Server 2008, I code the below 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
n your SQL Server database you have two database tables to store expenses and payments for closing expense amount.
You want to list the expenses of a customer using t-sql by querying your sql tables. This list will also add the payments done by the same customer to close these expenses. Each payment will be listed as new columns beside the expenses records.
SQL CLR String Split Function
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR It is one of the best enhancements of Microsoft SQL Server 2005 on t-sql development. 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.
In this sql clr tutorial, we will see VB.NET codes of a SQL Server CLR function.
And later we will create sql split function referring that sql CLR string split function.
T-SQL xp_cmdshell
List Directory Files using T-SQL xp_cmdShell Stored Procedure
In this sql tutorial, developers can find a short t-sql script which can be used 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. Let's start first by defining a sample sql xml structure which contains a list of SQL programming and administration books.
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. Taking sql backup of all sql databases on a SQL Server can be considered as a repeatitive task for many administrators. So it is certain that an SQL Server backup script using sp_Msforeachdb undocumented stored procedure will make it sql backups easier for many 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 your data in your database which can be used to restore your data in case of a failure in your production environment or in your data platform. SQL Server Backup Administrators are responsible from regularly taking sql backups of databases in SQL Server instances. Database backups can be stored on disks or on backup tapes according to the medium that is accepted in backup and recovery scenarios.
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. It is sometimes a requirement especially for web developers to call vb script from SQL Server engine and execute vbscript from sql code. 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, the old school is still common among SQL Server administrators and t-sql developers.
Let's make an example and 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
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. Let's define a sample sql xml variable @xml using type definition XML. Our sample SQL XML data contains a list of SQL Server 2008 books published by various publishing companies.
Import XML into SQL Server
How to Import XML into SQL Server 2005 or SQL Server 2008
In SQL XML data can be queried with new t-sql xml query improvements easier than ever. 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, I got the following error message from the SQL engine. "A correlation name must be specified for the bulk rowset in the from clause."
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. Since Database Mail feature is first introduced to administartors and developers with MS SQL Server 2005, SQL Server administrators and t-sql developers can use the below sp_send_dbmail example sql code on a Microsoft SQL Server 2005, MS SQL Server 2008 or SQL Server 2008 R2 instance.
SQL Server 2008 Certification
Microsoft SQL Server 2008 Certification Exams
In this certification guide, candidates will find the list of Microsoft SQL Server 2008 Certification Exams giving credits for MS 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 MS SQL Server 2005 and later versions (SQL Server 2008, SQL Server 2008 R2), t-sql developers and sql administrators can use below t-sql select script in order to list SQL Server indexes defined for a sql table.
SQL Server 2008 R2
AdventureWorks Database - Download Microsoft SQL Server 2008 R2 Sample Databases
After the RTM release of Microsoft SQL Server 2008 R2, the new data platform of Microsoft, related product releases are following each other. Microsoft has released sample databases for Microsoft SQL Server 2008 R2 on for SQL Server administrators, t-sql developers, BI developers and IT professionals. You can free 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. TSQL Try Catch in SQL Server 2005 enables SQL developers to have more control on error and exception handling in Microsoft SQL Server 2005 and MS SQL Server 2008 databases
SQL Server 2008 R2
Microsoft SQL Server 2008 R2 Upgrade Advisor Download
If you plan to upgrade to MS SQL Server 2008 R2, you should better choose Install Upgrade Advisor first. After downloading and installing Upgrade Advisor, you can run SQL Server Upgrade Advisor. Running SQL2008 R2 Upgrade Advisor will enable administrators to see if the current SQL Server instances will experience problems related with SQL Server 2008 R2 upgrade.
SQL Server 2008 R2
What is new in Microsoft SQL Server 2008 R2 Reporting Services
Microsoft has released MS SQL Server 2008 R2 recently. Now Business Intelligence (BI) developers want to learn what is new in Microsoft SQL Server 2008 R2 Reporting Services features.
Here is a short list of new features in Reporting Services 2008 R2.
Shared Data Sets
Report Part Gallery
Sparklines, Data Bars and Indicators
Aggregation on Aggregates
Rendering Features
Browser Support
SQL Server 2012
MS SQL Server 2012 aka Denali, Next Version of SQL Server
Microsoft has long ago started on project named Denali, codename of MS SQL Server 2012. If project goes as planned the successor the new SQL Server 2012 version will be available for use in the early months of year 2012. So speculations about the name as Microsoft SQL Server 2010 or SQL Server 2011 will be meaningless.
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
In this SQL Server tutorial for sql developers, I'll show t-sql codes that can be used to create 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
SQL developers frequently require to select from sql stored procedure execute results. This t-sql tutorial will display a sample t-sql script using SQL Server OPENQUERY which show how to sql select from SQL Server stored procedure. The second example will be similar to SQL OpenQuery but this time in sql codes we will use SQL OPENROWSET in order to select from stored procedure.
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 MS 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
If you want to drop a table which might have already created in the SQL database, you can use sql DROP TABLE command. The sql drop table command will run successfully if sql table table exists. Contrarily if the sql table does not exist in SQL Server database, then sql DROP TABLE command will cause sql engine to throw an error.
T-SQL Utilities
SQL Object Where Used List by a Search SQL Database Objects Text
It is a very common requirement for SQL Server database administrators and sql developers 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
In this sql tutorial, t-sql developers can find sql codes for extracting file name from fullpath of a file.
SQL Master Data
List of US States - US States Abbreviations and Names
SQL Master Data
Türkiye Şehir Listesi SQL Tablosu

download SQL Server 2012 for free evaluation






Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems