Tutorial by Examples: table
SELECT
OBJECT_ID('MemOptTable1') AS MemOptTable1_ObjectID,
OBJECT_ID('MemOptTable2') AS MemOptTable2_ObjectID
GO
SELECT
name,description
FROM sys.dm_os_loaded_modules
WHERE name LIKE '%XTP%'
GO
Show all Memory Optimized Tables:
SELECT
name,type_desc,durability_desc,...
For example, this is traditional tempdb-based table type:
CREATE TYPE dbo.testTableType AS TABLE
(
col1 INT NOT NULL,
col2 CHAR(10)
);
To memory-optimize this table type simply add the option memory_optimized=on, and add an index if there is none on the original type:
CREATE TYPE dbo....
For faster performance you can memory-optimize your table variable.
Here is the T-SQL for a traditional table variable:
DECLARE @tvp TABLE
(
col1 INT NOT NULL ,
Col2 CHAR(10)
);
To define memory-optimized variables, you must first create a memory-optimized table type and...
CREATE TABLE dbo.Employee
(
[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
, [Name] nvarchar(100) NOT NULL
, [Position] varchar(100) NOT NULL
, [Department] varchar(100) NOT NULL
, [Address] nvarchar(1024) NOT NULL
, [AnnualSalary] decimal (10,2) NOT NULL
...
The loop macro supports iteration over the keys, the values, or the keys and values of a hash table. The following examples show possibilities, but the full loop syntax allows more combinations and variants.
Over keys and values
(let ((ht (make-hash-table)))
(setf (gethash 'a ht) 1
(g...
The keys and values of a hash table can be iterated over using the macro with-hash-table-iterator. This may be a bit more complex than maphash or loop, but it could be used to implement the iteration constructs used in those methods. with-hash-table-iterator takes a name and a hash table and binds...
INSERT INTO Invoices [ /* column names may go here */ ]
VALUES (123, '1234abc', '2016-08-05 20:18:25.770', 321, 5, '2016-08-04');
Column names are required if the table you are inserting into contains a column with the IDENTITY attribute.
INSERT INTO Invoices ([ID], [Num], [DateTime], [Tota...
CREATE TABLE [dbo].[Customers]
(
CustomerID INT DEFAULT (NEXT VALUE FOR [dbo].[CustomersSeq]) NOT NULL,
CustomerName VARCHAR(100),
);
INSERT INTO [dbo].[Customers]
([CustomerName])
VALUES
('Jerry'),
('Gorge')
SELECT * FROM [dbo].[Customers]
Results
CustomerIDCustomerName10001Jerry10002Gorge
Will be available till the current connection persists for the user.
Automatically deleted when the user disconnects.
The name should start with # (#temp)
CREATE TABLE #LocalTempTable(
StudentID int,
StudentName varchar(50),
Student...
Will start with ## (##temp).
Will be deleted only if user disconnects all connections.
It behaves like a permanent table.
CREATE TABLE ##NewGlobalTempTable(
StudentID int,
StudentName varchar(50),
StudentAddress varchar(150))
Insert ...
Depending on the structure of your data, you can create variables that update dynamically.
DECLARE @CurrentID int = (SELECT TOP 1 ID FROM Table ORDER BY CreateDate desc)
DECLARE @Year int = 2014
DECLARE @CurrentID int = (SELECT ID FROM Table WHERE Year = @Year)
In most cases, you will want...
Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on system to create history table with default configuration. In the example below, a new system-versioned memory-optimized temporal table linked to a new disk-based history t...
FOREIGN KEY constraint can be added on existing tables that are still not in relationship. Imagine that we have Company and Employee tables where Employee table CompanyId column but don't have foreign key relationship.
ALTER TABLE statement enables you to add foreign key constraint on an existing c...
FOREIGN KEY columns with constraint can be added on existing tables that are still not in relationship. Imagine that we have Company and Employee tables where Employee table don't have CompanyId column.
ALTER TABLE statement enables you to add new column with foreign key constraint that references ...
Create a set
local set = {} -- empty set
Create a set with elements by setting their value to true:
local set = {pear=true, plum=true}
-- or initialize by adding the value of a variable:
local fruit = 'orange'
local other_set = {[fruit] = true} -- adds 'orange'
Add a member to the ...
