Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid
What is ISDATE(Expression) ?
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
Expression can be any character string , less than 4000 characters but it should not be Date datatype
Msg 8116, Level 16, State 1, Line 3
Argument data type date is invalid for argument 1 of isdate function.
Before we go ahead about these functions first I will introduce little bit about ASCII
ASCII stands for American Standard Code for Information Interchange. Computers can only understand numbers, so an ASCII code is the numerical representation of a character such as ‘a’ or ‘@’ or an action of some sort. ASCII was developed a long time ago and now the non-printing characters are rarely used for their original purpose
These are divided into three categories listed below in the table
The first 32 characters in the ASCII-table are unprintable control codes and are used to control peripherals such as printers.
Codes 32-127 are common for all the different variations of the ASCII table, they are called printable characters, represent letters, digits, punctuation marks, and a few miscellaneous symbols. You will find almost every character on your keyboard. Character 127 represents the command DEL.
There are several different variations of the 8-bit ASCII table. The table below is according to ISO 8859-1, also called ISO Latin-1. Codes 129-159 contain the Microsoft® Windows Latin-1 extended characters.
So the ASCII function in TSQL returns the (ASCII code)value of the first character of string expression
Syntax ASCII( string expression)
Char function does this in reverse converts the ASCII code value to an equivalent character
Syntax CHAR(integer expression)
Now we have the basics of the ASCII and CHAR let’s have some fun here
a simple task is we have to write a trigger which should not allow a column in the table to accept any invalid characters like “ã,ô,….” it should simply throw an error message saying invalid characters
if you look at the extended ascii codes table you will notice all the ASCII codes from 128 to 255 have invalid characters which we do not want them to be inserted in to the table
so now we have a table DBO.PRODUCT_CATALOG and the column is the name
in this we will exact similar logic we just used in the above ASCII() , CHAR() demo
CODE:
create
TRIGGER PRODUCT_INVALIDCHAR ON dbo.PRODUCT_CATALOG INSTEAD OF INSERT,UPDATE
AS
BEGIN
DECLARE @POSITION INT= 0
DECLARE @STRING VARCHAR(100)=(SELECT NAME FROM inserted)
WHILE @POSITION <LEN(@STRING) BEGIN
if(ASCII(SUBSTRING(@STRING,@POSITION+1,@POSITION+2))>124
AND
ASCII(SUBSTRING(@STRING,@POSITION+1,@POSITION+2))<= 255)
BEGIN
PRINT‘INVALID CHARACTER , PLEASE INSERT A VALID CAHRACTER’
RETURN
END
ELSE
BEGINSET @POSITION = @POSITION + 1
ENDEND
INSERT INTO dbo.PRODUCT_CATALOG
VALUES(@STRING)
END
(adsbygoogle = window.adsbygoogle || []).push({});
What is @@LOCK_TIMEOUT ?
Returns the current lock time-out setting in milliseconds for the current session
Syntax
now lets change the default value of @@lock_timeout
What is @@OPTIONS ?
Returns information about the current SET options
Why it is used?
SQL Server offers many built in Configuration functions and one of these functions, @@OPTIONS which allows you to get the current values that are set for the current session. When each connection is made the default values are established for each connection and remain set unless they are overridden by some other process
here are the list of set options
| Value | Configuration | Description |
|---|---|---|
| 1 | DISABLE_DEF_CNST_CHK | Controls interim or deferred constraint checking. |
| 2 | IMPLICIT_TRANSACTIONS | For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections. |
| 4 | CURSOR_CLOSE_ON_COMMIT | Controls behavior of cursors after a commit operation has been performed. |
| 8 | ANSI_WARNINGS | Controls truncation and NULL in aggregate warnings. |
| 16 | ANSI_PADDING | Controls padding of fixed-length variables. |
| 32 | ANSI_NULLS | Controls NULL handling when using equality operators. |
| 64 | ARITHABORT | Terminates a query when an overflow or divide-by-zero error occurs during query execution. |
| 128 | ARITHIGNORE | Returns NULL when an overflow or divide-by-zero error occurs during a query. |
| 256 | QUOTED_IDENTIFIER | Differentiates between single and double quotation marks when evaluating an expression. |
| 512 | NOCOUNT | Turns off the message returned at the end of each statement that states how many rows were affected. |
| 1024 | ANSI_NULL_DFLT_ON | Alters the session’s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls. |
| 2048 | ANSI_NULL_DFLT_OFF | Alters the session’s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls. |
| 4096 | CONCAT_NULL_YIELDS_NULL | Returns NULL when concatenating a NULL value with a string. |
| 8192 | NUMERIC_ROUNDABORT | Generates an error when a loss of precision occurs in an expression. |
| 16384 | XACT_ABORT | Rolls back a transaction if a Transact-SQL statement raises a run-time error. |
Source : Books online SQL server “Denali”
Use the user options option to specify global defaults for all users. A list of default query processing options is established for the duration of a user’s work session.
The user options option allows you to change the default values of the SET options (if the server’s default settings are not appropriate).
A user can override these defaults by using the SET statement. You can configure user options dynamically for new logins. After you change the setting of user options, new login sessions use the new setting; current login sessions are not affected.
The bit positions in user options are identical to those in @@OPTIONS. Each connection has its own @@OPTIONS function, which represents the configuration environment. When logging in to Microsoft SQL Server, a user receives a default environment that assigns the current user options value to @@OPTIONS. Executing SET statements for user options affects the corresponding value in the session’s @@OPTIONS function.
All connections created after this setting is changed receive the new value.
lets get in to the details how this setting can be changed and effect of SQL server performance with these options
SELECT @@OPTIONS AS OPTIONS
This returns a integer and this represents the bit values for each of the options mentioned above
use the below query to know more about the current sessions setting for current user
DECLARE @OPTIONS INT
SELECT @OPTIONS = @@OPTIONS
PRINT @OPTIONS
IF ( (1 & @OPTIONS) = 1 ) PRINT ‘DISABLE_DEF_CNST_CHK’
IF ( (2 & @OPTIONS) = 2 ) PRINT ‘IMPLICIT_TRANSACTIONS’
IF ( (4 & @OPTIONS) = 4 ) PRINT ‘CURSOR_CLOSE_ON_COMMIT’
IF ( (8 & @OPTIONS) = 8 ) PRINT ‘ANSI_WARNINGS’
IF ( (16 & @OPTIONS) = 16 ) PRINT ‘ANSI_PADDING’
IF ( (32 & @OPTIONS) = 32 ) PRINT ‘ANSI_NULLS’
IF ( (64 & @OPTIONS) = 64 ) PRINT ‘ARITHABORT’
IF ( (128 & @OPTIONS) = 128 ) PRINT ‘ARITHIGNORE’
IF ( (256 & @OPTIONS) = 256 ) PRINT ‘QUOTED_IDENTIFIER’
IF ( (512 & @OPTIONS) = 512 ) PRINT ‘NOCOUNT’
IF ( (1024 & @OPTIONS) = 1024 ) PRINT ‘ANSI_NULL_DFLT_ON’
IF ( (2048 & @OPTIONS) = 2048 ) PRINT ‘ANSI_NULL_DFLT_OFF’
IF ( (4096 & @OPTIONS) = 4096 ) PRINT ‘CONCAT_NULL_YIELDS_NULL’
IF ( (8192 & @OPTIONS) = 8192 ) PRINT ‘NUMERIC_ROUNDABORT’
IF ( (16384 & @OPTIONS) = 16384 ) PRINT ‘XACT_ABORT’
Integer value returned from the @@OPTIONS changes according the options turned on/off
Query execution plans are created the first time a stored procedure gets executed, once created the plans are cached and reused unless the query optimizer decides based on several factors (schema changes, statistics changes, etc.), to create a new plan. However, different query plans can be created for different connections if they have different SEToptions (SET ARITHABORT ON/OFF, SET ANSI_NULLS ON/OFF, etc.), so it is possible to have several execution plans for the same stored procedure at the same time.
If the user’s application has different SET options than those of the tools you are using to execute the queries (for example, SQL Server Management Studio), you will very likely get a different execution plan then the one the user is getting, and the stored procedure’s performance will be different.
In order to reproduce the problem in the same database where the performance problem is happening, you have to get the SET options that the application’s connection is using and then set your connection the same way. The easiest way to get this information is by using SQL Profiler and tracing the ExistingConnection event class (it is included in theStandard Template).
Once you get the application’s SET options, you can setup your connection the same way and you will be able to get the same cached plans that the users are getting from the application. The following steps show you how to do this:
it is one of the configuration functions returns first day of the week (tinyint) . The U.S. English default is 7, Sunday
Syntax:
SELECT @@DATEFIRST
Is an integer that indicates the first day of the week. It can be one of the following values.
| Value | First day of the week is |
|---|---|
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 (default, U.S. English) | Sunday |
Sqljunkieshare 