Devops Engineer

After using order by, can use LAG to feedback (up line) record and use LEAD to feedback (down line) record Example:  return the number of items produced at each location for each week and the week before. Select location, week, itemsproduced, lag(itemsproduced) over (partition by location order by week) as previtemproduced from sites Example:  return the number of items produced at each location for each week and the week next. Select location, week, itemsproduced, lead(itemsproduced) over (partition by location order by week) as previtemproduced from sites

PIVOT: CONVERT the table into a new table  Example USE AdventureWorks2008R2 ; GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture; Result DaysToManufacture          AverageCost 0                          5.0885 1                          223.88 2                          359.1082 4                          949.4105 -- Pivot...

Format: SET TRANSACTION ISOLATION LEVEL     { READ UNCOMMITTED     | READ COMMITTED     | REPEATABLE READ     | SNAPSHOT     | SERIALIZABLE     } [ ; ] 1. READ UNCOMMITTED: can read the uncommit record in update tranaction; 2. READ COMMITTED: can not  read the uncommit record in update tranaction;   3. REPEATABLE READ:  can not  read the uncommit record in update tranaction AND other tranaction can not update current read record; 4. SNAPSHOT: can read the original record before the update tranaction; 5. SERIALIZABLIE:  can not  read the uncommit record in update tranaction AND other tranaction can not update, insert, delete current read record;

Convert int, char into datetime SELECT    '2006-04-25T15:50:59.997' AS UnconvertedText,    CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,    CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ; GO Convert datetime into int, char SELECT    GETDATE() AS UnconvertedDateTime,    CAST(GETDATE() AS nvarchar(30)) AS UsingCast,    CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ; GO Pasted from: < http://msdn.microsoft.com/zh-cn/library/ms187928.aspx >

SQL Server 2005 OS: Windows   Version 1. Enterprise --> Cluster / Mirror / Snapshot 2. Standard 3. Workgroup 4. Express   Installation Components 1. Database Service --> Database Driver; Duplicataion, Full Index 2. Integration Services --> Data Migration 3. Analysis Services --> Online analysis and data mining 4. Notification Services --> Application Notification 5. Client components, Files, Tools   Management Tools   Default Databases System Databases 1. Master:     login information; System configuration 2. Model:     Create database template 3. Tempdb:  Save temporary objects 4. Msdb:       Auto jobs, Alerts 5. Resource: Read only database, system objects for sqlserver 2005    User Databases 1. AdventureWorks: Sample database 2. User1 3. User2   Database Storage Structure Dat...

Server 1. start SQL Server Management Studio 2. Right click "server", click "Properties" 3.  Click Security, choose "SQL Server and Windows Authentication mode", Click OK to save the change. 4. Right click the Security/Logins/sa, click Properties   5. Set password for account sa   6. Click Status, change login as "Enabled". Click OK to save the change   7. Close the SQL Server Manangement Studio, Use sa to login. If login successful, go to step 8. Otherwise check the error message   8. Start the SQL Server Configuration Manager, Start "SQL Server Browser" (If failed, check the status of services in "Services")   9. Click the SQL Server Network Configutation/Protocols for MSSQLSERVER, enable TCP/IP protocol.   10. Right click "TCP/IP protocol", Click "Properties". Click "IP Addresses", Check the value of "IP Address" and "TCP Por...

1. master database: save server connection information (login account, connection definition). Related files: <install path>\MSSQL10.MSSQLSERVER\MSSQL\Data\master.mdf <install path>\MSSQL10.MSSQLSERVER\MSSQL\Data\mastlog.ldf   2. model database: template databaes. Related files: <install path>\MSSQL10.MSSQLSERVER\MSSQL\Data\model.mdf <install path>\MSSQL10.MSSQLSERVER\MSSQL\Data\modellog.ldf   3. msdb database: automatic plan, automatic jobs. Related files: <install path>\MSSQL10.MSSQLSERVER\MSSQL\Data\msdbdata.mdf <install path>\MSSQL10.MSSQLSERVER\MSSQL\Data\msdblog.ldf   4. tempdb database: temp table, temp variable Related files: <install path>\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdb.mdf <install path>\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf   5. resource database: read-only database, save structure and description of system objects Related files: <install pa...

Hardware 1. CPU 32bit --> 1GHz 64bit --> 1.4GHz Recommendation: 2GHz   2. Memory Minmum: 512MB Recommendation: 2GB   3. Hard disk Raid 10 Application/Software seperate from Data file and Log file

Backup full backup: backup database <database name> to disk='<path>' Example: backup database test to disk='c:\backup\full.bak' Differential backup: backup database <database name> to disk='<path>' with differential Example: backup database test to disk='c:\backup\diff.bak' with differential Normal Log backup:  backup log <database name> to disk='<path>' Example: backup log test to disk='c:\backup\log.bak'  Tail log backup: backup log <database name> to disk='<path>'    with no_truncate Example: backup log test to disk='c:\backup\taillog.bak' with no_truncate Restore (normally you need to backup tail log before restore) full restore: restore database <database name> from disk='<path>' with recovery Example: restore database test from disk='c:\backup\full.bak' with recovery full restore + differential restore:  re...