Home • About • Contact
Welcome • Page Content • Site Map

Search
GH.com   GH.com dir
Wikipedia   Web

• Intro
• History
• User's Perspective of SQL Server
• Techie's Perspective of SQL Server
• Miscellany

Intro

Microsoft SQL Server is a RDBMS (Relational DataBase Management System) whose main purpose is to store and make data available to users.

SQL Server stores data in logical tables consisting of rows and columns. SQL Server can also connect with external data sources that are accessible via the OLE DB (Object Linking and Embedding DataBase), this includes sources such as spreadsheets, text documents, and XML documents.

SQL Server usually outputs data as individual values, record sets (tables) of values, or XML documents.

As with most RDBMSs, SQL (Structured Query Language) is the primary language used to communicate with the RDBMS. SQL Server's dialect of SQL is called T-SQL (Transact SQL). SQL is used to manipulate the data, define the structure of the data, and secure the data.

The importance of SQL in databases cannot be overemphasized.

History

• 1987 Sybase SQL Server was created for Unix systems.
• 1988 SQL Server for OS/2 made by Sybase, Microsoft, and Ashton-Tate.
• SQL Server 4.2. Released 1993-09-14.
• SQL Server 6. Code named "SQL95". 1995.
• SQL Server 6.5. Code named "Hydra". Released 1996-04-16.
• SQL Server 7. Code named "Sphinx". Released 1998-11-16.
• SQL Server 7 OLAP. Code named "Plato". 1999.
• SQL Server 8/2000 32 bit version. Code named "Shiloh". Released 2000-08-07.
  • SQL Server Enterprise Edition can supports groups of database servers to form terabyte-sized databases that can serve thousands of users at a time.
  • SQL Server Standard Edition can support a small to medium sized organization.
  • SQL Server Personal Edition can be used on the desktops and laptops of individuals. These databases can stand alone or can be synced via replication with larger databases.
  • SQL Server for Window CE can be used on portable devices that run the Windows CE operating system. These databases can stand alone or can be synced via replication with larger databases.
  • SQL Server Developer Edition is for developers making apps for SQL Server and SQL Server for Windows CE. It can be installed on a server or workstation. This version cannot be used for production.
  • SQL Server 2000 Desktop Engine is a version of SQL Server that third-party developers can incorporate into their apps.
• SQL Server 2000, 64 bit version. Code named "Liberty". Now competitive with Unix/Linux and Oracle. Released 2003-04-24.
• SQL Server 9/2005. Code named "Yukon". Released 2005-11-07. My favorite changes:
  • varchar(max), nvarchar(max), and xml datatypes.
  • INTERSECT & EXCEPT operands for SELECT statements.
  • PIVOT & UNPIVOT functions for SELECT statements.
  • SQL Server Management Studio

User's Perspective of SQL Server

From a user's perspective there are four basic services that SQL Server provides:

• On-Line services. The phrase "On-Line" is meant to signify that a user is using data live and in real time.
  • OLTP (On-Line Transaction Processing). OLTP processes implement transactions , sequences of operations which must complete in entirety or not at all. OLTP can also be said to cover non-transactional data access and structuring. Of the listed user services, OLTP is by far the biggest and most common.
  • OLAP (On-Line Analytical Processing). OLAP is the multidimensional presentation of data in multidimensional structures (eg data warehouses, data marts, and cubes), usually for the purpose of data mining. Just as OLTP uses SQL to access and define data, OLAP uses MDX (MultiDimensional Expressions) to access and define multidimensional data.
• Supplementary services:
  • English Query. Posing queries to a database using real world English that can be translated to SQL or MDX. English Query utilizes Full-Text Search where variations of the search words are used.
  • Replication . The copying, distribution, and synchronizing of data across different databases.

Techie's Perspective of SQL Server

Here is one way of looking at services, tools, and interfaces available through and for SQL Server:

• The data itself.
  • Physical architecture . The way data is stored in files in the operating system.
  • Logical architecture . Structuring the data logically as tables, rows, columns, etc.
• Processing the data.
  • Storage Engine . The application that handles how data is stored.
  • Relational Engine . The application that handles how data is accessed and related.
  • SQL Server Agent . An application that can handle scheduled tasks or reacts to events.
• Consuming the data.
  • DB APIs (Application Programming Interface) provide programmers with a consistent and flexible interface for code to access databases. The DB APIs can send T-SQL statements via an OLE DB provider or an ODBC driver. SQL Server supports the following DB APIs:
    • ADO (ActiveX Data Objects). The dominant API.
    • OLE DB (Object Linking and Embedding DataBase). More powerful than ADO but more difficult.
    • ODBC (Open DataBase Connectivity). This includes DB APIs built over ODBC such as RDO (Remote Data Objects) and DAO (Data Access Objects).
    • DB-Library for c . This was specifically for versions of SQL Server that predated SQL-92 but is still usable.
    • ESQL (Embedded SQL for c).
  • URLs (Uniform Resource Locator). URLs are strings used by Internet apps to access resources on the Internet or on an intranet. An URL can be used to send T-SQL or XPath statements via TCP/IP and HTTP.
  • English Query .
• Administering the data with the SQL-DMF (SQL Distributed Management Framework).
  • SQL-DMF Apps.
    • SQL Server Enterprise Manager . The major GUI interface for administering SQL Server.
    • Analysis Manager. The major GUI interface for administering Analysis Services . Data from OLTP systems are periodically summarized into fact and dimension tables which are stored in data warehouses and data marts. Analysis Services takes the data warehouses and data marts and makes multidimensional cubes which can be used for OLAP to determine trends and perspectives on the data.
    • COM objects, including those on ASP (Active Server Pages).
    • SQL Server tools, including wizards and GUI apps.
      • Query Analyzer . This beloved app provides quick and direct T-SQL access to SQL Server.
      • DTS (Data Transformation Services) . Extracts and transforms data from one data source to another. This can make tables, XML documents, OLE DB data sources, and data warehouses.
      • Backup and Restore Databases .
      • Replication .
      • Meta Data Services . Supports the MDC OIM (MDC Open Information Model) which provides a consistent format for data modeling, ie describes various database objects and relationships.
      • xsp (extended stored procedures). c and c++ apps and DLLs that can be called just like regular stored procedures.
      • SQL Profiler . Can be used on queries to trouble shoot or maximize performance.
  • SQL-DMF APIs.
    • SQL-NS (SQL Namespace). This API is usually an invisible API between the SQL-DMF Apps and the other SQL-DMF APIs.
    • SQL-DMO (SQL Distributed Management Objects). COM objects that encapsulate administrative functions for the Relational Database Engine. EG: This is heavily used by the SQL Server Enterprise Manager.
    • DSO (Decision Support Objects). COM objects that encapsulate administrative functions for Analysis Services.
    • WMI (Windows Management Instrumentation). Makes SQL Server a WMI Provider so that WMI apps can get info about SQL Server databases.

Miscellany

• For SQL Server 2005, here are the date formats accepted:
  • ISO 8601 works regardless of the SET DATEFORMAT or SET LANGUAGE settings.

    yyyy-MM-ddThh:mm:ss[.mmm]
    --EG:
    1996-04-15T14:30:20.5
    --For midnight use 00:00 instead of 24:00

  • Alphabetic date formats are human readable but the variants can confuse and 2 digit years should still be avoided:

    Apr[il] [15][,] 1996 
    Apr[il] 15[,] [19]96 
    Apr[il] 1996 [15]
    
    [15] Apr[il][,] 1996 
    15 Apr[il][,][19]96 
    15 [19]96 apr[il]
    [15] 1996 apr[il]
    
    1996 APR[IL] [15]
    1996 [15] APR[IL]

  • Numeric date formats can confuse. EG: 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting.

    [0]4/15/[19]96 -- (mdy)
    [0]4-15-[19]96 -- (mdy)
    [0]4.15.[19]96 -- (mdy)
    [04]/[19]96/15 -- (myd)
    
    15/[0]4/[19]96 -- (dmy)
    15/[19]96/[0]4 -- (dym)
    [19]96/15/[0]4 -- (ydm)
    [19]96/[04]/15 -- (ymd)

  • Time formats allowed.

    14:30
    14:30[:20:999]
    14:30[:20.9]
    4am
    4 PM
    [0]4[:30:20:500]AM
    --For midnight use 00:00 instead of 24:00

Page Modified: (Hand noted: 2007-10-13 14:12:18Z) (Auto noted: 2010-12-24 22:47:36Z)