Computer

Microsoft Sql Server

search

Microsoft Sql Server, MS-SQL

  • See Also
  1. Microsoft Windows Server
  2. Microsoft Windows Run Command
  3. Microsoft Internet Information Services
  4. Windows Powershell
  5. Microsoft Windows
  6. Microsoft Asp.Net
  7. Microsoft Sql Server
  • Technique
  • Select
  1. Select
    1. Select isnull( title, firstname) + ' ' + lastName as customerName from databaseTable
  2. Select with cast and convert
    1. Select LastName, CompanyName + CAST(id as nvarchar) as employeeId from databaseTable
    2. Select convert( date, orderDate, 102) as orderDate from databaseTable
  3. Select with case when
    1. Select case when completedDate is NULL then 'completed' else 'processing' end as status from databaseTable
  4. Select where
    1. Select * where id=1
    2. Select * where id is not null
    3. Select Color from databaseTable where Color in ('black', 'red','white')
    4. Select Code from databaseTable where Code like 'SM-%'
      1. % functions as wildcard
    5. Select Code from databaseTable where Code like 'SM-[^Z]%-%'
      1. Uses regular expression ([^Z] is any character other than 'Z')
  5. Ordering
    1. Select name from databaseTable order by name desc
  6. Offset and fetch
    1. Select name from databaseTable offset 5 rows fetch next 15 rows only
  7. Grouping
    1. SELECT aField, count(*) FROM [dbTableName] GROUP BY aField
    2. SELECT aField, count(*) FROM [dbTableName] GROUP BY aField HAVING count(*)>50
  8. Aggregates
    1. Aggregate functions: Count, Max, Min, Avg, StDev, Sum, Var
    2. SELECT COUNT(*) FROM [dbTableName]
    3. Examples
      1. Select customer, Rank() over (order by totalSale desc) as RankByTotalSale from invoices
      2. Select inv.cust, sum(line.total) as total from invoice as inv join lines as line on inv.id = line.invId, group by inv.cust
      3. Select count(productId) as nIds, count(quantity) as nQuantities, sum(unitPrice) as sumPrices from orderDetails
  9. Functions
    1. Select upper(Name), round (Weight, 0) as approxWeight from wrestlers
    2. Select year(tripDate) as year, DateName(month, tripDate) as month from trips
    3. Select left(ProductId,3) as model from products
  • Technique
  • Operations on Table
  1. Insert
    1. INSERT INTO [dbTableName] (field1, field2, field3) VALUES ('data1', 'data2', 'data3')
    2. INSERT INTO [dbTableName1] (field1, field2, field3) VALUES (SELECT f1, f2, f3 FROM [dbTableName2])
  2. Update
    1. Update databaseTable set survey = true where CustomerID % 9 = 1
  3. Delete
    1. DELETE FROM [dbTableName] where aField='test'
  4. Delete All Table Rows
    1. DELETE FROM [dbTableName]
    2. TRUNCATE TABLE [dbTableName]
      1. Truncate is faster, but cannot be rolled back
  5. Maintenance Queries
    1. SQL Server Version
      1. SELECT @@VERSION
  • Technique
  • Joins
  1. Inner Join
    1. Default, can be abbreviated as Join
    2. Represented by (A n B), or A intersect B
      1. All rows in which the field criteria match will be shown
      2. Criteria must be present in each table
    3. Examples (inner join is often written as join)
      1. Select * from tabA as A join tabB as B on A.id = B.id
      2. Select * customer as c join custAddress as ca on c.custId = ca.custId and ca.type = 'Main'
  2. Left Outer Join
    1. Represented by (A ? (A n B))
      1. All rows from the left-sided (first) table (regardless of match) AND
      2. Any matching from the right table
    2. Examples
      1. Select * from tabA as A left outer join tabB as B on A.id = B.id
  3. Right Outer Join
    1. Identical to a Left Outer Join except the right-sided (second) table is the priority
    2. Represented by (B ? (A n B))
      1. All rows from the right-sided (second) table (regardless of match) AND
      2. Any matching from the left table
  4. Full Outer Join
    1. Represented by (A ? B ? (A n B))
      1. All rows from both tables regardless of matches in one or the other
  • Technique
  • Unions/intersect/Except
  1. Union
    1. Example: Obtain all contacts for both staff and customers
    2. select fname, lname, phone from staff UNION fname, lname, phone from customer
  2. Intersect
    1. Example: Obtain all staff who are ALSO customers
    2. select fname, lname, phone from staff INTERSECT fname, lname, phone from customer
  3. Except
    1. Example: Obtain all staff who are NOT customers
    2. select fname, lname, phone from staff INTERSECT fname, lname, phone from customer
  • Management
  • Windows Server set-up
  1. Update the system
    1. Run Windows Update
    2. Apply Bios and firmare patches
    3. Update device drivers (e.g. drives)
  2. Set power options (esp. for dedicated database server)
    1. Set Windows Power Options to "High Performance"
    2. Set Bios Power Management to disabled or OS Control
  3. Set-up Windows server user account for each Database service
    1. User Account Examples: SqlAdmin, SqlAdminAgent, SqlBrowser
      1. Do not set-up accounts as administrator
      2. Set-up as password never expires and user cannot change password
      3. Have accounts ready for MS-SQL installation and have ready at install time
    2. Grant specific access
      1. Use Local Group Policy Editor (gpedit.msc, computer-Windows-Security-Local-User)
      2. Grant "Perform volume maintenance tasks"
      3. Grant "Lock pages in memory" (and set a max memory for MS-SQL usage)
  • Management
  • SQL Server Installation
  1. Choose a version of SQL Server
    1. Versions: Enterprise, Business, Standard, Express
    2. Licensing: Per core or per client (CAL)
    3. Enterprise is full featured, but astronomically priced
    4. Business is $5000+
    5. Standard is $3000 + CAL but has limitations and licensing is confusing
    6. Express is free, but has significant limitations
      1. No sqlAgent, <1 GB memory, <10 GB per database
      2. Can limit database size by using Filestream Data (saves blob data to file system)
      3. Can connect remotely with Sql Management Studio using SqlBrowser
        1. http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/
  2. Install only features needed currently
    1. Less exposure for attacks
    2. Faster to patch
    3. Less resource utilization
  3. Install Options
    1. Choose sql server stand-alone install or update from older version
    2. Sql Server Feature Installation (barebones options selected initially)
      1. Database Engine Services
      2. Management Tools
      3. Documentation Components
    3. Default or Named Instance
      1. Choose default on a production server with only one sql server version
      2. I chose Named Instance for SqlExpress
    4. Server Configuration
      1. Assign windows server accounts as created above (one for each service)
      2. Set Database Engine and SqlAgent both to Automatic startup
      3. Windows Authentication or Mixed Mode
      4. Many applications require mixed mode
      5. For mixed mode, enter a secure password for SA account
      6. Add Active Directory accounts you plan to use to SQL Server administrator list
    5. Data Directories
      1. Set database directory, log directory, temp DB directory, Backup directory
      2. Even using only a C-Drive, consider changing to shorter names in less deep dirs
    6. Filestream
      1. Enable to allow SQL Server to save blob data to file system outside the database
      2. SQL Express 10 GB limit does not include Filestream Data
  • Management
  • SQL Server Configuration and Maintenance
  1. Configuration via SQL Server Management Studio
    1. Right click on the SQL Server instance in the top left column (above the database list)
    2. Select Properties
    3. Memory Page
      1. Set maximum server memory (to less than the full amount)
    4. Database Settings Page
      1. Check "Compress Backup"
      2. Change Database locations
    5. Avoid changing Processors Page
    6. When making changes, consider "Use Script" button, which displays the T-SQL
  2. Configuration via SQL Server Configuration Manager
    1. SQL Server Services (e.g. SQL Server, SQL Server Browser, SQL Server Agent)
      1. View Service Properties
        1. Preferred interface for maximal functionality (e.g. Change logon account)
        2. Task manager, services applet are incomplete
      2. Manage Service
        1. Stop, Start, Restart a service
        2. Pause and resume a service
      3. Enabling a disabled service, requires using windows services to enable
        1. Task manager will not allow enabling (must be enabled via Services applet)
    2. SQL Server Network Configuration
      1. Shared Memory (enabled by default)
      2. TCP/IP (enabled by default)
      3. Named Pipes (older protocol, rarely used now)
  3. Maintenance
    1. Keep the system updated (patches, cummulative updates, service packs)
    2. Set-up Database mail (with SMTP) and SQL Server agent alerts
    3. Test database connectivity with a blank UDL file
      1. Create a blank file and change its extension to "UDL" (MS Data Link File)
      2. Opening the file will bring up a Data Link properties dialog
      3. Start on the Provider Tab and then move to the Connection Tab
      4. Type in DB Server, integrated security or SA/Password, database
      5. Click Test Connection
  • References
  1. Berry (2012) SQL Server 2012: Installation and Configuration, Pluralsight
  2. Gennick (2004) SQL Pocket Guide, O'Reilly, Sebastopol