[sql server] cmd

# base
sql server
sql server management studio (ssms)

Q: what is the sql server edition
SQL2019-SSEI-Eval.exe (evaluate)
SQL2019-SSEI-Dev.exe (developer)
SQL2019-SSEI-Expr.exe (express)
=> ref: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Q: how to download ssms
SSMS-Setup-CHT.exe
=> ref: https://docs.microsoft.com/zh-tw/sql/ssms/download-sql-server-management-studio-ssms

# command
sqlcmd -? // help
sqlcmd -S <server> -U <user> // connect to server
sqlcmd -S <server> -U <user> -P <password>
sqlcmd -S <server> -i <file-path> // execute some file (default: window authentication)
sqlcmd -S <server> -U <user> -P <password> -i <file-path> // execute some file
sqlcmd -S <server> -U <user> -P <password> -d <db> -i <file-path> // specify the database
sqlcmd -S <server> -U <user> -P <password> -d <db> -i <file-path> -f <codepage> // ex. -f 65001 (utf-8)
sqlcmd -S <server> -U <user> -P <password> -d <db> -q <sql> // ex. -q "select * from SomeTable"
sqlcmd -S <server> -U <user> -P <password> -d <db> -Q <sql> // after query, exit
=> ref: https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

# sqlcmd shell
## common
select @@version
go
select sysdatetimeoffset()
go
select serverproperty('collation')
go
:listvar
exit
=> ref: https://docs.microsoft.com/zh-tw/sql/tools/sqlcmd-utility?view=sql-server-ver15#sqlcmd-commands

## database
create database <db-name> collate SQL_Latin1_General_CP1_CI_AS
go
alter database <db-name> collate Chinese_Taiwan_Stroke_CI_AS
go
drop database <db-name>
go
select name, collation_name from sys.databases
go
use <db-name>
go
select db_name() // the current db name
go
select * from information_schema.tables
go
select * from information_schema.columns
go
=> ref: https://www.sqlshack.com/working-sql-server-command-line-sqlcmd/

## table
alter table <table> alter <column> <type>
go
alter table <table> drop column <column>
go

# transaction & lock
## ACID
- Atomicity (原子性)
- Consistency (一致性)
- Isolation (隔離性)
- Durability (持久性)

## transaction isolation level (事務隔離級別)
- READ UNCOMMITTED (讀未提交)
- READ COMMITTED (讀已提交)
- REPEATABLE READ (可重複讀)
- SERIALIZABLE (串型化)
=> ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

## resource type
- RID
- KEY
- PAGE
- EXTENT
- HOBT
- TABLE
- FILE
- APPLICATION
- METADATA
- APPLICATION_UNIT
- DATABASE

## lock type
- Shared Lock (S)
- Exclusive Lock (X)
- Update Lock (U)

# security
---
server: login, server role
database: user, role, schema
---
one login can map one user in each database
the user type can select "SQL user without login"
we can set the "permissions" of database, schema, table ... in their properties page
we can set the "securables" of user, role in their properties page

 server       database1
---------     --------
| login | --- | user |
---------     --------
   |
   |          database2
   |          --------
   ---------- | user |
              --------

=> ref: https://dotblogs.com.tw/supershowwei/2021/12/13/110243
=> ref: https://dotblogs.com.tw/supershowwei/2021/12/20/093543
=> ref: https://blog.miniasp.com/post/2021/06/21/Authorize-Azure-SQL-Database-to-Developers-Correctly

Q: what is schema ?
schema is a namespace
=> ref: https://blog.xuite.net/tolarku/blog/29614077

NOTE: show the current login, database and user
select suser_name() as [Login], db_name() as [Database], current_user as [User]

NOTE: show all objects in the some schema
select * from sys.objects where [schema_id] = schema_id('<schema_name>') // sys: schema, objects: view
select * from sys.objects where [schema_id] = schema_id('dbo')

NOTE: change the current user
step1. sssms -> <database> -> security -> users -> <user> -> properties -> securables -> grant impersonate on <other-user> to <user>
       grant impersonate on USER::<other-user> to <user>
step2. execute as user = '<other-user>'
step3. do-some-sql
step4. revert

NOTE: deny view and select
use master
go
create login [testdb_login] with password = <password>
go
create database testdb
go
use testdb
go
create user [testdb_user] for login [testdb_login]
go
deny view definition on SCHEMA::[dbo] to [testdb_user]
go
deny select on SCHEMA::[dbo] to [testdb_user]
go
create table [dbo].[Table_1](Name varchar(20)) on [primary]
go
=> ref: https://www.facebook.com/groups/DotNetUserGroupTaiwan/posts/2931756767117422/

NOTE: create a role, assign it to user and drop it
use testdb
go
create role [testdb_role]
go
grant execute to [testdb_role]
go
revoke execute to [testdb_role]
go
alter role [testdb_role] add member [testdb_user]
go
alter role [testdb_role] drop member [testdb_user]
go
drop role [testdb_role]
go

NOTE: change the error message language
ssms -> security -> logins -> <login> -> properties -> general -> default language: english

NOTE: show all logins default language
select [name], default_language_name from sys.server_principals // sys: schema, server_principals: view

# [note] the user sa logins to the sql server instance 
- login by "windows authentication"
- open "server properties" dialog
  - click "security" tab
  - server authentication chooses "sql server and windows authentication mode"
  - restart the sql server instance (or later)
- open security/logins/sa "login properties" dialog
  - click "general" tab
  - click "status" tab

# [note] find the executed sql statements
open tools/sql server profiler
=> ref: https://blog.xuite.net/tolarku/blog/481463389

# [note] using sqlcmd, import data to azure sql database
NOTE: azure sql database cannot use the 'use [db-name]' statement to switch database
sqlcmd -S <server> -U <user> -P <password> -d <db> -i <file-path> // -d is IMPORTANT

NOTE: the codepage of script file
sqlcmd -S <server> -U <user> -P <password> -d <db> -i <file-path> -f <codepage> // -f is IMPORTANT
=> ref: https://louis176127.pixnet.net/blog/post/351458335-%5Bsql-server%5D-sqlcmd-import-sql-file

# [note] linked server to azure sql database
=> ref: https://www.mssqltips.com/sqlservertip/6224/create-a-sql-server-linked-server-to-azure-sql-database/

# [note] time zones in azure sql database / azure sql managed instance
azure sql database does not support time zone settings (it always follows utc)
use "at time zone" in azure sql database if you need to interpret date and time information in a non-utc time zone
=> ref: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview

# [note] at time zone
---
applies to:
- sql server 2016 (13.x) and later
- azure sql database
- azure sql managed instance
- azure synapse analytics
---
when inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone.
if inputdate is provided as a datetimeoffset value, then at time zone clause converts it into the target time zone using the time zone conversion rules.
---

-- datetime
select getutcdate()
      ,getdate()
      ,getdate() at time zone 'West Asia Standard Time'

-- datetime2(7)
select sysutcdatetime()
      ,sysdatetime()
      ,sysdatetime() at time zone 'West Asia Standard Time'

-- datetimeoffset(7)
select sysdatetimeoffset()
      ,sysdatetimeoffset() at time zone 'West Asia Standard Time'

=> ref: https://stackoverflow.com/questions/36393742/using-at-time-zone-to-get-current-time-in-specified-time-zone
=> ref: https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql

# [note] list all time zones name
select * from sys.time_zone_info
=> ref: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-time-zone-info-transact-sql

# [note] change the database collation
---
applies to:
- sql server (all supported versions)
- azure sql managed instance
---
alter database <db-name> collate <collation>
=> ref: https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation

IMPORTANT:
the "alter database collate" statement is not supported on azure sql database
so, we need to decide database collation when it is created

# [note] azure sql database query cross database
"elastic database query" (preview) for azure sql database allows you to run t-sql queries that span multiple databases using a single connection point
=> ref: https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical?view=azuresql-db
=> ref: https://www.mssqltips.com/sqlservertip/6445/azure-sql-cross-database-query/

# [note] datediff() vs. datediff_big()
select datediff(second, '1900-01-01 10:01:01', '2100-01-01 10:01:01.123456') -- overflow (溢位)
select datediff_big(second, '1900-01-01 10:01:01', '2100-01-01 10:01:01.123456') -- success
=> ref: https://medium.com/ricos-note/sql-server-sql2016-%E6%96%B0%E8%B3%87%E6%96%99%E7%89%A9%E4%BB%B6-a04032a9d0fd

# [note] truncate table which be foreign
TODO

# [note] install sql server 2008 and ssms 2008
sql server 2008 r2 sp2 - express edition
=> ref: https://www.microsoft.com/zh-tw/download/details.aspx?id=30438

IMPORTANT:
before install, need .NET Framework 3.5 (includes .NET 2.0 and 3.0)
if above fail, need windows update

Q: connect to sql server 2008 from local ?
server name: <pc-name>\<sql-server-instance-name> // ex. CL03\SQLEXPRESS, localhost\SQLEXPRESS, .\SQLEXPRESS
=> ref: https://stackoverflow.com/questions/360141/how-to-connect-to-local-instance-of-sql-server-2008-express

Q: connect to sql server 2008 from local using localhost or 192.168.x.x ?
server name: localhost // fail
open 'sql server configuration manager'
- sql server network configuration
    TCP/IP -> Protocol -> General -> Enabled = Yes
    TCP/IP -> IP Addresses -> IPAll -> TCP Port = 1433
    TCP/IP -> IP Addresses -> IPAll -> TCP Dynamic Port (empty)
- sql server services
    SQL Server (<sql-server-instance-name>) restart
=> ref: https://dvoituron.com/2012/02/17/enable-tcpip-remote-connection-on-sql-server-2008/

Q: connect to sql server 2008 from remote using 192.168.x.x ?
add a firewall rule at 1433 port

# [note] linked server to azure sql database from sql server 2008
Q: invalid product name
A: just key some words
=> ref: https://shiyousan.com/post/635408773769286544

Q: server name cannot be determined
A: some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername)

Q: mini tls version
A: TODO

# [note] change the language in ssms
step1. use visual studio installer to download language package
step2. ssms -> tools -> options -> environment -> international settings -> language: english

# [note] clear server names from list in ssms
step1. hover over the item you want to remove
step2. press the delete key (del)
=> ref: https://nathondalton.wordpress.com/2014/09/15/clear-server-names-from-list-in-sql-server-management-studio/

# [note] size
-- database max size
select databasepropertyex('master', 'MaxSizeInBytes') AS DatabaseDataMaxSizeInBytes
exec sp_helpdb
exec sp_helpdb @dbname='master'

-- database current size
exec sp_spaceused

-- each table max size
TODO

-- each table current size
way1
 step1. select 'exec sp_spaceused ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '''' from information_schema.tables where TABLE_TYPE = 'BASE TABLE'
 step2. run each sql
way2
 exec sp_msforeachtable 'exec sp_spaceused [?]'
 
=> ref: https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database/48326827#48326827
=> ref: https://stackoverflow.com/questions/18014392/select-sql-server-database-size
=> ref: https://www.mssqltips.com/sqlservertip/1177/determining-space-used-for-all-tables-in-a-sql-server-database/

# [note] transaction log
TODO
=> ref: https://dotblogs.com.tw/rainmaker/2013/05/23/104746

# [note] import large data
bcp -h
bcp <db-name>.<owner-name>.<table-name> in <file-path> -c -t , -T -S <server>
=> ref: https://sdwh.dev/posts/2022/01/SQL-Server-BCP-Bulk-Insert/
=> ref: https://medium.com/%E7%A8%8B%E5%BC%8F%E8%A3%A1%E6%9C%89%E8%9F%B2/%E4%BD%BF%E7%94%A8-sql-server-bcp-%E5%8C%AF%E5%85%A5%E5%8C%AF%E5%87%BA%E5%A4%A7%E9%87%8F%E8%B3%87%E6%96%99-c623a8c4a81c
=> ref: https://sdwh.dev/posts/2020/12/SQL-Server-Import-CSV/

_a Search specified value in which table and column.sql

SET NOCOUNT ON

DECLARE @SearchStr NVARCHAR(100)

SET @SearchStr = 'admin' -- target content

CREATE TABLE #Results
  (
     ColumnName  NVARCHAR(370),
     ColumnValue NVARCHAR(3630)
  )

DECLARE @TableName  NVARCHAR(256),
        @ColumnName NVARCHAR(128),
        @SearchStr2 NVARCHAR(110)

SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.'
                                 + QUOTENAME(TABLE_NAME))
                        FROM INFORMATION_SCHEMA.TABLES
                       WHERE TABLE_TYPE = 'BASE TABLE'
                         AND QUOTENAME(TABLE_SCHEMA) + '.'
                             + QUOTENAME(TABLE_NAME) > @TableName
                         AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.'
                                                      + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)

    WHILE ( @TableName IS NOT NULL )
          AND ( @ColumnName IS NOT NULL )
    BEGIN
        SET @ColumnName = (SELECT MIN(QUOTENAME(COLUMN_NAME))
                             FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                              AND TABLE_NAME = PARSENAME(@TableName, 1)
                              AND DATA_TYPE IN ( 'char', 'varchar', 'nchar', 'nvarchar',
                                                 'int', 'decimal' )
                              AND QUOTENAME(COLUMN_NAME) > @ColumnName)

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 )
        END
    END
END

SELECT ColumnName,
       ColumnValue
  FROM #Results

DROP TABLE #Results 

_b unknown deadlock.sql

drop table if exists TestDeadlock

create table TestDeadlock(
	[Name] [varchar](10) not null,
	[Age] [int] not null,
	[Memo] [varchar](50),
	constraint [PK_TestDeadlock] primary key clustered
	(
		[Name] asc,
		[Age] asc
	) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

insert into TestDeadlock values('Peter', 27, null), ('Peter', 28, null)

-- session 1
begin tran
	
	update TestDeadlock
	set Memo = 'session 1.1'
	where [Name] = N'Peter'
	and Age = 28

	update TestDeadlock
	set Memo = 'session 1.2'
	where [Name] = N'Peter'
	and Age = 28

  --rollback

-- session 2
begin tran
	
	update TestDeadlock
	set Memo = 'session 2.1'
	where [Name] = N'Peter'
	and Age = 27
  
  --rollback