Hide SQL Server database engine instances from network discovery | Cluster instances | Availability Group Name

Hide SQL Server database engine instances from network discovery | Cluster Instances | Availability Groups name

SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. To prevent exposing an instance of the database engine to client computers that try to locate, we can use the following steps to hide the instance from SQL Server browser service. By using SQL Server Configuration Manager we can hide the instances.

• Open SQL Server Configuration Manager (SSCM).
• Expand SQL Server Network Configuration.
• Right-click on "Protocols for <instance_name>"( instance you want to hide) and then select Properties.
• On the Flags tab, in the HideInstance box, select Yes, and then click OK to close the dialog box. The change takes effect immediately for new connections.

Clustered Instances / Availability Group Name

If you hide a clustered instance or availability group name, cluster service may not be able to connect to SQL Server. This will cause the cluster instance IsAlive check to fail and SQL Server will go offline.

To avoid this, create an alias in all the nodes of the clustered instance or all instances that host availability group replicas to reflect the static port that you configured for the instance. For example, on an availability group with two replicas, on node-one, create an alias for the node-two instance, like node-two\instancename. On node-two, create an alias called node-one\instancename. The aliases are required for successful failover. Follow the below steps to create an alias.

• Open SQL Server Configuration Manager (SSCM).
• Expand SQL Server Native Client Configuration
• Right-click on Aliases, and then click New Alias.
• In the Alias Name box, type the name of the alias. Client applications use this name when they connect.
• In the Server box, type the name or IP address of a server. For a named instance append the instance name.
• In the Protocol box, select the protocol used for this alias. Selecting a protocol changes the title of the optional properties box to Port No, Pipe Name, or Connection String.

If you hide a clustered named instance, cluster service may not be able to connect to SQL Server if the LastConnect registry key (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0\LastConnect) has a different port than the port that SQL Server is listening on. If the cluster service is unable to make a connection to the SQL Server, you might see an error similar to the following:

Event ID: 1001: Event Name: Failover clustering resource deadlock.

References:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/hide-an-instance-of-sql-server-database-engine?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/create-or-delete-a-server-alias-for-use-by-a-client?view=sql-server-ver15

Thanks for your time. If you have any suggestions or queries, please comment below.

Read More

SQL Server Working with NULL | Null Functions | Expressions | Comparisons | Examples

SQL Server Working with NULL | Null Functions | Expressions | Comparisons | Examples 

What is a NULL value in SQL? 

In SQL, a NULL value helps to identify that particular attribute/column data is not available in the table. As per BOL, "Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database".

It is important that Null doesn't indicate any value such as Zero (0) or blank. NULL as meaning "unknown".

For example, In the employee data, the bonus field applies only to employees who are eligible for the bonus, and others it will be empty (NULL). 

Null values in the database tables

By default, all the columns in the table allow NULL value unless we specify NOT NULL constraint.  

During data insertion, if any optional field is missing then SQL Server places the NULL value in that field to denote an unknown or missing value. Note, The NULL value can't be applied if there is a default value set.

USE tempdb;
CREATE TABLE product(PRODUCT_ID INT NOT NULL, PRODUCT_NAME VARCHAR(45) NOT NULL, PCODE VARCHAR(45), PSTATUS INT DEFAULT 1)
INSERT INTO product VALUES (1,'Ear-Phones','CVR',2);
INSERT INTO product(PRODUCT_ID,PRODUCT_NAME) VALUES (2,'Mobile Chargers');
SELECT * FROM product;
*******************RESULT***********************
PRODUCT_ID  PRODUCT_NAME           PCODE          PSTATUS
----------- ---------------------- -------------- ---------- 
1           Ear-Phones             CVR            2
2           Mobile Chargers        NULL           1

(2 rows affected)

Null values in comparison

You can use IS NULL or IS NOT NULL comparisons to check for NULL values. For example 

SELECT * FROM product where PCODE IS NULL
*******************RESULT***********************
PRODUCT_ID  PRODUCT_NAME           PCODE          PSTATUS
----------- ---------------------- -------------- ---------- 
2           Mobile Chargers        NULL           1

SELECT * FROM product where PCODE IS NOT NULL
*******************RESULT***********************
PRODUCT_ID  PRODUCT_NAME           PCODE          PSTATUS
----------- ---------------------- -------------- ---------- 
1           Ear-Phones             CVR            2

Null values in a boolean expression

The default result of most operands (such as AND, OR, +, and -) is NULL when either operand is NULL. The AND operator returns TRUE only if all conditions are also TRUE. The OR operator return TRUE when one or more conditions are also TRUE.

	TRUE	FALSE	NULL
AND
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL
OR
TRUE	TRUE	TRUE	TRUE
FALSE	FALSE	FALSE	NULL
NULL	TRUE	NULL	NULL

Arithmetic Expressions

10 + NULL = NULL
NULL + (5*4) = NULL
10 -(5*4)/10 - NULL = NULL

String Expressions

'mahesh'+' '+ NULL = NULL

CONCAT_NULL_YIELDS_NULL

Controls whether concatenation results are treated as null or empty string values.
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'Mahesh' + NULL gives NULL.
When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'Mahesh' + NULL gives Mahesh.

set CONCAT_NULL_YIELDS_NULL off
select 'mahesh'+'_'+NULL 
set CONCAT_NULL_YIELDS_NULL on
 -- Result: mahesh_ 

SQL Server NULL functions

SQL Server includes below functions to handle nulls when used within expressions.

• ISNULL()
• COALESCE()
• NULLIF()

ISNULL()

This function validates whether a source expression is not equal to NULL, then the ISNULL function will return source value. However, if the source expression is NULL, then the replacement value will be returned. 

Syntax: ISNULL(source_expression, replacement_value)

SELECT ISNULL('SATHISH',NULL) AS RESULT1
select ISNULL(NULL,'MAHESH') AS RESULT2
DECLARE @NAME varchar(5) = NULL
SELECT ISNULL(@NAME,1) AS RESULT3
DECLARE @ID INT = NULL
SELECT ISNULL(@ID,'A') AS RESULT4
RESULT1
-------
SATHISH

RESULT2
-------
MAHESH

RESULT3
-------
1

RESULT4
-----------
Msg 245, Level 16, State 1, Line 33
Conversion failed when converting the varchar value 'A' to data type int.

As you see the last select statement is failed, this will happen if the source type is not matched with the replacement value type. Here ISNULL function tries to convert the replacement value with the source type.

COALESCE()
This function accepts a list of expressions and returns the first non-null value. COALESCE expects at least one of the expression that is not null, If all the expression contains NULL then it returns an error.

Syntax: COALESCE(expression1, expression2, ....)

SELECT COALESCE(NULL,NULL,NULL) AS RESULT1
SELECT COALESCE(NULL,NULL,'MAHESH') AS RESULT2
DECLARE @NAME varchar(5) = NULL
SELECT COALESCE(NULL,@NAME,1) AS RESULT3
DECLARE @ID INT = NULL
SELECT COALESCE(NULL,@ID,'A') AS RESULT4

RESULT1
-------
Msg 4127, Level 16, State 1, Line 37
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

RESULT2
-------
MAHESH

RESULT3
-----------
1

RESULT4
-----------
Msg 245, Level 16, State 1, Line 47
Conversion failed when converting the varchar value 'A' to data type int.

NULLIF()
This function accepts two expressions. If two are equal, then it returns a null; other it returns the first expression. NULLIF function can't allow null in the first expression.

Syntax: NULLIF(expression1, expression2)

select NULLIF('MAHESH','MAHESH') AS RESULT1
select NULLIF('MAHESH','SATHISH') AS RESULT2
select NULLIF(null,'A') AS RESULT3
SELECT NULLIF('MAHES',1) AS RESULT4

RESULT1
-------
NULL

RESULT2
-------
MAHESH

RESULT3
-------
Msg 4151, Level 16, State 1, Line 40
The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.

RESULT4
-------
Msg 245, Level 16, State 1, Line 41
Conversion failed when converting the varchar value 'MAHES' to data type int.

Thanks for your time. If you have any suggestions or queries, please comment below.

Read More

SQL Server RAND() function | Generating random number within the specific range

SQL Server RAND() function | Generating random number in a range

In this article, we will see RAND() function and also see how to generate a random number in a range using RAND() function.

RAND() Function

The RAND function can be used to return a random number. It will return a value between 0 and 1 such as value greater than 0 and less than 1 ( 0> value <1). By default, this function will return a completely random number, if the seed value is provided then a repeatable sequence of the random number will be returned.

Syntax: RAND([seed])

Return Type: Float

Examples

SELECT RAND()    -- Result - 0.182458908613686
select RAND(10)  -- Result - 0.713759689954247
select RAND(-10)  -- Result - 0.713759689954247

Generating Random Number within the specific range

In development, we often need to generate a random number, sometimes random number in a range e.g. 1 to 1000. 

Random Decimal Range

To create a random decimal number between two values (range), you can use the following formula:

SELECT (MAX_VAL-MIN_VAL) * RAND() + MIN_VAL

Where MAX_VAL is the maximum/upper/highest value and MIN_VAL is the minimum/lower/smallest value that you want to generate a random in a range

Random Integer Range

To create a random integer number between two values (range), you can use the following formula:

SELECT ROUND((MAX_VAL-MIN_VAL) * RAND() + MIN_VAL,0)
OR 
SELECT FLOOR((MAX_VAL-MIN_VAL) * RAND() + MIN_VAL)

Where MAX_VAL is the maximum/upper/highest value and MIN_VAL is the minimum/lower/smallest value that you want to generate a random in a range. Both methods will results the same output.

Examples

Let us see the queries to return a random number between 2000 and 3000.

DECLARE @MIN_VALUE INT = 2000
DECLARE @MAX_VALUE INT = 3000
-- Random Decimal Range
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND() + @MIN_VALUE
-- RESULT 2182.45890861369
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND(10) + @MIN_VALUE
-- RESULT 2713.75968995425
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND(-10) + @MIN_VALUE
-- RESULT 2713.75968995425
-- Random Integer Range
SELECT ROUND(((@MAX_VALUE - @MIN_VALUE) * RAND() + @MIN_VALUE), 0)
-- RESULT 2182
SELECT FLOOR(((@MAX_VALUE - @MIN_VALUE) * RAND(100) + @MIN_VALUE))
-- RESULT 2715
SELECT FLOOR(((@MAX_VALUE - @MIN_VALUE) * RAND(12345) + @MIN_VALUE))
-- RESULT 2943

Thanks for your time. If you have any suggestions or queries, please comment below.

Read More

Difference between truncate table and delete table commands with examples

Difference between truncate and delete commands with examples

Truncate

Truncate is a DDL command. This command is used to delete all the records from the table permanently.

Syntax: TRUNCATE TABLE <TABLE_NAME>;

Delete

Delete is a DML command. This command is used to delete all the records and also delete specific records by using where clause from the table.

Syntax: DELETE FROM <TABLE_NAME> [WHERE <CONDITION>];

Truncate	Delete
DDL command	DML command
Faster	Slower
Reset identity column (Schema Lock)	Leaves identity column
Permanent deletion. Rollback the data is not possible.	It is a temporary deletion (Full-logged recovery model). Rollback the data is possible
Specific record deletion is not possible.	Delete a specific record by using where clause.
Doesn't invoke delete triggers.	Invoke delete triggers.
Removes all pages	Can leave the empty pages, which can be removed by using the table rebuild.
Table lock ( inc schema lock)	Exclusive lock
Removes all rows. Deallocates the data pages in a table and only this deallocation is stored in the transaction log.	Removes one row at a time and the deleted row is stored in the transaction log.

Let us create a table and insert some sample data.

CREATE TABLE TRUN_TBL(ID INT IDENTITY NOT NULL PRIMARY KEY, NAME VARCHAR(8000) NOT NULL DEFAULT REPLICATE('A',5000))
CREATE TABLE DEL_TBL(ID INT IDENTITY NOT NULL PRIMARY KEY, NAME VARCHAR(8000) NOT NULL DEFAULT REPLICATE('A',5000))
GO
INSERT INTO TRUN_TBL DEFAULT VALUES
GO 20
INSERT INTO DEL_TBL DEFAULT VALUES
GO 20
-- TRUN_TBL statistics
exec sp_spaceused TRUN_TBL
dbcc showcontig(TRUN_TBL)
-- DEL_TABL statistics
exec sp_spaceused DEL_TBL
dbcc showcontig(DEL_TBL)

As you see, there are 20 pages (3 extents) and 200kb data size occupied in the tables. 

Next delete all the records in both the tables and check the statistics again.

truncate table TRUN_TBL
delete from DEL_TBL

Now the TRUN_TBL table has 0 pages that mean it deallocates all the data pages.

The DEL_TBL table has 1 page (72kb), as delete command can leave empty pages in a table since an empty page requires table lock which doesn't necessarily happen. We can remove this unused data by using the alter table rebuild.

ALTER TABLE DEL_TBL REBUILD

Thanks for your time.

Read More

SSMS query editor adding line number | GOTO Line number | SQL Server Management studio

SSMS query editor adding line numbers and GOTO line number

Adding Line Numbers

In SQL Server management studio we have a provision to enable line number in the query editor. 

For long scripts, it can be helpful in referring to a particular line number. It can also more useful if we are preparing a document for script instructions for others how to modify the query or trying to describe how a query will work etc.

To enable line numbers, 

•  Tools -> Options.
•  Expand Text Editor ->  Transact-SQL. 
• Click on the General tab
• On the right side, select the option Line Number.

GOTO Line Number

For long scripts, we can go directly to the particular line number by using the shortcut "Ctrl + G" or on the Edit menu, "Go To -> Go To Line". By clicking, it will popup a dialog and ask for a line number. In the dialog, the max line number is shown. By clicking Ok, it takes straight to entered line number.  

Read More

SQL Server Heap Tables | Storage | Forwarding Pointers | Rebuild Heap

What is Heap Table?

SQL Server stores a table as a heap or B-tree. If you create a table without index then it is stored as a heap. Similarly, If you create a table with a clustered index, then it is stored as a B-tree. A table can never be a heap and a clustered table at the same time. Simply, a heap table is one without a clustered index.

A tables use a heap until a clustered index created on the table. Within the heap tables, the data is unsorted. The server does not guarantee nor maintain any sorting order of the data in the heap tables. When we insert data into heap tables, SQL Server tries to fill pages as much as possible.

Logically, the heap is comprised of an Index Allocation Map (IAM) that points to all pages within the heap. Within the heap, there is no linking or organization between the pages. All reads and writes must consult the IAM first to read all pages within a heap. Heap tables are best for staging tables. 

In the Heap table, all the rows are identified internally using the heap row identifier. The row identifier is an actual physical location composed of three values, such as

• FileID
• PageNum
• SlotNum

If the base table is not clustered, then any non-clustered index can store the heap row identifier in every level of the index, which is used to point back to the full row of the base table.  

Advantages: In Heap tables, inserting data is very fast compared to clustered index tables.

Disadvantages:  

• Forwarding pointers
• Random I/O reads
• Consumes more space when not rebuild

Query to list all tables using the heap 

SELECT 
    stbl.name, sidx.type_desc
FROM
    sys.tables stbl
        INNER JOIN
    sys.indexes sidx ON stbl.object_id = sidx.object_id
        AND sidx.type = 0
        AND stbl.type = 'U'

Storage

When we are inserting data into heap tables, SQL Server scans for page free space (PFS) page before inserting it. If the data page contains enough space then it stores a new row into the existing page. If it does not have sufficient free space, SQL Server allocates a new page. 

SQL Server using the low value from the PFS free space percentage during the estimation such as SQL Server would not put a new row to the page if its size exceeds 20 percent of the page size. For example, if the data page store 4500 bytes of data, and has 3560 bytes of free space available, PFS would indicate that the page is 51-80 percentage full (0x02). SQL Server doesn't allow a new row to the page as it exceeds 20 percent free space ( 8,060 * 0.2 = 1,612 ).

CREATE TABLE dbo.heap_demo(val varchar(8000))
GO
INSERT INTO dbo.heap_demo(val) VALUES (replicate('1',4489))
GO 10
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.heap_demo'),0,null,'DETAILED');

Inserted 10 rows in the table and the data stored for each row is 4500 bytes. SQL Server allocates 10 data pages such as one page per row. As PFS indicated that the pages are 51-80 percentage full.

Now insert 1000 bytes data row in the table, which is about 12.5 percent of the page size. As a result SQL Server nows that record will fit in one of the existing pages ( they all have at least 20% free space).

INSERT INTO dbo.heap_demo(val) VALUES (replicate('1',1000))

Now insert 2000 bytes data row in the table, which is about 25 percent of the page size. SQL Server doesn't fit the row in any page as the row exceeds 20 percentage, as a record, it allocates a new page.

INSERT INTO dbo.heap_demo(val) VALUES (replicate('1',2000))

Here SQL Server unnecessarily allocates new data pages, leaving large amounts of free space unused. SQL Server eventually fills empty spaces with smaller rows.

Forwarding Pointers

When you update the row in the heap table, SQL Server tries to accommodate it on the same page. If there is no free space available, SQL Server moves the new version of the row to another page and replaces the old row with a special 16-byte row called a forwarding pointer. The new version of the row is called a forwarded row.

Forwarding pointers improves I/O operations such as duplicate reads and prevent updates of non-clustered index keys.

Let's create a table and insert three rows

CREATE TABLE dbo.heap_demo_fp(id int,val varchar(8000))
GO
INSERT INTO dbo.heap_demo_fp(ID,val) VALUES (1,replicate('1',1500))
INSERT INTO dbo.heap_demo_fp(ID,val) VALUES (2,replicate('1',1500))
INSERT INTO dbo.heap_demo_fp(ID,val) VALUES (3,replicate('1',3000))
GO
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.heap_demo_fp'),0,null,'DETAILED');

Here three rows size (6000 bytes) not exceed the PFS indication percentage such as 20% free space available, so all the three rows fill on the same page.

Next, update one of the table rows and increase their size ( 5000 bytes). The new version of the rows would not fit into the page anymore, which introduces the allocation of the one new page and one forwarding pointers.

update dbo.heap_demo_fp set val=replicate('1',5000) where ID=1;
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent,forwarded_record_count 
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.heap_demo_fp'),0,null,'DETAILED');

Rebuild Heap Tables

In the case of large heap tables, we can see the considerable value of these logical reads that can be causing performance issues for the data retrieval. If the forwarding pointer is more then more the logical reads. To resolve we need to monitor the heap tables and rebuild it.

For example, Let's insert 65,536 rows in the table

truncate table heap_demo_fp;

 ;with N1(C) as (select 0 UNION ALL select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.heap_demo_fp(ID)
select ID from IDs;
  
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent,forwarded_record_count from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.heap_demo_fp'),0,null,'DETAILED');
set statistics io on
select * from dbo.heap_demo_fp
set statistics io off

 

(65536 rows affected)
Table 'heap_demo_fp'. Scan count 1, logical reads 106.

As you see, there are 106 pages in the table and as a result, SQL Server performs 106 reads during IAM scan. Let's update the table and introduce forwarding pointers.

update dbo.heap_demo_fp set val=replicate('1',500);

select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.heap_demo_fp'),0,null,'DETAILED');

set statistics io on
select count(*) from dbo.heap_demo_fp
set statistics io off

 

Now the table has 4461 pages and it requires almost 70k reads to perform a scan. As you see a large number of forwarding pointer leads to extra I/O operations and reduces the performance of the query accessing the data.

To get rid of the forwarding pointers is by rebuilding the heap table. 

ALTER TABLE dbo.heap_demo_fp REBUILD;

select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent,forwarded_record_count from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.heap_demo_fp'),0,null,'DETAILED'); 

set statistics io on
select count(*) from dbo.heap_demo_fp
set statistics io off

After the table is rebuilt, the table now uses 4377 pages and no forwardings records.

Conclusion

Heap tables are useful in staging tables where inserting data will be very fast compare to clustered tables. But heap tables will perform slow compare to clustered tables. It is recommended to use clustered tables. According to our business needs, you can choose either clustered or heap tables.

Thanks for your time.

References:

https://aboutsqlserver.com/2013/12/17/sql-server-storage-engine-heap-tables/

https://www.sqlshack.com/forwarded-records-performance-issue-in-sql-server/

Read More

SQL Server accent-insensitive sorts and string comparisons | collation

SQL Server accent-insensitive sorts and string comparisons

In general, SQL Server default collation is SQL_Latin1_General_CP1_CI_AS, So by default SQL Server database is always insensitive to the case(CI) and sensitive to the accent(AS) of the characters. In sometimes we need to do accent-insensitive comparisons and sorts. To do such operations we can use the COLLATE clause followed by collation name where collation should be accent-insensitive (AI). We can check the collation case and accent information in the name itself. Collation name contains _CS - case-sensitive; _CI - case-insensitive; _AS - Accent-sensitive;_AI - Accent-insensitive. 

In SQL Server, collation can be set at different levels such as 

• SQL Server Level
• Database Level
• Column Level.

To check the default collation of the SQL Server

SELECT SERVERPROPERTY('COLLATION') as collation_default
OR
EXECUTE sp_helpsort

To list all collations supported by SQL Server

SELECT name, description FROM fn_helpcollations()

To list all Accent-insensitive collations supported by SQL Server

SELECT name, description FROM fn_helpcollations() where name like '%_AI%';

Accent-insensitive sorts

Reference : https://dba.stackexchange.com/questions/199853/accent-sensitive-sort/199933#answer-199865 Thanks, Solomon Rutzky

In general, the Unicode collation algorithm (UCA) allows for complex and multi-level sorting, such as

• Sorting is not Comparison:
  
  • Determining whether two strings are the same or different is fairly straight forward (given a particular locale/language and set of sensitivities). But determining the order of 2 or more strings can be highly complex.
• Sorting is done in a series of steps, with each step applied to the entire string, not character by character:
  
  • Standard: sort base characters (regardless of accent and case differences)
  • IF Accent-sensitive, apply accent/diacritic weights
  • IF Case-sensitive, apply casing weights

Example

USE [tempdb];
create table names(name varchar(45) collate SQL_Latin1_General_CP1_CI_AS)
insert into names(name) values ('Joel'),('JoEl'),('Joêl')

-- order by name 
select name from names order by name
Output
Joel
JoEl
Joêl

-- order by name with accent-insensitive collate
select name from names order by name collate SQL_Latin1_General_CP1_CI_AI
Output
JoEl
Joel
Joêl
Accent-insensitive search
In sql server query, if you filter the data on column name='Joel' with accent-sensitive collation,then we end up retrieving only Joel records here we missed Joêl record. If we filter the data with accent-insensitive collation then we retrieve all the records matches with Joel.
Example

USE [tempdb];
create table names(name varchar(45) collate SQL_Latin1_General_CP1_CI_AS)
insert into names(name) values ('Joel'),('JoEl'),('Joêl')
-- Search by name Joel
select * from names where name='Joel' 
Output
Joel
JoEl
-- search by name Joel with accent-insensitive collate 
select * from names where name='Joel' collate SQL_Latin1_General_CP1_CI_AI
Output
Joel
JoEl
Joêl
That's all about  SQL Server Accent-Insensitive search and sorting. Thanks for your time.
Let me know if you have any questions, suggestions, or comments.

Read More

SQL SERVER DBCC Commands | Database console commands | Documented List

DBCC Commands

What is DBCC?

DBCC stands for Database Console Commands / Database Consistency Checker. 

DBCC commands are used for checking database integrity, troubleshooting issues, performance activity, and performing maintenance exercises on the database, tables, indexes, and filegroups, etc. DBCC commands help to understand what's happening inside SQL Server internally. DBCC commands are grouped into the following categories

• Maintenance:- Perform maintenance tasks on databases, indexes, or filegroups
• Informational:- Display SQL Server information
• Validation:- validate operations on databases, tables, indexes, catalogs, filegroups, or allocation of database pages.
• Miscellaneous:- enabling trace flags or removing a DLL from memory.

Apart from the documented DBCC commands, there are undocumented commands too but here we listing documented commands only.

First and most important command DBCC HELP which provides you the information on a specific DBCC command 

To List, all DBCC commands 

DBCC HELP ('?')

To check the syntax of DBCC command 

DBCC HELP ('command_name')

Eg: DBCC HELP ('CHECKDB')
dbcc CHECKDB 
(
    { 'database_name' | database_id | 0 }
    [ , NOINDEX
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
            [ , [ PHYSICAL_ONLY ] ]
            [ , [ DATA_PURITY ] ]
            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]
            [ , [ MAXDOP = <dop> ] ]
        }
    ]

Following are the list of documented DBCC commands

Note: *  - Deprecated

Informational Statements

1. * DBCC SHOWCONTIG(table_name, index_name):- Provides fragmentation information for tables, views, or indexes.
   This command is deprecated and you should use sys.dm_db_index_physical_stats.
2. DBCC OPENTRAN:- Shows information about the oldest running transaction, oldest running distributed transaction, and oldest running non-distributed transaction.
3. DBCC SQLPERF(LOGSPACE):- Displays transaction log space usage statistics for all databases hosted by an instance.
4. DBCC INPUTBUFFER(SPID):- Returns the last command executed by a client.
5. DBCC OUTPUTBUFFER(SPID):- Returns the results of the last command executed by a client in hexadecimal and ASCII format.
6. DBCC TRACESTATUS:- Displays the active trace flags.
7. DBCC PROCCACHE:- Reports some basic statistics about the procedure cache.
8. DBCC USEROPTIONS:- Returns the active SET options for the current connections.
9. DBCC SHOW_STATISTICS(table_name, index_name):- Provides current query optimization statistics for a table or indexed view.

Validation Statements

1. DBCC CHECKALLOC('database_name'[, NOINDEX | REPAIR]):- Performs a consistency check of disk space allocation structures.
2. DBCC CHECKFILEGROUP([ {'filegroup_name' | filegroup_id} ] [, NOINDEX]):- Verifies the allocation and structural integrity of indexed views and tables in a specific filegroup.
3. DBCC CHECKCATALOG(db_name):- Checks catalog consistency of online databases.
4. DBCC CHECKIDENT('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ]):- Verifies and, if necessary, change the identity value for a specific table.
5. DBCC CHECKCONSTRAINTS( {'table_name' | tab_id | 'constraint_name' | constraint_id }):- Verifies the integrity of a specific constraint or all constraints on a table within the current database.
6. DBCC CHECKTABLE('Table'_name):- Performs physical and logical integrity checks on the table and all its nonclustered indexes (unless the NOINDEX option is used).
7. DBCC CHECKDB(('database_name'[, NOINDEX | REPAIR])):- Checks the physical and logical integrity of all objects in a specific database. It runs DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG.

Maintenance Commands

1. DBCC CLEANTABLE('database_name'|database_id, 'table_name'|table_id, [batch_size]):- Reclaims space from variable-length column (varchar,nvarchar, text, or ntext) that was dropped from the table.
2. * DBCC INDEXDEFRAG({database_id | database_name | 0}, {table_id | table_name}, {index_id | index_name}):- Defragments indexes.
   This command is deprecated and you should use REORGANIZE option of ALTER INDEX.
3. * DBCC DBREINDEX('table_name' [, index_name [, fillfactor ]]):- Rebuilds one or more indexes for a table.
   This command is deprecated and you should use REBUILD option of ALTER INDEX.
4. DBCC SHRINKDATABASE({ 'database_name' | database_id | 0 } [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ]):- Shrinks the size of all data and log files of the specified database.
5. DBCC DROPCLEANBUFFERS:- Cleans the memory of any buffered data.
6. DBCC SHRINKFILE({'Filename | File ID'} {[, emptyfile | [ [ , target_size] [, {NOTRUNCATE | TRUNCATEONLY } ]]}):- Shrinks a specified data or log file .
7. DBCC FREEPROCCACHE:- Clear an entire plan cache, remove specific plans from the plan cache by specifying a plan or SQL handle.
8. DBCC UPDATEUSAGE ({database_name}[, Table_name [,index_id]]):- Reports and corrects pages and row count inaccuracies in the catalog views.

Miscellaneous Commands

1. DBCC dllname (FREE):- Unloads the specified extended stored procedure dll from memory.
2. DBCC HELP:- Provides information on a specific DBCC command.
3. DBCC FLUSHAUTHCACHE:- This command works only in Azure SQL Database Empties the database authentication cache containing information about logins and firewall rules, for the current user database in SQL Database.
4. DBCC TRACEOFF:- Turns off the specified trace flags.
5. DBCC FREESESSIONCACHE:- Clears the cache used by the distributed queries.
6. DBCC TRACEON:- Turns on the specified trace flags.
7. DBCC FREESYSTEMCACHE:- Clears the unused cache entries from all caches include plan cache.
8. DBCC CLONEDATABASE(source_db,target_db):- Generates a schema-only clone of a database.

That's all about  SQL Server DBCC commands. Thanks for your time.

Let me know if you have any questions, suggestions, or comments.

Read More

SQL Server error logs | Intro | Configuration | Recycle | Querying

SQL SERVER ERROR LOGS

Introduction

In the general case, error logs are useful to troubleshoot system problems. Similarly, SQL Server Error Logs also useful in troubleshooting issues with the database server.  

Error Logs maintains events raised by the SQL Server database engine or Agent. It contains both system and user-defined events data. 

Types of error logs

1. SQL Server Log 
2. Agent Log

By default following events are recorded in error logs.

1. SQL Server startup events 
2. Database recovery and Maintenance related activities 
3. Backup and restore information
4. User-defined error message which has WITH LOG clause
5. Profiler and trace flags turning start or stop 
6. Other

Error logs are present in the LOG folder of the respective instances. The default location is "<Program-Files>\Microsoft SQL Server\<INSTANCE_NAME>\LOG\". Using SERVERPROPERTY() command to know the current error log file location

SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Err_log_file_loc';

SQL Server by default maintains a backup of previous six logs, naming each archived log file sequentially such as ( physical : "ERRORLOG.<seq_no>"; Logical : "Archive #<seq_no> - timestamp" ). The current error log file named ERROR ( logical: "Current - timestamp" ).  

• 0 - current error log
• 1-6 - Archive log files

Configuring

In SQL Server error logs configuration,  

• Limit the number of error logs to be archived. Maximum error logs can configure up to 99. 
• Set the maximum size of the error log file in kb. Value 0 means the log file size is unlimited. 

To configure 

• Go to Object Explorer ->  Expand the respective instance
• Management
• Right-click on SQL Server Logs
• Configure
• Select checkbox " Limit the number of error log files..." 
• Set the Maximum number of logs value.
• Ok

Recycle

By default when the server was restarted the error logs are recycled automatically. We can recycle error log using sp_cycle_errorlog.

Querying

Query to list error log and their sizes

exec sp_enumerrorlogs

Here Archive # = Error Log file number ( 0 - current ; 1 - archive #1 ; ..) ; Date = timestamp of last entry in that file ; Log file Size (Byte) = Error log file size in bytes

SQL Server error logs can read using different methods such as SSMS and T-SQL queries. 

Using T-SQL

we can read error logs using below procs

• sp_readerrorlog
• xp_readerrorlog

SP_READERRORLOG

syntax: sp_readerrorlog <log_no>,<log_file_type>,<filter>

log_no: Error log number ( 0 = current  ; 1 = Archive #1, 2 = Archive #2..  )

log_file_type: 1 or null - SQL Server log ; 2- Agent log

filter: Search keyword

-- List all data in the current error log 

exec sp_readerrorlog 0 -- Here 0 refers current error log

-- List only agent log in the current error log

exec sp_readerrorlog 0,2 -- Here 2 refer agent log

-- In agent search keyword "startup" in the current log 

exec sp_readerrorlog 0,2,'startup'

Note: To execute these procedures, either SecurityAdmin server-level role or the View Server State permission is needed. 

XP_READERRORLOG

syntax: xp_readerrorlog <log_no>,<log_file_type>,<filter_1>,<filter_2>,<start_date>,<end_date>,<sort_order>

log_no: Error log number ( 0 = current  ; 1 = Archive #1, 2 = Archive #2..  )

log_file_type: 1 or null - SQL Server log ; 2- Agent log

filter_1: Search keyword

filter_2: Search keyword ( additional search filter. both conditions should be statisfied to execute { filter_1 and filter_2 }) 

start_date , end_date: Filter logs between start and end dates.

sort_order: ASC (Ascending) or DSC (descending) 

-- Searches for text warning in the current error log

exec xp_readerrorlog 0,1,N'warning'

-- Searches for text "Error" and "Severity: 16" in the current log between dates 2020-05-01 to 2020-05-10

exec xp_readerrorlog 0,1,N'Error',N'Severity: 16', N'2020-05-01 00:00:01.000', N'2020-05-10 00:00:01.000', 'ASC'

Using SSMS 

To read error logs using SSMS, we need to connect the instance and navigate to Management. Expand the Management folder, followed by SQL Server logs.

In the SQL Server Logs folder, you can see the logs - Current and Archive

Double-click on the desired error log file and it will open the error log in a separate window

Problem with accessing error log

• Error logs are not recycled due to log recycling agent job is not available or not running. 
• Enabling auditing or log backups also some time cause to grow log size huge.

Conclusion

This article helps you to understand the error logs and steps to configuring and methods for reading error logs and recycling techniques. 

That's all about  SQL Server Error Logs. 

Let me know if you have any questions, suggestions, or comments.

Read More