Showing posts with label SQL SERVER. Show all posts
Showing posts with label SQL SERVER. Show all posts

What are Magic Tables in SQL Server?



Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the inserted and the deleted tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).

While using triggers these Inserted & Deleted tables (called as magic tables) will be created automatically.

The inserted table contains a copy of all records that are inserted in the trigger table.

The deleted table contains all records that have been deleted from deleted from the trigger table.

Whenever any updation takes place,the trigger uses both the inserted and deleted tables.

When we insert any record then that record will be added into this Inserted table initially, similarly while updating a record a new entry will be inserted into Inserted table & old value will be inserted into Deleted table. In the case of deletion of a record then it will insert that record into the Deleted table.

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.
Labels:

Pre-defined Data Types in SQL Server Database


Data Types in SQL Server Database

In a Database, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, data and time data, binary strings, and so on.

Integer Types: To hold the Integer values it provides with tinyint, smallint, int and bigint data types with sizes 1, 2, 4 and 8 bytes respectively.

Boolean Type: To hold the Boolean values it provides with bit data type that can take a value of 1, 0, or NULL.

Note: The string values TRUE and FALSE can be converted to bitvalues. TRUE is converted to 1 and FALSE is converted to 0.

Decimal Types: To hold the decimal values it provides with the following types:

decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

p (precision)

The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)

The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 throughp. Scale can be specified only if precision is specified. The default scale is 0.

Storage sizes of Decimal and Numeric types vary, based on the precision.

PrecisionStorage bytes
1-95
10-199
20-2813
29-3817

Note: numeric is functionally equivalent to decimal.

float [ ( n ) ] and real

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

n valuePrecisionStorage size
1-247 digits4 bytes
25-5315 digits8 bytes

Monetary or Currency Types: To hold the Currency values it provides with the following types which takes a scale of 4 by default:

Data typeRangeSize
money-922,337,203,685,477.5808 to 922,337,203,685,477.58078 bytes
smallmoney- 214,748.3648 to 214,748.36474 bytes

Date and Time Values: To hold the Date and Time values of a day it provides with the following types:

Data typeRangeAccuracy
datetimeJanuary 1, 1753, through December 31, 99993.33 milliseconds
smalldatetimeJanuary 1, 1900, through June 6, 20791 minute

Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. The Database Engine storessmalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight.

String Values: To hold the string values it provides with the following types:

char [ ( n ) ]

Fixed-length, non-Unicode character data with a length of nbytes. n must be a value from 1 through 8,000. The storage size is n bytes.

varchar [ ( n | max ) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes.

text

It was equal to varchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varchar(max) instead.

Unicode Data types for storing Multilingual Characters are nchar, nvarchar and ntext where n stands for national.

nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times nbytes.

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes.

ntext

It was equal to nvarchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use nvarchar(max) instead.

Binary Values: To hold the binary values likes images, audio clips and video clips we use the following types.

binary [ ( n ) ]

Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

varbinary [ ( n | max) ]

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes.

Image

It was equal to varbinary(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varbinary(max) instead.

  • Use char, nchar, binary when the sizes of the column data entries are consistent.
  • Use varchar, nvarchar, varbinary when the sizes of the column data entries vary considerably.
  • Use varchar(max), nvarchar(max), varbinary(max)when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
      Other Types:
        Apart from the above it provides some additional types like -


timestamp:
Is a data type that exposes automatically generated, unique binary numbers within a database. The storage size is 8 bytes. You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.

Uniqueidentifier:
Is a 16-byte GUID which is initialized by using the newid() function or converting a string constant in the form of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx which is used to guarantee that rows are uniquely identified across multiple copies of the table.

Xml:
Is the data type that stores XML data. You can store xmlinstances in a column, or a variable of xml type. The stored representation of xml data type instances cannot exceed 2 gigabytes (GB) in size.

Labels:

User-Defined Data Types (UDDT)



What is a User-Defined Data Type?

A user-defined data type provides a convenient use of underlying native datatypes for columns known to have the same domain of possible values.

Example:
EXEC sp_addtype phone_number, 'VARCHAR(20)','NOT NULL'
CREATE TABLE Customer(cust_id smallint, cust_phone phone_number)

How to create User-Defined Data Types?

User-defined data types are based on the system data types in Microsoft SQL Server. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and NULLability. For example, a user-defined data type called postal_code could be created based on the char data type. User-defined data types are not supported in TABLE variables.

When a user-defined data type is created, you must supply these parameters:
  1. Name
  2. System data type upon which the new data type is based
  3. NULLability (whether the data type allows NULL values)

    When NULLability is not explicitly defined, it will be assigned based on the ANSI NULL default setting for the database or connection.
Note: If a user-defined data type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user-defined database, the data type exists only in that user-defined database.

Examples:

The SQL Server user defined data types can be created both with SQL Server Management Studio and T-SQL commands. Let's walk through samples of each option to serve as an example of SQL Server user defined data types can be used with defaults and rules.
1.Creating SQL Server User Defined Data Types in SQL Server Management Studio (SSMS)

> Open SQL Server Management Studio.
> Navigate to the Databases | AdventureWorks | Programmability | Types folder.
> Right click on the Types folder and select New | User-Defined Data Type

2.Syntax for creating SQL Server user defined data type

sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @NULLtype = ] 'NULL_type' ] [ , [ @owner = ] 'owner_name' ]

Here is a basic explanation of the four parameters from the system stored procedure:

Parameter Explanation @typename Name of new user defined data type that is being created @phystype Base system data type of SQL Server @NULLtype To specify that NULL values are allowed for this data type or not @owner Owner of this being created user defined data type

Below is the example default and rule code used in SSMS above: Example

Example Default and Rule Code
1.CREATE a default value for phone number to be used in example

USE AdventureWorks
GO

CREATE DEFAULT Default_PhNo
AS 'UnknownNumber'
GO

2.CREATE a rule for phone number to be used in example
Number will be in format +92-3335409953 or UnknownNumber by default

USE AdventureWorks
GO

CREATE RULE rule_PhNo AS (@phone='UnknownNumber')
OR (LEN(@phone)=14
AND SUBSTRING(@phone,1,1)= '+'
AND SUBSTRING(@phone,4,1)= '-')
GO

In the final code snippet, we will bind the rules and defaults to the user defined data types:

Commands to bind the defaults and rules to user defined data types

To bind a default 'Default_PhNo' to user defined data type 'PhoneNumb'

EXEC sp_bindefault 'Default_PhNo', 'PhoneNumb'

To bind a rule 'rule_PhNo' to user defined data type 'PhoneNumb'

EXEC sp_bindrule 'rule_PhNo', 'PhoneNumb'

Labels:

Logical Operators (Transact-SQL)




Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.
OperatorMeaning
TRUE if all of a set of comparisons are TRUE.
TRUE if both Boolean expressions are TRUE.
TRUE if any one of a set of comparisons are TRUE.
TRUE if the operand is within a range.
TRUE if a subquery contains any rows.
TRUE if the operand is equal to one of a list of expressions.
TRUE if the operand matches a pattern.
Reverses the value of any other Boolean operator.
TRUE if either Boolean expression is TRUE.
TRUE if some of a set of comparisons are TRUE.

Table

How Logical Operator works:

ALL

   
Compares a scalar value with a single-column set of values.
   
The following query returns all if all the StateCodes greater than 200. If atleast one statecode is less then 200 then it doesn,t return any records. Here States MP and UP have statecodes greater than 200, so condition fails and result is nothing.
   
SELECT * FROM  tbl_Population
WHERE 200 > ALL
(
      SELECT StateCode FROM tbl_Population
)
   
OUTPUT
Noyhing
   
   

AND

   
Performs a logical AND operation. The expression evaluates to TRUE if all conditions are TRUE.
   
SELECT * FROM tbl_Population
WHERE (StateCode > 100 AND StateCode < 200)
   
OUTPUT

   

ANY and SOME

   
Compares a scalar value with a single-column set of values.
   
Both SOME or ANY returns TRUE when the comparison specified is TRUE for ANY pair, otherwise, returns FALSE.
   
In the given table there is some states which statecodes are less than 200, so it will returns all the records.
   

ANY

   
SELECT * FROM  tbl_Population
WHERE 200 > ANY
(
      SELECT StateCode FROM tbl_Population
)
   

SOME

   
SELECT * FROM  tbl_Population
WHERE 200 > SOME
(
      SELECT StateCode FROM tbl_Population
)
   
OUTPUT
   
   

BETWEEN

   
Specifies a range to test.
   
SELECT * FROM tbl_Population
WHERE StateCode BETWEEN 100 AND 200
   
OUTPUT

EXISTS

   
Specifies a subquery to test for the existence of rows.
   
SELECT * FROM tbl_Population WHERE EXISTS
(
      SELECT * FROM tbl_Population
WHERE StateCode=409
)
   
It returns data when a specified record exist in the table which is given in sub query of where condition
   
OUTPUT
   
   


IN

   
Determines whether a given value matches any value in a subquery or a list.
   
SELECT * FROM tbl_Population
WHERE StateCode IN (1,101,102,300)
   
OUTPUT
LIKE
   
Determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any one of the arguments are not of character string data type, the SQL Server 2005 Database Engine converts them to character string data type, if it is possible.
   
SELECT * FROM tbl_Population
WHERE StateName LIKE 'K%'
   
Returns all the records which has K as first letter in StateName
   
OUTPUT

NOT

   
To find rows that do not match a value, use the NOT operator.
   
SELECT * FROM tbl_Population
WHERE StateCode NOT IN (1,100,200,300)
   
OUTPUT
   
   

OR

Performs a logical OR operation. The expression evaluates to TRUE if atleast one condition is TRUE.
   
SELECT * FROM tbl_Population
WHERE StateName LIKE 'K%' OR StateCode < 105
   
OUTPUT
Labels:

IDENTITY PROPERTY




The IDENTITY property makes generating unique values easy. IDENTITY isn't a datatype. It's a column property.

Ex: CREATE TABLE Customer(cust_id smallint IDENTITY NOT NULL, cust_name VARCHAR(50) NOT NULL)

The system function @@IDENTITY contains the last identity value used by the connection.

SELECT @@IDENTITY:- If a trigger was fired for the INSERT, the value of @@IDENTITY might have changed.

SELECT SCOPE_IDENTITY:- If an INSERT trigger also inserted a row that contained an identity column, it would be in a different scope.

SELECT IDENT_CURRENT('Customer'): - To know the last IDENTITY value inserted in a specific TABLE from any application or user.
Labels:

@@ Keywords in sql server



SQL Server @@ Keywords (Transact-SQL)
@@CONNECTIONS
Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.
@@MAX_CONNECTIONS is the maximum number of connections allowed simultaneously to the server.
@@CONNECTIONS is incremented with each login attempt, therefore @@CONNECTIONS can be greater than @@MAX_CONNECTIONS.

@@CPU_BUSY
Returns the time that SQL Server has spent working since it was last started. Result is in CPU time increments, or "ticks," and is cumulative for all CPUs, so it may exceed the actual elapsed time.

@@CURSOR_ROWS
Returns the number of qualifying rows currently in the last cursor opened on the connection.

@@DATEFIRST
Returns the current value, for a session, of SET DATEFIRST.
SET DATEFIRST specifies the first day of the week. The U.S. English default is 7, Sunday.

@@DBTS
@@DBTS returns the last-used timestamp value of the current database. A new timestamp value is generated when a row with a timestamp column is inserted or updated.

@@ERROR
Returns the error number for the last Transact-SQL statement executed.

@@FETCH_STATUS
Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

@@IDENTITY
Is a system function that returns the last-inserted identity value.

@@IDLE
Returns the time that SQL Server has been idle since it was last started. The result is in CPU time increments, or "ticks," and is cumulative for all CPUs, so it may exceed the actual elapsed time.

@@IO_BUSY
Returns the time that SQL Server has spent performing input and output operations since SQL Server was last started. The result is in CPU time increments ("ticks"), and is cumulative for all CPUs, so it may exceed the actual elapsed time.

@@LANGID
Returns the local language identifier (ID) of the language that is currently being used.

@@LANGUAGE
Returns the name of the language currently being used.

@@LOCK_TIMEOUT
SET LOCK_TIMEOUT allows an application to set the maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is automatically canceled, and an error message is returned to the application.
@@LOCK_TIMEOUT returns a value of -1 if SET LOCK_TIMEOUT has not yet been run in the current session.

@@MAX_CONNECTIONS
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.

@@MAX_PRECISION
Returns the precision level used by decimal and numeric data types as currently set in the server.
By default, the maximum precision returns 38.

@@NESTLEVEL
Returns the nesting level of the current stored procedure execution (initially 0) on the local server.
When @@NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level. When @@NESTLEVEL is executed dynamically by using sp_executesql the value returned is 2 + the current nesting level.

@@OPTIONS
Returns information about the current SET options.

@@PACK_RECEIVED
Returns the number of input packets read from the network by SQL Server since it was last started.

@@PACK_SENT
Returns the number of output packets written to the network by SQL Server since it was last started.

@@PACKET_ERRORS
Returns the number of network packet errors that have occurred on SQL Server connections since SQL Server was last started.

@@PROCID
Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider.

@@REMSERVER
@@REMSERVER enables a stored procedure to check the name of the database server from which the procedure is run.

@@ROWCOUNT
Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

@@SERVERNAME
Returns the name of the local server that is running SQL Server.

@@SERVICENAME
Returns the name of the registry key under which SQL Server is running. @@SERVICENAME returns 'MSSQLSERVER' if the current instance is the default instance; this function returns the instance name if the current instance is a named instance.

@@SPID
Returns the session ID of the current user process.
@@SPID can be used to identify the current user process in the output of sp_who.

@@TEXTSIZE
Returns the current value of the TEXTSIZE option.

@@TIMETICKS
Returns the number of microseconds per tick.
The amount of time per tick is computer-dependent. Each tick on the operating system is 31.25 milliseconds, or one thirty-second of a second.

@@TOTAL_ERRORS
Returns the number of disk write errors encountered by SQL Server since SQL Server last started.

@@TOTAL_READ
Returns the number of disk reads, not cache reads, by SQL Server since SQL Server was last started.

@@TOTAL_WRITE
Returns the number of disk writes by SQL Server since SQL Server was last started.

@@TRANCOUNT
Returns the number of active transactions for the current connection.
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.
Labels:
Subscribe to: Posts (Atom)