Sql Server

Read All SQL Server Log Files

You can use a script similar to the following to read through all SQL Server Log files: CREATE PROCEDURE SearchLogFiles (@LogType INT = 1, Filter NVARCHAR(MAX) = ”)ASBEGIN DECLARE @LogsTable TABLE (LogIndex INT, LogDate DATETIME, LogSize INT) DECLARE @LogRows TABLE (LogDate DATETIME, ProcessInfo NVARCHAR (4000), Test NVARCHAR (4000)) INSERT INTO

Using the STRING_AGG SQL Server 2017 Function

The STRING_AGG?SQL Server 2017 function performs grouped string concatenation. Here is an example: SELECT STRING_AGG(value, ‘ ‘) AS Result FROM (VALUES(‘Hannes’),(‘du’),(‘Preez’)) AS I(value); This returns: Hannes du Preez in the Result column

Using sys.dm_db_index_physical_stats

Using dm_db_index_physical_stats?returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. USE master; GO — If Student.Name does not exist in the database, the function returns NULL. — If NULL is specified as an OBJECT_ID, all objects in the database are

Transfer Data with JSON in SQL Server

You can transfer data with JSON in SQL Server. Based on an SQL query, you can output the results into JSON format. Here is a small example: DECLARE @JSONOutput NVARCHAR(MAX) = (SELECT * FROM Students FOR JSON auto, INCLUDE_NULL_VALUES)Print @JSONOutput;This could potentially give the following result:[ { “StudentID”:1, “StudentName”:”Hannes”, “EnrolDate”:”2017-12-14T14:19:22.273″

Compress and Decompress SQL Functions

Compress: Compresses input data and returns the binary data of type VARBINARY(MAX) Decompress: Decompresses compressed input binary data and returns the binary data of type VARBINARY(MAX) Here is small example in SQL Server 2016: SELECT COMPRESS (‘Data To Compress’)SELECT DECOMPRESS (Binary Data To Decompress)

Using the STRING_SPLIT Function in SQL Server

SQL Server 2016 provides a way to split a string that is concatenated with a separator. For example: SELECT VALUE FROM STRING_SPLIT(“John|Mary|Dohe”, “|”) would return the values John, Mary and Dohe in 3 rows.

Allow SQL Server to Silently Truncate Data

SQL Server usually presents an error on an attempt to insert more data into a field than it can hold. It typically throws an error message similar to the following: Msg 8152, Level 16, State 14, Line 15 String or binary data would be truncated. The statement has been terminated.

No more posts to show