|
What software is
used ?
Where does my application reside
?
What tools do
I need to use ?
Should I not be using Oracle for scalability
?
What database scripting languages can
I use with SQL Server ?
What
software is used ?
Microsoft's
SQL server version 7 service pack 1 is employed. See Microsoft's
SQL
site for revision information.
Where does my application reside
?
The
SQL service is a shared one allowing only databases to be
sited on the database server. ODBC & OLEDB calls can
be made to your database on the shared server. This provides
a 24 X 7 scalable service that can take workplace databases
onto the web as scaleable applications. Your application
remains within your allocated web space
What
tools do I need ?
SQL server
ships with client tools which allow data transformation
services. Wizards enable upsizing of legacy data such as
Microsoft Access databases. Once up-sized Query analyser
and Enterprise manager allow manipulation of data and structures
natively.
Other tools
such as Microsoft Interdev can be enabled to hook directly
into the power of your SQL database.
Should
I not be using Oracle for scalability ?
Industry
is currently watching Microsoft SQL server and Oracle for
NT slug it out. Rather than replicate arguments for both
sides we suggest you read up on the battle yourself. Suffice
to say, if the products were not competing then the arguments
would not exist!
Problem
Connecting to SQL Server Via Enterprise Manager?
Some people
experience problems using Microsoft's SQL Server client
tools such as the Enterprise Manager to connect to their
SQL Server database. The error that will most commonly occur
is:
Unable
to Connect To Server (reason: [DB-Library] Unable to Connect:
SQL Server is unavailable or does not exist. Specified
SQL Server not found). Register anyway?
There are
several problems that may cause this. The first is to make
sure that you are using the correct IP address (we suggest
using the IP address to register the server) and have the
correct login name and password. If you are unsure of any
of the above, please email techmail@bt.com.
to confirm the information.
If the
above is correct, the problem may exist in your installation
of Microsoft's client utilities. A major issue is that the
utilities install with the communication protocol of Named
Pipes selected as the default protocol. This must be changed
to TCP/IP. To do this:
- Go to
your Menu Bar and select the SQL Client Configuration
Manager from the SQL Server program group.
- Select
the Net Library tab.
- Select
TCP/IP as the Default Network
- Click
on the Done button.
What
Database scripting languages can I use with SQL Server?
We support
Allaire's Cold Fusion, Microsoft's Active Server Pages and
Microsoft's Internet Database Connector scripting languages.
There are differences between Microsoft SQL Server and Microsoft
Access query syntax:
| Access |
SQL
Server |
| ORDER
BY in queries |
ORDER
BY in views not supported |
| DISTINCTROW |
DISTINCT |
| String
concatenation with "&" |
String
concatenation with "+" |
|
Supported Clauses/Operators:
SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
WITH OWNERACCESS
Not Supported:
COMPUTE, FOR BROWSE, OPTION
|
Supported Clauses/Operators:
SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
COMPUTE
FOR BROWSE
OPTION
Not Supported:
WITH OWNERACCESS
|
|
Aggregate functions:
AVG
COUNT(column)
COUNT(*)
MIN
MAX
FIRST
LAST
STDEV, STDEVP
SUM
VAR, VARP
|
Aggregate functions:
AVG([ALL | DISTINCT] expression)
COUNT([ALL | DISTINCT] expression)
COUNT(*)
GROUPING (column_name)
MAX(expression)
MIN(expression)
STDEV, STDEVP
SUM([ALL | DISTINCT] expression)
VAR, VARP
Not supported:
FIRST, LAST
|
TRANSFORM
(SELECT statement)
PIVOT |
WITH
ROLLUP, WITH CUBE on SELECT statements |
|
MAKE TABLE, ALTER TABLE
Supported clauses:
CONSTRAINT
ADD COLUMN
DROP COLUMN
DROP INDEX
Also,
Stand-alone statement:
DROP INDEX
|
CREATE TABLE, ALTER TABLE
Supported clauses:
CONSTRAINT
ADD COLUMN
DROP COLUMN
Stand-alone statement:
DROP INDEX
|
What
is the maximum size of database I can have?
SQL server
can accomodate a database of any size. With support for
a potential 32 processors and 64 GB of memory the bottlenecks
will lie within the web enabling technology and network.
Should
I use Access or SQL ?
That Depends.
Access is cheap. It is easy to create tables and modify
them as needed. But in a production environment Access has
some major drawbacks:
a) It is
not designed to handle many concurrent writes into the database.
b) It is hard to modify an Access database without taking
it offline.
c) Locking doesn't work as well as in SQL Server
d) Using SQL Server constraints and transactions makes it
easier to protect the database from logical corruption.
My
wildcard search that worked with Access fails with SQL ?
This is
normally because you can use * and ? as wildcards with Access
while you should use % and _ respectively when using a SQL
Server. For instance:
SELECT
PhoneNumber WHERE NAME LIKE 'John*'
should
be replaced with:
SELECT PhoneNumber WHERE NAME LIKE 'John%'
when using
SQL Server. The % and _ also work with Access, so you may
just as well always use them.
Why
should I store NULL in a text field rather than " ".
Because
storing that one space in the text fields will result in
SQL Server allocating a complete page (2 k) for the field,
wheras NULL will require no extra storage.
|
