codekingpro/portable-devtools
114k
1# dialects/mssql/base.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
8
9"""
10.. dialect:: mssql
11 :name: Microsoft SQL Server
12 :normal_support: 2012+
13 :best_effort: 2005+
14
15.. _mssql_external_dialects:
16
17External Dialects
18-----------------
19
20In addition to the above DBAPI layers with native SQLAlchemy support, there
21are third-party dialects for other DBAPI layers that are compatible
22with SQL Server. See the "External Dialects" list on the
23:ref:`dialect_toplevel` page.
24
25.. _mssql_identity:
26
27Auto Increment Behavior / IDENTITY Columns
28------------------------------------------
29
30SQL Server provides so-called "auto incrementing" behavior using the
31``IDENTITY`` construct, which can be placed on any single integer column in a
32table. SQLAlchemy considers ``IDENTITY`` within its default "autoincrement"
33behavior for an integer primary key column, described at
34:paramref:`_schema.Column.autoincrement`. This means that by default,
35the first integer primary key column in a :class:`_schema.Table` will be
36considered to be the identity column - unless it is associated with a
37:class:`.Sequence` - and will generate DDL as such::
38
39 from sqlalchemy import Table, MetaData, Column, Integer
40
41 m = MetaData()
42 t = Table(
43 "t",
44 m,
45 Column("id", Integer, primary_key=True),
46 Column("x", Integer),
47 )
48 m.create_all(engine)
49
50The above example will generate DDL as:
51
52.. sourcecode:: sql
53
54 CREATE TABLE t (
55 id INTEGER NOT NULL IDENTITY,
56 x INTEGER NULL,
57 PRIMARY KEY (id)
58 )
59
60For the case where this default generation of ``IDENTITY`` is not desired,
61specify ``False`` for the :paramref:`_schema.Column.autoincrement` flag,
62on the first integer primary key column::
63
64 m = MetaData()
65 t = Table(
66 "t",
67 m,
68 Column("id", Integer, primary_key=True, autoincrement=False),
69 Column("x", Integer),
70 )
71 m.create_all(engine)
72
73To add the ``IDENTITY`` keyword to a non-primary key column, specify
74``True`` for the :paramref:`_schema.Column.autoincrement` flag on the desired
75:class:`_schema.Column` object, and ensure that
76:paramref:`_schema.Column.autoincrement`
77is set to ``False`` on any integer primary key column::
78
79 m = MetaData()
80 t = Table(
81 "t",
82 m,
83 Column("id", Integer, primary_key=True, autoincrement=False),
84 Column("x", Integer, autoincrement=True),
85 )
86 m.create_all(engine)
87
88.. versionchanged:: 1.4 Added :class:`_schema.Identity` construct
89 in a :class:`_schema.Column` to specify the start and increment
90 parameters of an IDENTITY. These replace
91 the use of the :class:`.Sequence` object in order to specify these values.
92
93.. deprecated:: 1.4
94
95 The ``mssql_identity_start`` and ``mssql_identity_increment`` parameters
96 to :class:`_schema.Column` are deprecated and should we replaced by
97 an :class:`_schema.Identity` object. Specifying both ways of configuring
98 an IDENTITY will result in a compile error.
99 These options are also no longer returned as part of the
100 ``dialect_options`` key in :meth:`_reflection.Inspector.get_columns`.
101 Use the information in the ``identity`` key instead.
102
103.. deprecated:: 1.3
104
105 The use of :class:`.Sequence` to specify IDENTITY characteristics is
106 deprecated and will be removed in a future release. Please use
107 the :class:`_schema.Identity` object parameters
108 :paramref:`_schema.Identity.start` and
109 :paramref:`_schema.Identity.increment`.
110
111.. versionchanged:: 1.4 Removed the ability to use a :class:`.Sequence`
112 object to modify IDENTITY characteristics. :class:`.Sequence` objects
113 now only manipulate true T-SQL SEQUENCE types.
114
115.. note::
116
117 There can only be one IDENTITY column on the table. When using
118 ``autoincrement=True`` to enable the IDENTITY keyword, SQLAlchemy does not
119 guard against multiple columns specifying the option simultaneously. The
120 SQL Server database will instead reject the ``CREATE TABLE`` statement.
121
122.. note::
123
124 An INSERT statement which attempts to provide a value for a column that is
125 marked with IDENTITY will be rejected by SQL Server. In order for the
126 value to be accepted, a session-level option "SET IDENTITY_INSERT" must be
127 enabled. The SQLAlchemy SQL Server dialect will perform this operation
128 automatically when using a core :class:`_expression.Insert`
129 construct; if the
130 execution specifies a value for the IDENTITY column, the "IDENTITY_INSERT"
131 option will be enabled for the span of that statement's invocation.However,
132 this scenario is not high performing and should not be relied upon for
133 normal use. If a table doesn't actually require IDENTITY behavior in its
134 integer primary key column, the keyword should be disabled when creating
135 the table by ensuring that ``autoincrement=False`` is set.
136
137Controlling "Start" and "Increment"
138^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
139
140Specific control over the "start" and "increment" values for
141the ``IDENTITY`` generator are provided using the
142:paramref:`_schema.Identity.start` and :paramref:`_schema.Identity.increment`
143parameters passed to the :class:`_schema.Identity` object::
144
145 from sqlalchemy import Table, Integer, Column, Identity
146
147 test = Table(
148 "test",
149 metadata,
150 Column(
151 "id", Integer, primary_key=True, Identity(start=100, increment=10)
152 ),
153 Column("name", String(20)),
154 )
155
156The CREATE TABLE for the above :class:`_schema.Table` object would be:
157
158.. sourcecode:: sql
159
160 CREATE TABLE test (
161 id INTEGER NOT NULL IDENTITY(100,10) PRIMARY KEY,
162 name VARCHAR(20) NULL,
163 )
164
165.. note::
166
167 The :class:`_schema.Identity` object supports many other parameter in
168 addition to ``start`` and ``increment``. These are not supported by
169 SQL Server and will be ignored when generating the CREATE TABLE ddl.
170
171.. versionchanged:: 1.3.19 The :class:`_schema.Identity` object is
172 now used to affect the
173 ``IDENTITY`` generator for a :class:`_schema.Column` under SQL Server.
174 Previously, the :class:`.Sequence` object was used. As SQL Server now
175 supports real sequences as a separate construct, :class:`.Sequence` will be
176 functional in the normal way starting from SQLAlchemy version 1.4.
177
178
179Using IDENTITY with Non-Integer numeric types
180^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
181
182SQL Server also allows ``IDENTITY`` to be used with ``NUMERIC`` columns. To
183implement this pattern smoothly in SQLAlchemy, the primary datatype of the
184column should remain as ``Integer``, however the underlying implementation
185type deployed to the SQL Server database can be specified as ``Numeric`` using
186:meth:`.TypeEngine.with_variant`::
187
188 from sqlalchemy import Column
189 from sqlalchemy import Integer
190 from sqlalchemy import Numeric
191 from sqlalchemy import String
192 from sqlalchemy.ext.declarative import declarative_base
193
194 Base = declarative_base()
195
196
197 class TestTable(Base):
198 __tablename__ = "test"
199 id = Column(
200 Integer().with_variant(Numeric(10, 0), "mssql"),
201 primary_key=True,
202 autoincrement=True,
203 )
204 name = Column(String)
205
206In the above example, ``Integer().with_variant()`` provides clear usage
207information that accurately describes the intent of the code. The general
208restriction that ``autoincrement`` only applies to ``Integer`` is established
209at the metadata level and not at the per-dialect level.
210
211When using the above pattern, the primary key identifier that comes back from
212the insertion of a row, which is also the value that would be assigned to an
213ORM object such as ``TestTable`` above, will be an instance of ``Decimal()``
214and not ``int`` when using SQL Server. The numeric return type of the
215:class:`_types.Numeric` type can be changed to return floats by passing False
216to :paramref:`_types.Numeric.asdecimal`. To normalize the return type of the
217above ``Numeric(10, 0)`` to return Python ints (which also support "long"
218integer values in Python 3), use :class:`_types.TypeDecorator` as follows::
219
220 from sqlalchemy import TypeDecorator
221
222
223 class NumericAsInteger(TypeDecorator):
224 "normalize floating point return values into ints"
225
226 impl = Numeric(10, 0, asdecimal=False)
227 cache_ok = True
228
229 def process_result_value(self, value, dialect):
230 if value is not None:
231 value = int(value)
232 return value
233
234
235 class TestTable(Base):
236 __tablename__ = "test"
237 id = Column(
238 Integer().with_variant(NumericAsInteger, "mssql"),
239 primary_key=True,
240 autoincrement=True,
241 )
242 name = Column(String)
243
244.. _mssql_insert_behavior:
245
246INSERT behavior
247^^^^^^^^^^^^^^^^
248
249Handling of the ``IDENTITY`` column at INSERT time involves two key
250techniques. The most common is being able to fetch the "last inserted value"
251for a given ``IDENTITY`` column, a process which SQLAlchemy performs
252implicitly in many cases, most importantly within the ORM.
253
254The process for fetching this value has several variants:
255
256* In the vast majority of cases, RETURNING is used in conjunction with INSERT
257 statements on SQL Server in order to get newly generated primary key values:
258
259 .. sourcecode:: sql
260
261 INSERT INTO t (x) OUTPUT inserted.id VALUES (?)
262
263 As of SQLAlchemy 2.0, the :ref:`engine_insertmanyvalues` feature is also
264 used by default to optimize many-row INSERT statements; for SQL Server
265 the feature takes place for both RETURNING and-non RETURNING
266 INSERT statements.
267
268 .. versionchanged:: 2.0.10 The :ref:`engine_insertmanyvalues` feature for
269 SQL Server was temporarily disabled for SQLAlchemy version 2.0.9 due to
270 issues with row ordering. As of 2.0.10 the feature is re-enabled, with
271 special case handling for the unit of work's requirement for RETURNING to
272 be ordered.
273
274* When RETURNING is not available or has been disabled via
275 ``implicit_returning=False``, either the ``scope_identity()`` function or
276 the ``@@identity`` variable is used; behavior varies by backend:
277
278 * when using PyODBC, the phrase ``; select scope_identity()`` will be
279 appended to the end of the INSERT statement; a second result set will be
280 fetched in order to receive the value. Given a table as::
281
282 t = Table(
283 "t",
284 metadata,
285 Column("id", Integer, primary_key=True),
286 Column("x", Integer),
287 implicit_returning=False,
288 )
289
290 an INSERT will look like:
291
292 .. sourcecode:: sql
293
294 INSERT INTO t (x) VALUES (?); select scope_identity()
295
296 * Other dialects such as pymssql will call upon
297 ``SELECT scope_identity() AS lastrowid`` subsequent to an INSERT
298 statement. If the flag ``use_scope_identity=False`` is passed to
299 :func:`_sa.create_engine`,
300 the statement ``SELECT @@identity AS lastrowid``
301 is used instead.
302
303A table that contains an ``IDENTITY`` column will prohibit an INSERT statement
304that refers to the identity column explicitly. The SQLAlchemy dialect will
305detect when an INSERT construct, created using a core
306:func:`_expression.insert`
307construct (not a plain string SQL), refers to the identity column, and
308in this case will emit ``SET IDENTITY_INSERT ON`` prior to the insert
309statement proceeding, and ``SET IDENTITY_INSERT OFF`` subsequent to the
310execution. Given this example::
311
312 m = MetaData()
313 t = Table(
314 "t", m, Column("id", Integer, primary_key=True), Column("x", Integer)
315 )
316 m.create_all(engine)
317
318 with engine.begin() as conn:
319 conn.execute(t.insert(), {"id": 1, "x": 1}, {"id": 2, "x": 2})
320
321The above column will be created with IDENTITY, however the INSERT statement
322we emit is specifying explicit values. In the echo output we can see
323how SQLAlchemy handles this:
324
325.. sourcecode:: sql
326
327 CREATE TABLE t (
328 id INTEGER NOT NULL IDENTITY(1,1),
329 x INTEGER NULL,
330 PRIMARY KEY (id)
331 )
332
333 COMMIT
334 SET IDENTITY_INSERT t ON
335 INSERT INTO t (id, x) VALUES (?, ?)
336 ((1, 1), (2, 2))
337 SET IDENTITY_INSERT t OFF
338 COMMIT
339
340
341
342This is an auxiliary use case suitable for testing and bulk insert scenarios.
343
344SEQUENCE support
345----------------
346
347The :class:`.Sequence` object creates "real" sequences, i.e.,
348``CREATE SEQUENCE``:
349
350.. sourcecode:: pycon+sql
351
352 >>> from sqlalchemy import Sequence
353 >>> from sqlalchemy.schema import CreateSequence
354 >>> from sqlalchemy.dialects import mssql
355 >>> print(
356 ... CreateSequence(Sequence("my_seq", start=1)).compile(
357 ... dialect=mssql.dialect()
358 ... )
359 ... )
360 {printsql}CREATE SEQUENCE my_seq START WITH 1
361
362For integer primary key generation, SQL Server's ``IDENTITY`` construct should
363generally be preferred vs. sequence.
364
365.. tip::
366
367 The default start value for T-SQL is ``-2**63`` instead of 1 as
368 in most other SQL databases. Users should explicitly set the
369 :paramref:`.Sequence.start` to 1 if that's the expected default::
370
371 seq = Sequence("my_sequence", start=1)
372
373.. versionadded:: 1.4 added SQL Server support for :class:`.Sequence`
374
375.. versionchanged:: 2.0 The SQL Server dialect will no longer implicitly
376 render "START WITH 1" for ``CREATE SEQUENCE``, which was the behavior
377 first implemented in version 1.4.
378
379MAX on VARCHAR / NVARCHAR
380-------------------------
381
382SQL Server supports the special string "MAX" within the
383:class:`_types.VARCHAR` and :class:`_types.NVARCHAR` datatypes,
384to indicate "maximum length possible". The dialect currently handles this as
385a length of "None" in the base type, rather than supplying a
386dialect-specific version of these types, so that a base type
387specified such as ``VARCHAR(None)`` can assume "unlengthed" behavior on
388more than one backend without using dialect-specific types.
389
390To build a SQL Server VARCHAR or NVARCHAR with MAX length, use None::
391
392 my_table = Table(
393 "my_table",
394 metadata,
395 Column("my_data", VARCHAR(None)),
396 Column("my_n_data", NVARCHAR(None)),
397 )
398
399Collation Support
400-----------------
401
402Character collations are supported by the base string types,
403specified by the string argument "collation"::
404
405 from sqlalchemy import VARCHAR
406
407 Column("login", VARCHAR(32, collation="Latin1_General_CI_AS"))
408
409When such a column is associated with a :class:`_schema.Table`, the
410CREATE TABLE statement for this column will yield:
411
412.. sourcecode:: sql
413
414 login VARCHAR(32) COLLATE Latin1_General_CI_AS NULL
415
416LIMIT/OFFSET Support
417--------------------
418
419MSSQL has added support for LIMIT / OFFSET as of SQL Server 2012, via the
420"OFFSET n ROWS" and "FETCH NEXT n ROWS" clauses. SQLAlchemy supports these
421syntaxes automatically if SQL Server 2012 or greater is detected.
422
423.. versionchanged:: 1.4 support added for SQL Server "OFFSET n ROWS" and
424 "FETCH NEXT n ROWS" syntax.
425
426For statements that specify only LIMIT and no OFFSET, all versions of SQL
427Server support the TOP keyword. This syntax is used for all SQL Server
428versions when no OFFSET clause is present. A statement such as::
429
430 select(some_table).limit(5)
431
432will render similarly to:
433
434.. sourcecode:: sql
435
436 SELECT TOP 5 col1, col2.. FROM table
437
438For versions of SQL Server prior to SQL Server 2012, a statement that uses
439LIMIT and OFFSET, or just OFFSET alone, will be rendered using the
440``ROW_NUMBER()`` window function. A statement such as::
441
442 select(some_table).order_by(some_table.c.col3).limit(5).offset(10)
443
444will render similarly to:
445
446.. sourcecode:: sql
447
448 SELECT anon_1.col1, anon_1.col2 FROM (SELECT col1, col2,
449 ROW_NUMBER() OVER (ORDER BY col3) AS
450 mssql_rn FROM table WHERE t.x = :x_1) AS
451 anon_1 WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1
452
453Note that when using LIMIT and/or OFFSET, whether using the older
454or newer SQL Server syntaxes, the statement must have an ORDER BY as well,
455else a :class:`.CompileError` is raised.
456
457.. _mssql_comment_support:
458
459DDL Comment Support
460--------------------
461
462Comment support, which includes DDL rendering for attributes such as
463:paramref:`_schema.Table.comment` and :paramref:`_schema.Column.comment`, as
464well as the ability to reflect these comments, is supported assuming a
465supported version of SQL Server is in use. If a non-supported version such as
466Azure Synapse is detected at first-connect time (based on the presence
467of the ``fn_listextendedproperty`` SQL function), comment support including
468rendering and table-comment reflection is disabled, as both features rely upon
469SQL Server stored procedures and functions that are not available on all
470backend types.
471
472To force comment support to be on or off, bypassing autodetection, set the
473parameter ``supports_comments`` within :func:`_sa.create_engine`::
474
475 e = create_engine("mssql+pyodbc://u:p@dsn", supports_comments=False)
476
477.. versionadded:: 2.0 Added support for table and column comments for
478 the SQL Server dialect, including DDL generation and reflection.
479
480.. _mssql_isolation_level:
481
482Transaction Isolation Level
483---------------------------
484
485All SQL Server dialects support setting of transaction isolation level
486both via a dialect-specific parameter
487:paramref:`_sa.create_engine.isolation_level`
488accepted by :func:`_sa.create_engine`,
489as well as the :paramref:`.Connection.execution_options.isolation_level`
490argument as passed to
491:meth:`_engine.Connection.execution_options`.
492This feature works by issuing the
493command ``SET TRANSACTION ISOLATION LEVEL <level>`` for
494each new connection.
495
496To set isolation level using :func:`_sa.create_engine`::
497
498 engine = create_engine(
499 "mssql+pyodbc://scott:tiger@ms_2008", isolation_level="REPEATABLE READ"
500 )
501
502To set using per-connection execution options::
503
504 connection = engine.connect()
505 connection = connection.execution_options(isolation_level="READ COMMITTED")
506
507Valid values for ``isolation_level`` include:
508
509* ``AUTOCOMMIT`` - pyodbc / pymssql-specific
510* ``READ COMMITTED``
511* ``READ UNCOMMITTED``
512* ``REPEATABLE READ``
513* ``SERIALIZABLE``
514* ``SNAPSHOT`` - specific to SQL Server
515
516There are also more options for isolation level configurations, such as
517"sub-engine" objects linked to a main :class:`_engine.Engine` which each apply
518different isolation level settings. See the discussion at
519:ref:`dbapi_autocommit` for background.
520
521.. seealso::
522
523 :ref:`dbapi_autocommit`
524
525.. _mssql_reset_on_return:
526
527Temporary Table / Resource Reset for Connection Pooling
528-------------------------------------------------------
529
530The :class:`.QueuePool` connection pool implementation used
531by the SQLAlchemy :class:`.Engine` object includes
532:ref:`reset on return <pool_reset_on_return>` behavior that will invoke
533the DBAPI ``.rollback()`` method when connections are returned to the pool.
534While this rollback will clear out the immediate state used by the previous
535transaction, it does not cover a wider range of session-level state, including
536temporary tables as well as other server state such as prepared statement
537handles and statement caches. An undocumented SQL Server procedure known
538as ``sp_reset_connection`` is known to be a workaround for this issue which
539will reset most of the session state that builds up on a connection, including
540temporary tables.
541
542To install ``sp_reset_connection`` as the means of performing reset-on-return,
543the :meth:`.PoolEvents.reset` event hook may be used, as demonstrated in the
544example below. The :paramref:`_sa.create_engine.pool_reset_on_return` parameter
545is set to ``None`` so that the custom scheme can replace the default behavior
546completely. The custom hook implementation calls ``.rollback()`` in any case,
547as it's usually important that the DBAPI's own tracking of commit/rollback
548will remain consistent with the state of the transaction::
549
550 from sqlalchemy import create_engine
551 from sqlalchemy import event
552
553 mssql_engine = create_engine(
554 "mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+17+for+SQL+Server",
555 # disable default reset-on-return scheme
556 pool_reset_on_return=None,
557 )
558
559
560 @event.listens_for(mssql_engine, "reset")
561 def _reset_mssql(dbapi_connection, connection_record, reset_state):
562 if not reset_state.terminate_only:
563 dbapi_connection.execute("{call sys.sp_reset_connection}")
564
565 # so that the DBAPI itself knows that the connection has been
566 # reset
567 dbapi_connection.rollback()
568
569.. versionchanged:: 2.0.0b3 Added additional state arguments to
570 the :meth:`.PoolEvents.reset` event and additionally ensured the event
571 is invoked for all "reset" occurrences, so that it's appropriate
572 as a place for custom "reset" handlers. Previous schemes which
573 use the :meth:`.PoolEvents.checkin` handler remain usable as well.
574
575.. seealso::
576
577 :ref:`pool_reset_on_return` - in the :ref:`pooling_toplevel` documentation
578
579Nullability
580-----------
581MSSQL has support for three levels of column nullability. The default
582nullability allows nulls and is explicit in the CREATE TABLE
583construct:
584
585.. sourcecode:: sql
586
587 name VARCHAR(20) NULL
588
589If ``nullable=None`` is specified then no specification is made. In
590other words the database's configured default is used. This will
591render:
592
593.. sourcecode:: sql
594
595 name VARCHAR(20)
596
597If ``nullable`` is ``True`` or ``False`` then the column will be
598``NULL`` or ``NOT NULL`` respectively.
599
600Date / Time Handling
601--------------------
602DATE and TIME are supported. Bind parameters are converted
603to datetime.datetime() objects as required by most MSSQL drivers,
604and results are processed from strings if needed.
605The DATE and TIME types are not available for MSSQL 2005 and
606previous - if a server version below 2008 is detected, DDL
607for these types will be issued as DATETIME.
608
609.. _mssql_large_type_deprecation:
610
611Large Text/Binary Type Deprecation
612----------------------------------
613
614Per
615`SQL Server 2012/2014 Documentation <https://technet.microsoft.com/en-us/library/ms187993.aspx>`_,
616the ``NTEXT``, ``TEXT`` and ``IMAGE`` datatypes are to be removed from SQL
617Server in a future release. SQLAlchemy normally relates these types to the
618:class:`.UnicodeText`, :class:`_expression.TextClause` and
619:class:`.LargeBinary` datatypes.
620
621In order to accommodate this change, a new flag ``deprecate_large_types``
622is added to the dialect, which will be automatically set based on detection
623of the server version in use, if not otherwise set by the user. The
624behavior of this flag is as follows:
625
626* When this flag is ``True``, the :class:`.UnicodeText`,
627 :class:`_expression.TextClause` and
628 :class:`.LargeBinary` datatypes, when used to render DDL, will render the
629 types ``NVARCHAR(max)``, ``VARCHAR(max)``, and ``VARBINARY(max)``,
630 respectively. This is a new behavior as of the addition of this flag.
631
632* When this flag is ``False``, the :class:`.UnicodeText`,
633 :class:`_expression.TextClause` and
634 :class:`.LargeBinary` datatypes, when used to render DDL, will render the
635 types ``NTEXT``, ``TEXT``, and ``IMAGE``,
636 respectively. This is the long-standing behavior of these types.
637
638* The flag begins with the value ``None``, before a database connection is
639 established. If the dialect is used to render DDL without the flag being
640 set, it is interpreted the same as ``False``.
641
642* On first connection, the dialect detects if SQL Server version 2012 or
643 greater is in use; if the flag is still at ``None``, it sets it to ``True``
644 or ``False`` based on whether 2012 or greater is detected.
645
646* The flag can be set to either ``True`` or ``False`` when the dialect
647 is created, typically via :func:`_sa.create_engine`::
648
649 eng = create_engine(
650 "mssql+pymssql://user:pass@host/db", deprecate_large_types=True
651 )
652
653* Complete control over whether the "old" or "new" types are rendered is
654 available in all SQLAlchemy versions by using the UPPERCASE type objects
655 instead: :class:`_types.NVARCHAR`, :class:`_types.VARCHAR`,
656 :class:`_types.VARBINARY`, :class:`_types.TEXT`, :class:`_mssql.NTEXT`,
657 :class:`_mssql.IMAGE`
658 will always remain fixed and always output exactly that
659 type.
660
661.. _multipart_schema_names:
662
663Multipart Schema Names
664----------------------
665
666SQL Server schemas sometimes require multiple parts to their "schema"
667qualifier, that is, including the database name and owner name as separate
668tokens, such as ``mydatabase.dbo.some_table``. These multipart names can be set
669at once using the :paramref:`_schema.Table.schema` argument of
670:class:`_schema.Table`::
671
672 Table(
673 "some_table",
674 metadata,
675 Column("q", String(50)),
676 schema="mydatabase.dbo",
677 )
678
679When performing operations such as table or component reflection, a schema
680argument that contains a dot will be split into separate
681"database" and "owner" components in order to correctly query the SQL
682Server information schema tables, as these two values are stored separately.
683Additionally, when rendering the schema name for DDL or SQL, the two
684components will be quoted separately for case sensitive names and other
685special characters. Given an argument as below::
686
687 Table(
688 "some_table",
689 metadata,
690 Column("q", String(50)),
691 schema="MyDataBase.dbo",
692 )
693
694The above schema would be rendered as ``[MyDataBase].dbo``, and also in
695reflection, would be reflected using "dbo" as the owner and "MyDataBase"
696as the database name.
697
698To control how the schema name is broken into database / owner,
699specify brackets (which in SQL Server are quoting characters) in the name.
700Below, the "owner" will be considered as ``MyDataBase.dbo`` and the
701"database" will be None::
702
703 Table(
704 "some_table",
705 metadata,
706 Column("q", String(50)),
707 schema="[MyDataBase.dbo]",
708 )
709
710To individually specify both database and owner name with special characters
711or embedded dots, use two sets of brackets::
712
713 Table(
714 "some_table",
715 metadata,
716 Column("q", String(50)),
717 schema="[MyDataBase.Period].[MyOwner.Dot]",
718 )
719
720.. versionchanged:: 1.2 the SQL Server dialect now treats brackets as
721 identifier delimiters splitting the schema into separate database
722 and owner tokens, to allow dots within either name itself.
723
724.. _legacy_schema_rendering:
725
726Legacy Schema Mode
727------------------
728
729Very old versions of the MSSQL dialect introduced the behavior such that a
730schema-qualified table would be auto-aliased when used in a
731SELECT statement; given a table::
732
733 account_table = Table(
734 "account",
735 metadata,
736 Column("id", Integer, primary_key=True),
737 Column("info", String(100)),
738 schema="customer_schema",
739 )
740
741this legacy mode of rendering would assume that "customer_schema.account"
742would not be accepted by all parts of the SQL statement, as illustrated
743below:
744
745.. sourcecode:: pycon+sql
746
747 >>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=True)
748 >>> print(account_table.select().compile(eng))
749 {printsql}SELECT account_1.id, account_1.info
750 FROM customer_schema.account AS account_1
751
752This mode of behavior is now off by default, as it appears to have served
753no purpose; however in the case that legacy applications rely upon it,
754it is available using the ``legacy_schema_aliasing`` argument to
755:func:`_sa.create_engine` as illustrated above.
756
757.. deprecated:: 1.4
758
759 The ``legacy_schema_aliasing`` flag is now
760 deprecated and will be removed in a future release.
761
762.. _mssql_indexes:
763
764Clustered Index Support
765-----------------------
766
767The MSSQL dialect supports clustered indexes (and primary keys) via the
768``mssql_clustered`` option. This option is available to :class:`.Index`,
769:class:`.UniqueConstraint`. and :class:`.PrimaryKeyConstraint`.
770For indexes this option can be combined with the ``mssql_columnstore`` one
771to create a clustered columnstore index.
772
773To generate a clustered index::
774
775 Index("my_index", table.c.x, mssql_clustered=True)
776
777which renders the index as ``CREATE CLUSTERED INDEX my_index ON table (x)``.
778
779To generate a clustered primary key use::
780
781 Table(
782 "my_table",
783 metadata,
784 Column("x", ...),
785 Column("y", ...),
786 PrimaryKeyConstraint("x", "y", mssql_clustered=True),
787 )
788
789which will render the table, for example, as:
790
791.. sourcecode:: sql
792
793 CREATE TABLE my_table (
794 x INTEGER NOT NULL,
795 y INTEGER NOT NULL,
796 PRIMARY KEY CLUSTERED (x, y)
797 )
798
799Similarly, we can generate a clustered unique constraint using::
800
801 Table(
802 "my_table",
803 metadata,
804 Column("x", ...),
805 Column("y", ...),
806 PrimaryKeyConstraint("x"),
807 UniqueConstraint("y", mssql_clustered=True),
808 )
809
810To explicitly request a non-clustered primary key (for example, when
811a separate clustered index is desired), use::
812
813 Table(
814 "my_table",
815 metadata,
816 Column("x", ...),
817 Column("y", ...),
818 PrimaryKeyConstraint("x", "y", mssql_clustered=False),
819 )
820
821which will render the table, for example, as:
822
823.. sourcecode:: sql
824
825 CREATE TABLE my_table (
826 x INTEGER NOT NULL,
827 y INTEGER NOT NULL,
828 PRIMARY KEY NONCLUSTERED (x, y)
829 )
830
831Columnstore Index Support
832-------------------------
833
834The MSSQL dialect supports columnstore indexes via the ``mssql_columnstore``
835option. This option is available to :class:`.Index`. It be combined with
836the ``mssql_clustered`` option to create a clustered columnstore index.
837
838To generate a columnstore index::
839
840 Index("my_index", table.c.x, mssql_columnstore=True)
841
842which renders the index as ``CREATE COLUMNSTORE INDEX my_index ON table (x)``.
843
844To generate a clustered columnstore index provide no columns::
845
846 idx = Index("my_index", mssql_clustered=True, mssql_columnstore=True)
847 # required to associate the index with the table
848 table.append_constraint(idx)
849
850the above renders the index as
851``CREATE CLUSTERED COLUMNSTORE INDEX my_index ON table``.
852
853.. versionadded:: 2.0.18
854
855MSSQL-Specific Index Options
856-----------------------------
857
858In addition to clustering, the MSSQL dialect supports other special options
859for :class:`.Index`.
860
861INCLUDE
862^^^^^^^
863
864The ``mssql_include`` option renders INCLUDE(colname) for the given string
865names::
866
867 Index("my_index", table.c.x, mssql_include=["y"])
868
869would render the index as ``CREATE INDEX my_index ON table (x) INCLUDE (y)``
870
871.. _mssql_index_where:
872
873Filtered Indexes
874^^^^^^^^^^^^^^^^
875
876The ``mssql_where`` option renders WHERE(condition) for the given string
877names::
878
879 Index("my_index", table.c.x, mssql_where=table.c.x > 10)
880
881would render the index as ``CREATE INDEX my_index ON table (x) WHERE x > 10``.
882
883.. versionadded:: 1.3.4
884
885Index ordering
886^^^^^^^^^^^^^^
887
888Index ordering is available via functional expressions, such as::
889
890 Index("my_index", table.c.x.desc())
891
892would render the index as ``CREATE INDEX my_index ON table (x DESC)``
893
894.. seealso::
895
896 :ref:`schema_indexes_functional`
897
898Compatibility Levels
899--------------------
900MSSQL supports the notion of setting compatibility levels at the
901database level. This allows, for instance, to run a database that
902is compatible with SQL2000 while running on a SQL2005 database
903server. ``server_version_info`` will always return the database
904server version information (in this case SQL2005) and not the
905compatibility level information. Because of this, if running under
906a backwards compatibility mode SQLAlchemy may attempt to use T-SQL
907statements that are unable to be parsed by the database server.
908
909.. _mssql_triggers:
910
911Triggers
912--------
913
914SQLAlchemy by default uses OUTPUT INSERTED to get at newly
915generated primary key values via IDENTITY columns or other
916server side defaults. MS-SQL does not
917allow the usage of OUTPUT INSERTED on tables that have triggers.
918To disable the usage of OUTPUT INSERTED on a per-table basis,
919specify ``implicit_returning=False`` for each :class:`_schema.Table`
920which has triggers::
921
922 Table(
923 "mytable",
924 metadata,
925 Column("id", Integer, primary_key=True),
926 # ...,
927 implicit_returning=False,
928 )
929
930Declarative form::
931
932 class MyClass(Base):
933 # ...
934 __table_args__ = {"implicit_returning": False}
935
936.. _mssql_rowcount_versioning:
937
938Rowcount Support / ORM Versioning
939---------------------------------
940
941The SQL Server drivers may have limited ability to return the number
942of rows updated from an UPDATE or DELETE statement.
943
944As of this writing, the PyODBC driver is not able to return a rowcount when
945OUTPUT INSERTED is used. Previous versions of SQLAlchemy therefore had
946limitations for features such as the "ORM Versioning" feature that relies upon
947accurate rowcounts in order to match version numbers with matched rows.
948
949SQLAlchemy 2.0 now retrieves the "rowcount" manually for these particular use
950cases based on counting the rows that arrived back within RETURNING; so while
951the driver still has this limitation, the ORM Versioning feature is no longer
952impacted by it. As of SQLAlchemy 2.0.5, ORM versioning has been fully
953re-enabled for the pyodbc driver.
954
955.. versionchanged:: 2.0.5 ORM versioning support is restored for the pyodbc
956 driver. Previously, a warning would be emitted during ORM flush that
957 versioning was not supported.
958
959
960Enabling Snapshot Isolation
961---------------------------
962
963SQL Server has a default transaction
964isolation mode that locks entire tables, and causes even mildly concurrent
965applications to have long held locks and frequent deadlocks.
966Enabling snapshot isolation for the database as a whole is recommended
967for modern levels of concurrency support. This is accomplished via the
968following ALTER DATABASE commands executed at the SQL prompt:
969
970.. sourcecode:: sql
971
972 ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
973
974 ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
975
976Background on SQL Server snapshot isolation is available at
977https://msdn.microsoft.com/en-us/library/ms175095.aspx.
978
979""" # noqa
980
981from __future__ import annotations
982
983import codecs
984import datetime
985import operator
986import re
987from typing import Any
988from typing import overload
989from typing import TYPE_CHECKING
990from uuid import UUID as _python_UUID
991
992from . import information_schema as ischema
993from .json import JSON
994from .json import JSONIndexType
995from .json import JSONPathType
996from ... import exc
997from ... import Identity
998from ... import schema as sa_schema
999from ... import Sequence
1000from ... import sql
1001from ... import text
1002from ... import util
1003from ...engine import cursor as _cursor
1004from ...engine import default
1005from ...engine import reflection
1006from ...engine.reflection import ReflectionDefaults
1007from ...sql import coercions
1008from ...sql import compiler
1009from ...sql import elements
1010from ...sql import expression
1011from ...sql import func
1012from ...sql import quoted_name
1013from ...sql import roles
1014from ...sql import sqltypes
1015from ...sql import try_cast as try_cast # noqa: F401
1016from ...sql import util as sql_util
1017from ...sql._typing import is_sql_compiler
1018from ...sql.compiler import InsertmanyvaluesSentinelOpts
1019from ...sql.elements import TryCast as TryCast # noqa: F401
1020from ...types import BIGINT
1021from ...types import BINARY
1022from ...types import CHAR
1023from ...types import DATE
1024from ...types import DATETIME
1025from ...types import DECIMAL
1026from ...types import FLOAT
1027from ...types import INTEGER
1028from ...types import NCHAR
1029from ...types import NUMERIC
1030from ...types import NVARCHAR
1031from ...types import SMALLINT
1032from ...types import TEXT
1033from ...types import VARCHAR
1034from ...util import update_wrapper
1035from ...util.typing import Literal
1036
1037if TYPE_CHECKING:
1038 from ...sql.ddl import DropIndex
1039 from ...sql.dml import DMLState
1040 from ...sql.selectable import TableClause
1041
1042# https://sqlserverbuilds.blogspot.com/
1043MS_2017_VERSION = (14,)
1044MS_2016_VERSION = (13,)
1045MS_2014_VERSION = (12,)
1046MS_2012_VERSION = (11,)
1047MS_2008_VERSION = (10,)
1048MS_2005_VERSION = (9,)
1049MS_2000_VERSION = (8,)
1050
1051RESERVED_WORDS = {
1052 "add",
1053 "all",
1054 "alter",
1055 "and",
1056 "any",
1057 "as",
1058 "asc",
1059 "authorization",
1060 "backup",
1061 "begin",
1062 "between",
1063 "break",
1064 "browse",
1065 "bulk",
1066 "by",
1067 "cascade",
1068 "case",
1069 "check",
1070 "checkpoint",
1071 "close",
1072 "clustered",
1073 "coalesce",
1074 "collate",
1075 "column",
1076 "commit",
1077 "compute",
1078 "constraint",
1079 "contains",
1080 "containstable",
1081 "continue",
1082 "convert",
1083 "create",
1084 "cross",
1085 "current",
1086 "current_date",
1087 "current_time",
1088 "current_timestamp",
1089 "current_user",
1090 "cursor",
1091 "database",
1092 "dbcc",
1093 "deallocate",
1094 "declare",
1095 "default",
1096 "delete",
1097 "deny",
1098 "desc",
1099 "disk",
1100 "distinct",
1101 "distributed",
1102 "double",
1103 "drop",
1104 "dump",
1105 "else",
1106 "end",
1107 "errlvl",
1108 "escape",
1109 "except",
1110 "exec",
1111 "execute",
1112 "exists",
1113 "exit",
1114 "external",
1115 "fetch",
1116 "file",
1117 "fillfactor",
1118 "for",
1119 "foreign",
1120 "freetext",
1121 "freetexttable",
1122 "from",
1123 "full",
1124 "function",
1125 "goto",
1126 "grant",
1127 "group",
1128 "having",
1129 "holdlock",
1130 "identity",
1131 "identity_insert",
1132 "identitycol",
1133 "if",
1134 "in",
1135 "index",
1136 "inner",
1137 "insert",
1138 "intersect",
1139 "into",
1140 "is",
1141 "join",
1142 "key",
1143 "kill",
1144 "left",
1145 "like",
1146 "lineno",
1147 "load",
1148 "merge",
1149 "national",
1150 "nocheck",
1151 "nonclustered",
1152 "not",
1153 "null",
1154 "nullif",
1155 "of",
1156 "off",
1157 "offsets",
1158 "on",
1159 "open",
1160 "opendatasource",
1161 "openquery",
1162 "openrowset",
1163 "openxml",
1164 "option",
1165 "or",
1166 "order",
1167 "outer",
1168 "over",
1169 "percent",
1170 "pivot",
1171 "plan",
1172 "precision",
1173 "primary",
1174 "print",
1175 "proc",
1176 "procedure",
1177 "public",
1178 "raiserror",
1179 "read",
1180 "readtext",
1181 "reconfigure",
1182 "references",
1183 "replication",
1184 "restore",
1185 "restrict",
1186 "return",
1187 "revert",
1188 "revoke",
1189 "right",
1190 "rollback",
1191 "rowcount",
1192 "rowguidcol",
1193 "rule",
1194 "save",
1195 "schema",
1196 "securityaudit",
1197 "select",
1198 "session_user",
1199 "set",
1200 "setuser",