|
|
One important difference between table variables and temporary tables is that ROLLBACK command do not affect table variables.
We will create a temporary table and insert one value into the table. After that we will begin a transaction which inserts another value into the table, and then ROLLBACK the transaction:
CREATE TABLE #T_Tab
(Identity_Column INT IDENTITY(1,1),
Test_Data int)
INSERT INTO #T_Tab (Test_Data) VALUES (1);
BEGIN TRANSACTION
INSERT INTO #T_Tab (Test_Data) VALUES (2)
ROLLBACK TRANSACTION
SELECT * FROM #T_Tab
DROP TABLE #T_Tab
As expected, there will be only one record in the temporary table, the second insert is rolled back:
Then, we will create a table variable and insert one value into it. After that we will begin a transaction which inserts another value into the table variable, and then ROLLBACK the transaction:
DECLARE @T_Var TABLE
(Identity_Column INT IDENTITY(1,1),
Test_Data int)
INSERT INTO @T_Var (Test_Data) VALUES (1);
BEGIN TRANSACTION
INSERT INTO @T_Var (Test_Data) VALUES (2)
ROLLBACK TRANSACTION
SELECT * FROM @T_Var
As we can see, there are two records in the table variable, because transaction rollbacks do not affect table variables:
To navigate easier through many lines of code in SQL Server Management Studio (SSMS) Query Editor Window, it can be useful to bookmark some lines to which you would like to return to.
To set or remove a bookmark at the current line, press ‘CTRL+K’ followed by another ‘CTRL+K’. A small blue square will appear left of the bookmarked line:
To move to the next bookmarked line of code from the current cursor location press ‘CTRL+K’ followed by CTRL+N, to move to the previous bookmarked line of code press ‘CTRL+K’ followed by ‘CTRL+P’.
To clear all bookmarks press ‘CTRL+K’ followed by ‘CTRL+L’.
By pressing ‘CTRL+K’ followed by ‘CTRL+W’, you can open a Bookmarks window:
You can move the cursor to the bookmarked line by double clicking on the bookmark in the Bookmark Window.
There is an option to rename bookmarks in the Bookmark Window.
If you didn’t enable the option to Display Line Numbers in SQL Server Management Studio Query Editor Window, and you want to quickly go to a specific line number:
Click ‘Edit’ -> ‘Go To’:
or press ‘CTRL + G’ to open ‘Go To Line’ dialog box:
Enter the number of the line that you want to go to, and click ‘OK’.
If you want to quickly go to the line in the code where you have a syntax error, double click the red error message and SQL Server Management Studio (SSMS) will take you directly to the line of the code stated in the error message:
By default, line numbers are not shown in SQL Server Management Studio (SSMS) Query Editor Window next to the T-SQL code:
To enable or disable the display of line numbers next to the T-SQL code in Query Editor Window, go to:
Tools -> Options:
Expand ‘Text Editor’ -> expand ‘Transact-SQL’ -> Click ‘General’ -> Select the ‘Line numbers’ check box:
Line numbers will be displayed next to the T-SQL code in all new Query Editor Windows in SQL Server Management Studio:
To list all column collations for character-based columns (char, varchar, text, nchar, nvarchar, and ntext data) for all user-defined tables in a database (in this example we will use AdventureWorks database) that have different collation than the Database collation, run this query:
USE AdventureWorks
GO
DECLARE @DB_Name VARCHAR(50)
SET @DB_Name=(SELECT DB_NAME())
DECLARE @DB_Collation VARCHAR(50)
SET @DB_Collation=CAST(DATABASEPROPERTYEX(@DB_Name, 'Collation') as varchar(50))
SELECT s.name as 'Schema_Name', t.name as Table_Name,
c.name AS Column_Name,
c.collation_name AS Collation,
@DB_Collation AS Database_Collation
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id = s.schema_id
INNER JOIN sys.columns c
ON c.object_id = t.object_id
WHERE collation_name is not null
AND collation_name <> @DB_Collation
ORDER BY Column_Name
To see how to List all Column Collations for all Tables in a SQL Server Database, read related blog post:
List all Column Collations for all Tables in a SQL Server Database
To list all column collations for character-based columns (char, varchar, text, nchar, nvarchar, and ntext data) for all user-defined tables in a database (in this example we will use AdventureWorks database), run this query:
USE AdventureWorks
GO
SELECT s.name as 'Schema_Name', t.name as Table_Name,
c.name AS Column_Name,
c.collation_name AS Collation
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id = s.schema_id
INNER JOIN sys.columns c
ON c.object_id = t.object_id
WHERE collation_name is not null
ORDER BY Column_Name
To see how to List all Table Columns that have Collations different than the SQL Server Database, read related blog post:
List all Table Columns that have Collations different than the SQL Server Database
To list all collations supported by the version of SQL Server that you are running, and the Non-Unicode code page of the collation, run the following query:
select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()
Unicode-only collations do not have associated code pages, they are specified for Unicode-only data (nchar, nvarchar, and nvarchar(max)). They cannot be set at the database or server level.
If you try to create a database with a Unicode-only collation (for example ‘Lao_100_CI_AI’), you will get the following error:
Collation ‘Lao_100_CI_AI’ is supported on Unicode data types only and cannot be set at the database or server level. (Microsoft SQL Server, Error: 453).
You can see the Code Page and Description for a specific collation:
select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()
where name = 'Latin1_General_CS_AS'
For example, you can list all collation that have a name that starts with ‘SQL_L’ and have a word ‘insensitive’ in the description:
select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()
where Name like 'SQL_L%' and Description like '%insensitive%';
To list all databases and their collations, together with the SQL Server Instance collation, run this query:
SELECT
name as Database_Name
, DATABASEPROPERTYEX(NAME,'COLLATION') AS Database_collation
, SERVERPROPERTY('COLLATION') AS Server_Collation
FROM SYS.DATABASES
To list only databases that have DIFFERENT collation that the SQL Server Instance collation, run this query:
SELECT
name as Database_Name
, DATABASEPROPERTYEX(NAME,'COLLATION') AS Database_collation
, SERVERPROPERTY('COLLATION') AS Server_Collation
FROM SYS.DATABASES
WHERE (DATABASEPROPERTYEX(NAME,'COLLATION')) <> SERVERPROPERTY('COLLATION')
|
|
Follow Us!