https://sqlines.com/ 2026-04-18T03:00:16+00:00 https://sqlines.com/ https://sqlines.com/lib/images/favicon.ico text/html 2011-12-14T11:28:29+00:00 https://sqlines.com/sql-server/ansi_nulls?rev=1323862109&do=diff ANSI_NULLS option defines whether =, <> and != operators can be used to compare with NULL in addition to IS NULL and IS NOT NULL predicates. Syntax SET ANSI_NULLS ON | OFF Values ON allows only IS [NOT] NULL OFF enables =, <> and != with NULL Default ON since SQL Server 2008 OFF in earlier versions Note SET ANSI_DEFAULTS ON/OFF automatically sets ANSI_NULLS ON/OFF Get Current Execute SELECT SESSIONPROPERTY('ANSI_NULLS') or DBCC USEROPTIONS Versions: Microsoft SQL Server 2008 R2 text/html 2011-12-14T11:41:58+00:00 https://sqlines.com/sql-server/ansi_padding?rev=1323862918&do=diff ANSI_PADDING option specifies how trailing spaces are handled in CHAR and VARCHAR columns, and trailing zeros in BINARY and VARBINARY columns. Syntax SET ANSI_PADDING ON | OFF Value ON CHAR(n) Padded with spaces to n, and n spaces retrieved VARCHAR(n) Trailing spaces are stored and retrieved if data contains them Value OFF CHAR(n) padded with spaces to n, but no spaces retrieved VARCHAR(n) Trailing spaces truncated Default ON Notes Affects new tables only, does not affect the ex… text/html 2012-06-12T19:41:49+00:00 https://sqlines.com/sql-server/create_multiple_instances?rev=1339530109&do=diff When you install SQL Server, the installation creates an instance of SQL Server. But how to create more instances? After you have installed SQL Server and have the default (or named) instance, start the setup again to create a new instance. For example, when you run SQL Server 2008 R2 Express setup, the following screen appears: text/html 2017-12-29T09:49:13+00:00 https://sqlines.com/sql-server/datetime_or_datetime2_3?rev=1514540953&do=diff Starting from SQL Server 2008, you can use DATETIME2(p) data type with fractional seconds precision up to 7 digits (0.1 microseconds) in Microsoft SQL Server. Although it is clear that you can use DATETIME2 to store more fractional seconds, is there any benefit of using DATETIME2(3) i.e with milliseconds precision that is also provided by the traditional DATETIME data type? text/html 2012-05-24T20:30:09+00:00 https://sqlines.com/sql-server/define_version?rev=1337891409&do=diff Connect to a Microsoft SQL Server database, and execute @@VERSION function to find out the version, processor architecture, build date, and operating system for the current installation of SQL Server. -- Define the SQL Server version SELECT @@VERSION; text/html 2012-10-08T09:32:06+00:00 https://sqlines.com/sql-server/drop_references?rev=1349688726&do=diff In SQL Server, you cannot drop a table if it is referenced by a FOREIGN KEY constraint. You have to either drop the child tables before removing the parent table, or remove foreign key constraints. This article provides a Transact-SQL script to drop foreign keys that can be helpful when you do not need to drop the child tables. text/html 2012-05-25T05:20:58+00:00 https://sqlines.com/sql-server/find_local_instances?rev=1337923258&do=diff You can find out the list of locally installed SQL Server instances in the Windows registry (regedit.exe) at the following location: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL Additionally, you can get this list by running the following command line (cmd.exe): text/html 2012-07-03T13:38:53+00:00 https://sqlines.com/sql-server/insert_autocommit_on_off_performance?rev=1341322733&do=diff Let's consider how autocommit mode affects the INSERT performance in Microsoft SQL Server and whether using transactions (turning autocommit off) can help improve the performance. SQL Server - INSERT in Autocommit Mode By default, SQL Server works in autocommit mode, so it commits the transaction after each DML or DDL SQL statement. Let's measure the insert performance in the autocommit mode: text/html 2012-07-03T14:08:35+00:00 https://sqlines.com/sql-server/insert_trigger_performance?rev=1341324515&do=diff Implementing Oracle BEFORE INSERT Triggers in Microsoft SQL Server Sometimes you have to use a trigger, not a default value to set the required column value. Consider a simple example: SQL Server: IF OBJECT_ID('sales', 'U') IS NOT NULL DROP TABLE sales; CREATE TABLE sales ( id INT PRIMARY KEY, created DATETIME DEFAULT GETDATE() ); GO text/html 2012-07-06T13:52:26+00:00 https://sqlines.com/sql-server/oracle-linked-server?rev=1341582746&do=diff Configuring Connection from SQL Server to Oracle using Linked Server SQL Server Linked Servers technology allows you to access non-SQL Server databases from a SQL Server database using OLE DB providers. You can connect to SQL Server and execute commands against OLE DB data sources on remote servers. text/html 2012-07-25T14:28:59+00:00 https://sqlines.com/sql-server/sql_batch_go?rev=1343226539&do=diff In SQL Server, a batch is a group of Transact-SQL statements sent to SQL Server and compiled into a single execution plan. Then these statements are executed one at a time. Last Update: Microsoft SQL Server 2012 SQL Statement Delimiter Do not confuse GO command with a semicolon (;) that is the SQL statement delimiter in SQL Server (and Oracle as well). text/html 2024-06-21T18:42:24+00:00 https://sqlines.com/sql-server/string_concat?rev=1718995344&do=diff + operator concatenates one or more strings into a single string in Microsoft SQL Server and SQL Azure. Quick Example: -- Concatenate strings 'New ' and 'York' SELECT 'New ' + 'York'; -- Result: New York + Operator Overview Summary information: text/html 2024-06-22T15:16:13+00:00 https://sqlines.com/sql-server/table_exceeds_1024_columns?rev=1719069373&do=diff In SQL Server, a table can contain a maximum of 1024 columns. For the example, the following CREATE TABLE statement fails: SQL Server: CREATE TABLE items ( c1 CHAR(1), c2 CHAR(1), c3 CHAR(1), c4 CHAR(1), c5 CHAR(1), // ... Some columns are skipped for brevity c1024 CHAR(1), c1025 CHAR(1), c1026 CHAR(1), c1027 CHAR(1) ); # Msg 1702, Level 16, State 1, Line 1 # CREATE TABLE failed because column 'c1025' in table 'items' exceeds the maxi… text/html 2024-06-22T08:58:51+00:00 https://sqlines.com/sql-server/table_exceeds_row_size_8060?rev=1719046731&do=diff In SQL Server, a table can contain a maximum of 8,060 bytes per row excluding VARCHAR, NVARCHAR, VARBINARY and some other variable length types. For the example, the following CREATE TABLE statement fails: SQL Server: CREATE TABLE items ( c1 CHAR(2000), c2 CHAR(2000), c3 CHAR(2000), c4 CHAR(2000), c5 CHAR(2000) ); # Msg 1701, Level 16, State 1, Line 1 # Creating or altering table 'items' failed because the minimum row size would be 10007, # including 7 bytes… text/html 2012-04-03T15:34:09+00:00 https://sqlines.com/sql-server/table_valued_functions?rev=1333467249&do=diff A table-valued user-defined function is a function that returns a table (a set of rows) and can be used in FROM clause. Quick Example: -- Create a table-valued user-defined function CREATE FUNCTION get_cities (@filter VARCHAR(70)) RETURNS @cities TABLE -- Table definition for result ( name VARCHAR(90), state CHAR(2) ) AS BEGIN -- Insert rows into the return table INSERT @cities SELECT * FROM cities WHERE name LIKE @filter; RETUR…