-- fibonacci
with recursive fib (n, a, b) as (
select 0, cast(0 as decimal), cast(1 as decimal)
union all
select n + 1, b, a + b
from fib
where n < 100
)
select n, a
from fib;
DECLARE @num INT = -245.66,
@num2 FLOAT = 0.25,
@num3 FLOAT = -0.8;
SELECT ABS(@num), -- 245
ACOS(@num2), -- 1.31811607165282
ASIN(@num3), -- -0.927295218001612
ATAN(@num2), -- 0.244978663126864
CEILING(@num), -- -245
COS(@num2), -- 0.968912421710645
COT(@num2), -- 3.91631736464594
DEGREES(@num2), -- 14.3239448782706
EXP(@num2), -- 1.28402541668774
FLOOR(@num3), -- -1
ROUND(@num, 1), -- -245
SIGN(@num3), -- -1
SIN(@num3), -- -0.717356090899523
TAN(@num3) -- -1.02963855705036
-- [AVG]
SELECT AVG(ListPrice)
FROM [Production].[Product];
-- Output: 437.7976
-- [COUNT]
SELECT COUNT(ProductID) AS Total
FROM [Production].[Product];
-- Output: 505
-- [MAX/MIN]
SELECT MAX(ListPrice), MIN(ListPrice)
FROM [Production].[Product];
-- Output: 3578.27, 0.00
-- [PI]
SELECT PI();
-- Output: 3.14159265358979
-- [POWER]
SELECT POWER(4, 2);
-- Output: 16
-- [RADIANS]
SELECT RADIANS(180);
-- Output: 3
-- [RANDOM]
SELECT RAND()
-- Output: 0.210903950196124
SELECT FLOOR(RAND()*(10-5)+5);
-- Output: 6
-- [SQRT]
SELECT SQRT(64)
-- Output: 8
-- [SUM]
SELECT SUM(ListPrice)
FROM [Production].[Product];
-- Output: 221087.79
-- [Syntax]
-- ABS(number) absolute (positive) value of a number
-- ACOS(number) num: -1 to 1 | arc cosine of a number
-- ASIN(number) num: -1 to 1 | arc sine of a number
-- ATAN(number) arc tangent of a number
-- AVG(expression) average value of an expression
-- CEILING(number) smallest integer value >= number
-- COS(number) cosine of a number
-- COT(number) cotangent of a number
-- COUNT(expression) number of records
-- DEGREES(number) radian value into degrees
-- EXP(number) e raised to the power of the number
-- FLOOR(number) largest integer value <= number
-- MAX(expression) max value in a set of values
-- MIN(expression) min value in a set of values
-- PI() value of PI
-- POWER(x, y) power of another number
-- RADIANS(number) degree value into radians
-- RAND(seed) random number between 0 and 1
-- ROUND(num, decimals) rounds to a decimal places
-- SIGN(number) sign of a number -1, 0 or 1
-- SIN(number) sine of a number
-- SQRT(number) square root of a number
-- SUM(expression) sum of a set of values
-- TAN(number) tangent of a number
-- Replace:
-- BakPath
-- DBName
-- DBLoginUser
-- DBLoginPassword
RESTORE filelistonly FROM DISK = 'BakPath'
USE[master]
GO
RESTORE DATABASE DBName
FROM DISK = N'BakPath'
WITH FILE = 1 , NOUNLOAD , STATS = 10, RECOVERY ,
MOVE N'DBName' TO N'D:\MSSQL\Data\DBName_Data.mdf' ,
MOVE N'DBName_log' TO N'D:\MSSQL\Data\DBName_Log.ldf'
GO
-- 修正邏輯名稱
ALTER DATABASE DBName MODIFY FILE (NAME =N'DBName', NEWNAME=N'DBName_Data' )
GO
ALTER DATABASE DBName MODIFY FILE (NAME =N'DBName_log', NEWNAME=N'DBName_Log' )
GO
USE master
GO
IF NOT EXISTS (SELECT * FROM sys. server_principals WHERE [name] = N'DBLoginUser')
CREATE LOGIN DBLoginUser WITH PASSWORD = 'DBLoginPassword', CHECK_EXPIRATION= OFF, CHECK_POLICY=OFF ;
GO
USE DBName
GO
ALTER USER DBLoginUser WITH LOGIN = DBLoginUser
GO
USE [master]
GO
ALTER DATABASE [DBName] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE[DBName]
GO
EXEC sp_addrolemember @rolename= 'db_owner', @membername = 'DBLoginUser'
GO
USE [DBName]
GO
EXEC dbo. sp_changedbowner @loginame = N'sa' , @map = false
GO
exec sp_configure 'clr enabled', '1'
GO
reconfigure with override
GO
db-usage.sql
-- =============================================
-- Author: Ian
-- Create date: 2019/08/14
-- Description: 提供查看資料庫容量使用情況
-- =============================================
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
SUM(a.total_pages) DESC
// add padding left
String.Format("{0:0000}", 5).Dump();
formate date.cs
public string FormatDate(object rowValue)
{
var cultureInfo = new CultureInfo("EN-US", false);
var dateSpec = new string[] {
"yyyy/MM/dd",
"yyyy/M/d",
"yyyyMMdd",
"yyyyMMdd/hhmm",
"MMddyyyy",
"MM/dd/yyyy",
"M/d/yyyy",
"M/d/yy",
"yyyy/MM/dd hh:mm:ss",
"yyyy/M/d hh:mm:ss",
"yyyyMMdd hh:mm:ss",
"MMddyyyy hh:mm:ss",
"MM/dd/yyyy hh:mm:ss",
"M/d/yy hh:mm:ss",
"yyyy/M/d tt hh:mm:ss",
"yyyy/MM/dd tt hh:mm:ss",
"yyyyMMdd/HH:mm",
"yyyyMMdd/HH：mm",
"M月d日",
"MM月dd日"
};
DateTime ymd = DateTime.MinValue;
if (rowValue is DateTime)
{
ymd = (DateTime)rowValue;
}
else
{
var dateTime = Regex.Replace(rowValue.ToString(), @"-|\.", "/");
if (!DateTime.TryParseExact(dateTime, dateSpec, cultureInfo, DateTimeStyles.None, out ymd))
{
if(!DateTime.TryParseExact(dateTime, "yyy/M/d", cultureInfo, DateTimeStyles.None, out ymd)){
return String.Empty;
}
ymd = ymd.AddYears(1911);
}
}
return ymd.ToString("yyyy/MM/dd");
}
javascript.js
// Sample Regex test
console.log(/^([a-z0-9]{5,})$/.test('abc123'));
// array foreach
var array1 = ['a', 'b', 'c'];
array1.forEach(function(element) {
console.log(element);
});
// remove empty item from array
var newArr = receiverArr.filter(function (el) {
return el != null && el.trim() != "";
});
// Check if checkbox is checked with jQuery
$('#' + id).is(":checked")
yoyo.sql
ALTER TABLE [dbo].[P_Event] ALTER COLUMN [WarnRStandard] bit null;
-- USE[master]
-- GO
-- RESTORE DATABASE DBName
-- FROM DISK = N'BakPath'
-- WITH FILE = 1 , NOUNLOAD , STATS = 10, RECOVERY ,
-- MOVE N'DBName' TO N'D:\MSSQL\Data\DBName_Data.mdf' ,
-- MOVE N'DBName_log' TO N'D:\MSSQL\Data\DBName_Log.ldf'
-- GO
select * from Basic