WIXLIB

Applied SQLPresented by Eddy KleinjanTake advantage of SQL Server capabilities and use them in your DataFlex project.

SQLSQL Server Options

SQL Server OptionsSQL Server Versions 2000200520082008 R220122014SQL Server Editions (2014) ExpressWebStandardBusiness IntelligenceEnterpriseLocalDB

SQL Server ScaleFeature NameEnterpriseBusiness IntelligenceStandardWebExpressMaximum Compute Capacity Operating System maximumUsed by a Single Instance (SQL1Server Database Engine)Limited to lesser of 4 Socketsor 16 coresLimited to lesser of 4 Socketsor 16 coresLimited to lesser of 4 Socketsor 16 coresLimited to lesser of 1 Socket or4 coresMaximum Compute CapacityUsed by a Single Instance(Analysis Services, Reporting1Services)Operating system maximumOperating system maximumLimited to lesser of 4 Socketsor 16 coresLimited to lesser of 4 Socketsor 16 coresLimited to lesser of 1 Socket or4 coresMaximum memory utilized(SQL Server Database Engine)Operating system maximum64 GB64 GB64 GB1 GBMaximum memory utilized(Analysis Services)Operating system maximumOperating system maximum64 GBN/AN/AMaximum memory utilized(Reporting Services)Operating system maximumOperating system maximum64 GB64 GBN/A524 PB524 PB524 PB10 GBMaximum relational Database 524 PBsizeSource: http://msdn.micros oft.com/en-us/ libra ry/cc645993.aspx

SQL Server Nice to Know Multiple Databases on an Instance SQL Server Versions side by side Restore only on Same or Newer

Benefits from SQL Standardized Data FormatServer Side FilteringComplex Reports made easier and fasterShare DataOnline Backup

SQLConnectivity Kit What’s New

CK What’s New Updated Supported data types Microsoft SQL Server 2012 IBM DB2 10.1 Auto Reconnect Improved Restructure Logic

Data Types Support for new Data Types like Date, DateTime2, Time Supported on MS SQL, DB2 and ODBCDEFAULT MAP DF TO SQL TYPE SCHEMA

DEFAULT MAP DF TO SQL TYPE SCHEMA Mapping Schema’s DF ASCIIDF DATEDF DATETIMEDF TEXTDF 00CharDatetimeDatetimeTextBinary/image ax)MAP DF TO SQL TYPE CK5MAP DF TO SQL TYPE SQL2000MAP DF TO SQL TYPE SQL2005MAP DF TO SQL TYPE SQL2008MAP DF TO SQL TYPE SQL2012MAP DF TO SQL TYPE ax)CK6CharDateDatetime2Varchar(max)Varbinary(max)

CK Data Types: nchar & nvarchar Unicode Data Type Maps to Clients Code Page Data might get lost when writing backThe originaltext in SQLserverDataFlex whenno codepagemappingWhen writingback the datato SQL serverTradiçãoTradi?oTradi?o

Auto Reconnect Reconnect when connection is lostOpen and Find will reconnectSupported on MS SQL, DB2 and ODBCAUTO RECONNECT 1

SQLHow to

Create Database SQL Server Management Studio Use SQL defaults Mind collating

Convert Existing to SQL Database Builder converts data Standard vs Recnum Tables 18.1 can create clustered indexes Structure: DataFlex StudioLeading

SQL Tasks Generate Scripts Easy way to copy between versions Structure only, Dataonly, or Both See Advanced Optionsbutton

Attach to Existing SQL Setup Database Connection Connect to Tables fromStudio Structure: SQL is leading

Are you enjoying the view? Join data from different tables databasesInclude complex filtersLimit the number of columnsDatabase structure independency

Create a ViewCREATE VIEW [vwCustomerLast120Days] ASSELECTCustomer NumberFROMCustomerWHEREEXISTS ( SELECT Order NumberFROM OrderHeaWHERE OrderHea.Customer Number Customer.Customer NumberAND DATEDIFF(dd, Order Date, GETDATE()) 120) Benefits: Treat like a table, ReusableDownside: Distributed code

How to connect Connect to views Views are not tables Views can be updatable

SQL Side Filter Works in driver version 4 or higher DF FILE SQL FILTER DF FILE SQL FILTER ACTIVEEXISTS (SELECT Order Number FROM OrderHeaWHERE OrderHea.Customer Number Customer.Customer NumberAND DATEDIFF(dd, Order Date, GETDATE()) 120)

SQL Side Filter Use Data Dictionaries psSQLFilter pbUseDDSQLFilters pbApplyGlobalSQLFilters (defaults to FALSE!)

SQLSome SQL Tricks

SQL Tricks: Back-up a table-- Create backupSELECT *INTO CustomerBackupFROM Customer-- Empty tableTRUNCATE TABLE Customer-- Alternative: DELETE FROM Customer-- Copy backup data back into tableINSERT INTO CustomerSELECT * FROM CustomerBackup-- Drop the backup tableDROP TABLE CustomerBackup

SQL Tricks: UNION Concatenate result sets First result set defines names and data types

SQL Tricks: DISTINCT SELECT unique rows Cannot have aggregates; use GROUP BY forthat

SQL Trick: CASE Great to put in conditionals Use it anywhere; even in joins

SQL Trick: Four-Way Path Join Data from different serversRegister the external le]

SQLCreate some samples

2014 SQL Server Editions (2014) Express Web Standard Business Intelligence Enterprise LocalDB. SQL Server Scale Feature Name Enterprise Business Intelligence Standard Web Express Maximum Compute Capacity Used by a Single Instance (SQL Server Database Engine) 1 Op