連線資訊

https://www.connectionstrings.com/

[MS SQL]停用/啟用 Foreign Key

-- Disable all table constraints

ALTER TABLE [table_name] NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE [table_name] WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE [table_name] NOCHECK CONSTRAINT [fk_name]

-- Enable single constraint

ALTER TABLE [table_name] WITH CHECK CHECK CONSTRAINT [fk_name]

https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql

[MS SQL] loop

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
BEGIN
   {...statements...}
   SET @cnt = @cnt + 1;
END;

[MS SQL]Patition

https://dotblogs.com.tw/jamesfu/archive/2012/12/25/partitiontable.aspx?fid=77758
https://dotblogs.com.tw/ricochen/2012/05/04/71971

https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-partition-scheme-transact-sql

-- 依據 Partition Function 建立 Partition Schema

create partition scheme psPartitionTest

as partition pfPartitionTest

ALL TO ([ PRIMARY ] );

--create partition function

CREATE PARTITION FUNCTION myRangePF1 (int)  

AS RANGE LEFT FOR VALUES (1, 100, 1000);  

GO  

--create partition scheme

CREATE PARTITION SCHEME myRangePS1  

AS PARTITION myRangePF1  

ALL TO ( [PRIMARY] );  

--檢查是否建立成功

select * from sys.partition_functions;

select * from sys.partition_schemes;

-- 建立 Table 一開始就使用 Partition Schema

CREATE TABLE [apuser].[A](

    [MOUDLE] [nvarchar](4) NOT NULL,

    [SEQ] int NOT NULL,

[CONTENT] [nvarchar](100),

 CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED 

(

    [MOUDLE] ASC,

    [SEQ] ASC

)

) ON myRangePS1(SEQ);

--insert 測試資料

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',1,'testtest');

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',100,'testtest');

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',101,'testtest');

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',999,'testtest');

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',999999,'testtest');

--檢查是否建於正確的partition

SELECT t.name,p.object_id,p.partition_id,p.rows 

FROM sys.partitions AS p

JOIN sys.tables AS t

ON  p.object_id = t.object_id

WHERE p.partition_id IS NOT NULL

AND t.name = 'A';

--取得某一資料的資料分割編號

SELECT $PARTITION.myRangePF1 (988) ;

--取得每個資料分割筆數(is not null)

SELECT $PARTITION.myRangePF1(SEQ) AS Partition, 

COUNT(*) AS [COUNT] FROM A 

GROUP BY $PARTITION.myRangePF1(SEQ)

ORDER BY Partition ;

--取得 partition table 分割界限值

SELECT t.name AS TableName, i.name AS IndexName,r.value AS BoundaryValue , p.partition_number, 

p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id

FROM sys.tables AS t

JOIN sys.indexes AS i

    ON t.object_id = i.object_id

JOIN sys.partitions AS p

    ON i.object_id = p.object_id AND i.index_id = p.index_id 

JOIN  sys.partition_schemes AS s 

    ON i.data_space_id = s.data_space_id

JOIN sys.partition_functions AS f 

    ON s.function_id = f.function_id

LEFT JOIN sys.partition_range_values AS r 

    ON f.function_id = r.function_id and r.boundary_id = p.partition_number

WHERE t.name = 'A' AND i.type <= 1

ORDER BY p.partition_number;

--取得 partition column

SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, 

c.name AS PartitioningColumnName 

FROM sys.tables AS t

JOIN sys.indexes AS i

    ON t.object_id = i.object_id

JOIN sys.columns AS c

    ON t.object_id = c.object_id

JOIN sys.partition_schemes AS ps

    ON ps.data_space_id = i.data_space_id

JOIN sys.index_columns AS ic

ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0 

WHERE t.name = 'A'

AND i.type <= 1

AND c.column_id = 1;

--傳回資料分割=2 相關資料

SELECT * FROM A

WHERE $PARTITION.myRangePF1(SEQ) = 2 ;

--取得所有 partition table的 partition function 、scheme和column name

SELECT OBJECT_NAME(p.OBJECT_ID) TableName,    

c.name PartColumn,      

ps.name PartScheme,    

pf.name PartFunction 

FROM sys.data_spaces  d JOIN      

sys.indexes i JOIN   

(SELECT DISTINCT OBJECT_ID     

FROM sys.partitions       

WHERE partition_number > 1) p    

ON i.OBJECT_ID = p.OBJECT_ID      

ON d.data_space_id = i.data_space_id     

JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id    

JOIN sys.partition_functions pf ON ps.function_id = pf.function_id      

JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID  

JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id;

[SQL Server]update join table

https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql

SQL Server:

update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

Oracle:

update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid

SVN小烏龜圖示消失

參考來源：http://hsieheric.blogspot.tw/2014/12/tortoisesvn-tortoisegit-icon.html

1.使用執行輸入regedit

2.路徑
HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft > Windows >CurrentVersion >Explorer > ShellIconOverlayIdentifiers

3.修改名稱改變順序

4.重啟資料夾

TABLE LOCK KILL

1.先查出要砍掉的session
select b.object_name obj_name,
       d.ctime time,
       to_char(d.lmode) l,
       to_char(d.request) r,
       a.os_user_name os_user,
       c.machine machine,
       c.program program,
       c.serial# s_serial,
       a.oracle_username ora_user,
       a.object_id obj_id,
       a.process pid,
       a.session_id s_id,
       p.spid
from v$locked_object a, all_objects b, v$session c, v$lock d,V$process p
where a.object_id=b.object_id
and c.Paddr = p.Addr
and a.session_id=c.sid
and a.session_id=d.sid
--and d.type='TX'
order by d.ctime desc, p.spid;

2.unix 砍掉該spid session
ps -ef
kill -9