Transact-SQL

Clustered index in SQL Server

What is a SQL Server clustered index ? The definition of a SQL Server index is a structure, i.e. a file, stored physically in a disk. It’s like the master data file and the log data file that composes a database. Basically, to find the associated lines or rows faster, the index stores one or more key columns. So what’s the definition of a

Transact-SQL

Split text with XML or STRING_SPLIT function in SQL Server

How to split a delimited text with XML or STRING_SPLIT function in SQL Server ? The string to split can have a comma or a semicolon delimiter. Using an SQL Server query, you can cut a string compounded of text, delimited by commas, semicolons, tabulations, dash, underscore, or even dots. It can basically be any character that defines a delimited text. Indeed, for example,

Transact-SQL

Line break with SQL Server

How to remove line break from a text with SQL Server ? If you store a string as a variable or a column in SQL Server, then it can contain line breaks. For example, if you export the string to excel, your line will be cut. Indeed, with SSMS, if you select data containing carriage return or new lines, then the screen displays the

Transact-SQL

Split text after character with SQL Server Substring

How to split a text after a particular character with the SQL Server Substring function ? Like for example a file name file.txt to separate the file name from its extension. Indeed, it is obvious that splitting a string just before the point character must be done dynamically, i.e., whatever the position of the character is. How to split a text after a predefined

Transact-SQL

How to create a SQL Server function ?

How to create a simple SQL Server function ? This example of Transact-SQL code is to be copied and pasted. And use it as a syntax checklist. In fact, here is a script for creating an SQL function with a customer number as a parameter and the customer’s name as the result. T-SQL functions, like stored procedures, are at the heart of Microsoft database

Transact-SQL

SQL Server text with more than 8000 characters

How to manage SQL Server text with more than 8000 characters without errors? VARCHAR and NVARCHAR fields are limited to 8000 characters with SQL Server. Handle text fields longer than 8000 characters and you get the following message: “The text, ntext, and image data types are invalid for local variables. Here is a solution to get around this 8000 character limitation in T-SQL with

Transact-SQL

Display the modification date of a SQL Server table

How to display the modification date of a SQL Server table? This sample SQL query uses SQL Server system tables to detect the latest changes made to a SQL Server table or view. It displays the creation date and the last update date. Moreover, this query only takes into account the tables because it reads the data from the system table sys.tables. How to

Transact-SQL

Display the full month name with SQL Server

How to display the full month name from a date with SQL Server ? Which is to say with the complete month names like January, February, March, April, May, etc. written out in full. This query shows you how to get the entire month names from the current date or any other date. Indeed, with a simple SELECT GETDATE() you can display easily the current

Transact-SQL

Implicit conversion from data type XML to NVARCHAR is not allowed error

How to convert XML into NVARCHAR with SQL Server? To avoid the SQL Server error conversion from XML to NVARCHAR. The exact message is “Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.”. With SQL Server data, it is useful to combine numerical columns, alphanumeric strings and XML typed columns.   In addition, to

Transact-SQL

Violation of Unique Key constraint with SQL Server

How to avoid Violation of Unique Key constraint with SQL Server ? Insert or update data in an SQL Server table with a simple query? Here are two simple solutions to execute an update or insert and avoid errors. The SQL Server error ” Violation of UNIQUE KEY constraint . Cannot insert duplicate key in object . The duplicate key value is ” because