SQL Server 2012 Support (Revision #36)

SQL Server 2012 Support

Aqua Data Studio 11.0 now supports SQL Server 2012, a high performance analytic database, designed by SQL Server 2012 from the ground up for speed. SQL Server 2012 has an extensible framework that imbeds 502 analytic functions and supports on-demand integration of a variety of data and analytic results, right at the point of execution. With Aqua Data Studio version 11.0, you can register a SQL Server 2012 Server and use the different features available such as Query Analyzer Window, Visual Editors, Table Data Editor, Import/ Export Tools, ER Modeler, Schema Script Generator and FluidShell. Visual Editing tools to manage the SQL Server 2012 Users and Groups as well as Create Databases, Schemas, Tables, Views and Functions are also available.

Native support has been added for SQL Server 2012

Vendor: Microsoft
Website: http://www.microsoft.com/sqlserver/en/us/future-editions.aspx
Database Product: SQL Server 2012
Description: High-performance analytic database with massively parallel, columnar, compressed, compiled approach for speed.


CREATE Sequence	Snapshots Node	Script Generator Linked & Remote Servers	CREATE User	CREATE Server Role

Server Triggers	CREATE Database

Create Database GUI in Aqua Data Studio 11.0 has been modified to support the following create database properties

CONTAINMENT
DEFAULT_FULLTEXT_LANGUAGE
DEFAULT_LANGUAGE
TWO_DIGIT_YEAR_CUTOFF
NESTED_TRIGGERS
TRANSFORM_NOISE_WORDS
FILESTREAM NON_TRANSACTED_ACCESS
FILESTREAM DIRECTORY_NAME
CROSS DATBASE OWNERSHIP CHAINING
TRUSTWORTHY

Create Sequence is now supported in Aqua Data Studio 11.0

Snapshot Database node is displayed in the Server Tree in the latest release

Create User is now supported

Create Server Role has new options

Server Triggers are now supported

Script Generator is now enhanced with scripting of Linked and Remote Server objects

Features supported in SQL Server 2012

Query Analyzer - Connect to SQL Server 2012 and execute SQL Queries. Auto Completion, Syntax Coloring, Grid, Pivot Grid Results and Charting toolkit.
Visual Editing GUI - Create Database, Create Schema, Create Table, Create View, Create Function.
Table Data Editor - Excel like editor of data in tables and query results.
Import Tool - Import delimited data files, SQL scripts or INSERT statements to database tables.
Export Tool - Export table data as delimited data, Excel Worksheets or SQL Script of INSERT statements.
Schema Script Generator - Script a complete database to file, including CREATE, DROP DDL and INSERT DML.
Server Script Generator - Allows a user to generate SQL scripts for server objects such as users and roles.
ER Modeler - Fully functional entity relationship modeler.
FluidShell - Connect to SQL Server 2012 and execute SQL Queries as well as Shell commands.
Schema Compare Tool - Allows comparison of Schema Objects and Databases, including functions, packages and more.
Results Compare Tool - Allows comparison of SQL Query results.
Object Search Tool - Assists in finding Schema and Database Objects within a Database Server.
Visual Query Builder - Visually design queries.
Schema Browser, DDL Scripting, Visual Editing - Tables, Views, Indexes, Users and Roles.
SQL History/Archive - Provides a history of all SQL statements and scripts that have been executed within the Query Analyzer, Query Builder and Table Data Editor.

Alter database GUI has been modified to support all of the new database properties.

Filestream list has been added to filegroup types tab.

----

create database GUI has been modified to support the following create database properties

CONTAINMENT: Specifies the containment status of the database. NONE = non-contained database. PARTIAL = partially contained database.

NON_TRANSACTED_ACCESS: Specifies the level of non-transactional FILESTREAM access to the database.

DIRECTORY_NAME: A windows-compatible directory name. This name should be unique among all the Database_Directory names in the SQL Server instance. Uniqueness comparison is case-insensitive, regardless of SQL Server collation settings. This option should be set before creating a FileTable in this database.

DEFAULT_FULLTEXT_LANGUAGE: Use the default full-text language option to specify a default language value for full-text indexed columns. Linguistic analysis is performed on all data that is full-text indexed and is dependent on the language of the data.

DEFAULT_LANGUAGE: se the default language option to specify the default language for all newly created logins.

NESTED_TRIGGERS: Use the nested triggers option to control whether an AFTER trigger can cascade; that is, perform an action that initiates another trigger, which initiates another trigger, and so on. When nested triggers is set to 0, AFTER triggers cannot cascade. When nested triggers is set to 1 (the default), AFTER triggers can

cascade to as many as 32 levels. INSTEAD OF triggers can be nested regardless of the setting of this option.

TRANSFORM_NOISE_WORDS: Use the transform noise words server configuration option to suppress an error message if noise words, that is stopwords, cause a Boolean operation on a full-text query to return zero rows. This option is useful for full-text queries that use the CONTAINS predicate in which Boolean operations or NEAR

operations include noise words

TWO_DIGIT_YEAR_CUTOFF: Use the two digit year cutoff option to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.

TRUSTWORTHY: When ON is specified, database modules (for example, views, user-defined functions, or stored procedures) that use an impersonation context can access resources outside the database. When OFF, database modules in an impersonation context cannot access resources outside the database. The default is OFF.

FILESTREAMS:

Extracting filestream groups.

PROPERTIES:

CONTAINMENT: Specifies the containment status of the database. NONE = non-contained database. PARTIAL = partially contained database.

NON_TRANSACTED_ACCESS: Specifies the level of non-transactional FILESTREAM access to the database.

DEFAULT_LANGUAGE: se the default language option to specify the default language for all newly created logins.

cascade to as many as 32 levels. INSTEAD OF triggers can be nested regardless of the setting of this option.

operations include noise words

TWO_DIGIT_YEAR_CUTOFF: Use the two digit year cutoff option to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.

AUTO_CLOSE:

ON: The database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

OFF: The database remains open after the last user exits.

READ_ONLY: Users can read data from the database but not modify it.

READ_WRITE: The database is available for read and write operations.

User Access:

SINGLE_USER: Specifies that only one user at a time can access the database.

RESTRICTED_USER: allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.

MULTI_USER: All users that have the appropriate permissions to connect to the database are allowed.

AUTO_SHRINK:

ON: The database files are candidates for periodic shrinking. Both data file and log files can be automatically shrunk.

OFF: The database files are not automatically shrunk during periodic checks for unused space.

ANSI_NULL_DEFAULT:

Determines the default value, NULL or NOT NULL, of a column, alias data type, or CLR user-defined type for which the nullability is not explicitly defined in CREATE TABLE or ALTER TABLE statements.

ANSI_NULLS:

ON: All comparisons to a null value evaluate to UNKNOWN.

OFF: Comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

ANSI_PADDING

ON: Strings are padded to the same length before conversion or inserting to a varchar or nvarchar data type.

OFF: Trailing blanks for varchar or nvarchar and zeros for varbinary are trimmed.

ANSI_WARNINGS

ON: Errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions.

oFF: No warnings are raised and null values are returned when conditions such as divide-by-zero occur.

RECURSIVE_TRIGGERS:

ON: Recursive firing of AFTER triggers is allowed.

OFF: Only direct recursive firing of AFTER triggers is not allowed. To also disable indirect recursion of AFTER triggers, set the nested triggers server option to 0 by using sp_configure.

QUOTED_IDENTIFIER

ON: Double quotation marks can be used to enclose delimited identifiers.

OFF: Identifiers cannot be in quotation marks and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

AUTO_UPDATE_STATISTICS_ASYNC

ON: Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are asynchronous.

OFF: Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are synchronous.

CURSOR_CLOSE_ON_COMMIT

ON: Any cursors open when a transaction is committed or rolled back are closed.

OFF: Cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

CURSOR_DEFAULT:

LOCAL: When LOCAL is specified and a cursor is not defined as GLOBAL when created, the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created.

GLOBAL: When GLOBAL is specified, and a cursor is not defined as LOCAL when created, the scope of the cursor is global to the connection.

DATE_CORRELATION_OPTIMIZATION

ON: SQL Server maintains correlation statistics between any two tables in the database that are linked by a FOREIGN KEY constraint and have datetime columns.

OFF: Correlation statistics are not maintained.

ENCRYPTION: Sets the database to be encrypted (ON) or not encrypted (OFF).

PARAMETERIZATION

SIMPLE: Queries are parameterized based on the default behavior of the database. For more information, see Simple Parameterization.

FORCED: SQL Server parameterizes all queries in the database. For more information, see Forced Parameterization.

TARGET_RECOVERY_TIME: Specifies the maximum bound on the time to recover the specified database in the event of a crash.

ENABLE_BROKER: Specifies that Service Broker is enabled for the specified database. Message delivery is started, and the is_broker_enabled flag is set to true in the sys.databases catalog view

DISABLE_BROKER: Specifies that Service Broker is disabled for the specified database. Message delivery is stopped, and the is_broker_enabled flag is set to false in the sys.databases catalog view

HONOR_BROKER_PRIORITY

ON: Send operations take into consideration the priority levels that are assigned to conversations. Messages from conversations that have high priority levels are sent before messages from conversations that are assigned low priority levels.

OFF: Send operations run as if all conversations have the default priority level.

ALLOW_SNAPSHOT_ISOLATION

ON: Enables Snapshot option at the database level. When it is enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation.

Once this option is enabled, transactions can specify the SNAPSHOT transaction isolation level. When a transaction runs at the SNAPSHOT isolation level,

all statements see a snapshot of data as it exists at the start of the transaction. If a transaction running at the SNAPSHOT isolation level accesses data in multiple databases,

either ALLOW_SNAPSHOT_ISOLATION must be set to ON in all the databases, or each statement in the transaction must use locking hints on any reference in a FROM clause

to a table in a database where ALLOW_SNAPSHOT_ISOLATION is OFF.

OFF: Turns off the Snapshot option at the database level. Transactions cannot specify the SNAPSHOT transaction isolation level.

READ_COMMITTED_SNAPSHOT

ON: Enables Read-Committed Snapshot option at the database level. When it is enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation.

Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. When a transaction runs at the read committed isolation level,

all statements see a snapshot of data as it exists at the start of the statement.

OFF: Turns off Read-Committed Snapshot option at the database level. Transactions specifying the READ COMMITTED isolation level use locking.

ARITHABORT

ON: A query is ended when an overflow or divide-by-zero error occurs during query execution.

OFF: A warning message is displayed when one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred.

CONCAT_NULL_YIELDS_NULL

ON: The result of a concatenation operation is NULL when either operand is NULL. For example, concatenating the character string "This is" and NULL causes the value NULL, instead of the value "This is".

OFF: The null value is treated as an empty character string.

NUMERIC_ROUNDABORT

ON: An error is generated when loss of precision occurs in an expression.

OFF: Losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

CHANGE_TRACKING: Controls change tracking options. You can enable change tracking, set options, change options, and disable change tracking.

ON: Enables change tracking for the database. When you enable change tracking, you can also set the AUTO CLEANUP and CHANGE RETENTION options.

OFF: Disables change tracking for the database. You must disable change tracking on all tables before you can disable change tracking off the database.

AUTO_CLEANUP

ON: Change tracking information is automatically removed after the specified retention period.

OFF: Change tracking data is not removed from the database.

CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }

Specifies the minimum period for keeping change tracking information in the database. Data is removed only when the AUTO_CLEANUP value is ON.

1. SQL SERVER 2011 aka "denali" support -- CREATE SEQUENCE https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/6106

2. SQL SERVER 2012 -- Tree node -- Databases -- display snapshot databases

SQL SERVER 2011 aka "denali" support -- Server SCRIPT GENERATOR --> linked servers remote servers and server roles added to server script generator.

4. SQL SERVER 2011 aka "denali" support -- CREATE USER / ROLE https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/6102

5. SQL SERVER 2011 aka "denali" support -- CREATE SERVER ROLE https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/6111

6. Add support for Server Triggers in SQL Server https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/6171

7. create Database

CREATE DATABASE database_name

ON <filespec> [ ,...n ]

FOR { { ATTACH [ WITH <attach_database_option> [ , ...n ] ] }

| ATTACH_REBUILD_LOG }

<attach_database_option> ::=

RESTRICTED_USER

| FILESTREAM ( DIRECTORY_NAME = { 'directory_name' | NULL } )

8. SQL SERVER 2011 aka "denali" support -- CREATE LOGIN

Search Tips

Aqua Data Studio / nhilam

SQL Server 2012 Support (Revision #36)

Search Tips

Aqua Data Studio / nhilam

Title

SQL Server 2012 Support (Revision #36)