Any way to create 5KB size of Database?

Hi
when we create new database that will take base structure and size of the
model database. it will be approx 1.43 MB as per version. now i want to
create database that size is 5kb any way to create database like that size,
why the reason is for CD technolgy we have less space.. can u suggest me?
Thanks
Sriram.Srikanth
SQL-Server Database Administrator,
Hi
The newly created DB gets it's sizing from Model DB. Model DB can not be
made smaller. With all the structures needed in the MDF and LDF files,
1.43mb is the smallest you can get.
Why ship the DB? Create the DB though T-SQL code on the client machine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>
|||What you could do is distribute the MDF file only, in a zipped version...
Then unzip it, and use sp_attach_single_file_db to load it up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>
|||You could also simply distribute the create database/object script. This
would insulate you from physical changes to the mdf file, which is a risk
with the other way I mentioned.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>

Any way to create 5KB size of Database?

Hi
when we create new database that will take base structure and size of the
model database. it will be approx 1.43 MB as per version. now i want to
create database that size is 5kb any way to create database like that size,
why the reason is for CD technolgy we have less space.. can u suggest me?
Thanks
--
Sriram.Srikanth
SQL-Server Database Administrator,Hi
The newly created DB gets it's sizing from Model DB. Model DB can not be
made smaller. With all the structures needed in the MDF and LDF files,
1.43mb is the smallest you can get.
Why ship the DB? Create the DB though T-SQL code on the client machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||What you could do is distribute the MDF file only, in a zipped version...
Then unzip it, and use sp_attach_single_file_db to load it up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||You could also simply distribute the create database/object script. This
would insulate you from physical changes to the mdf file, which is a risk
with the other way I mentioned.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>

Any way to create 5KB size of Database?

Hi
when we create new database that will take base structure and size of the
model database. it will be approx 1.43 MB as per version. now i want to
create database that size is 5kb any way to create database like that size,
why the reason is for CD technolgy we have less space.. can u suggest me?
Thanks
--
Sriram.Srikanth
SQL-Server Database Administrator,Hi
The newly created DB gets it's sizing from Model DB. Model DB can not be
made smaller. With all the structures needed in the MDF and LDF files,
1.43mb is the smallest you can get.
Why ship the DB? Create the DB though T-SQL code on the client machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||What you could do is distribute the MDF file only, in a zipped version...
Then unzip it, and use sp_attach_single_file_db to load it up..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||You could also simply distribute the create database/object script. This
would insulate you from physical changes to the mdf file, which is a risk
with the other way I mentioned.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>

Any tools to compare DB structure, update the field length ?

Any good suggestion '
I am willing to pay but can't afford too much .
Thanks in advanceTry QALite, thats a free one.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:%23Dy6UT6TFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Any good suggestion '
> I am willing to pay but can't afford too much .
> Thanks in advance
>|||Thanks.. Jens.
Where I can find it ? Could you be kind post the web site for me ?
Thanks a lot.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:%23Fd1jz6
TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Try QALite, thats a free one.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:%23Dy6UT6TFHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||Assuming I understand what you would like to do, try SQL Compare from Red Ga
te
(www.red-gate.com). Extremely fast and reasonably priced.
Thomas
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23Dy6UT6TFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Any good suggestion '
> I am willing to pay but can't afford too much .
> Thanks in advance
>

Any structure on Sql Server like WITH ... SELECT structure on DB2

Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.

Is there any basic structure on Sql Server like WITH ... SELECT
structure?

A Sample statement for WITH ... SELECT on DB2 like below
WITH
totals (code, amount)
AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code)
SELECT
code, SUM(amount)
FROM totals
GROUP BY code

......................

Note: 'creating temp table and using it' maybe a solution.
However i need to know the definition of the result set of Union
clause. I don't want to use this way.
CREATE TABLE #totals (codechar(10), amount dec(15))
GO
INSERT INTO #totals
SELECT code, SUM(amount) FROM trans1 GROUP BY code
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY code
GO
SELECT code, sum(amount) FROM #totals GROUP BY code
GO

Any help would be appreciated
Thanks in advance
MemduhIn SQL Server, you can use SELECT ... INTO to create a table using a SELECT
query as the source for schema and data. Untested example:

SELECT
code,
SUM(amount)
INTO #totals
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code

SELECT
code,
SUM(amount)
FROM #totals
GROUP BY code

You can also produce the result using a derived table instead of a temp
table:

SELECT
code,
SUM(amount)
FROM
(
SELECT
code,
SUM(amount) AS amount
FROM trans1
GROUP BY code
UNION ALL
SELECT
code,
SUM(amount)
FROM trans2
GROUP BY code
) AS totals
GROUP BY code

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Memduh Durmaz" <mdurmaz@.derece.com.tr> wrote in message
news:e0c9cfd5.0408291350.31b8585a@.posting.google.c om...
> Hi,
> I'm using DB2 UDB 7.2.
> Also I'm doing some tests on SQL Server 2000 for some statements to
> use efectively.
> I didn't find any solution on Sql Server about WITH ... SELECT
> structure of DB2.
> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code
> .....................
> Note: 'creating temp table and using it' maybe a solution.
> However i need to know the definition of the result set of Union
> clause. I don't want to use this way.
> CREATE TABLE #totals (code char(10), amount dec(15))
> GO
> INSERT INTO #totals
> SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code
> GO
> SELECT code, sum(amount) FROM #totals GROUP BY code
> GO
> Any help would be appreciated
> Thanks in advance
> Memduh|||On 29 Aug 2004 14:50:25 -0700, Memduh Durmaz wrote:

> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code

You can always use a derived table:

SELECT code, SUM(amount)
FROM (
SELECT code, SUM(amount) FROM trans1 GROUP BY CODE
UNION ALL
SELECT code, SUM(amount) FROM trans2 GROUP BY CODE
) AS TOTALS
GROUP BY CODE

(the "AS" in "AS TOTALS" is optional, and is often omitted.)|||SQL-Server 2005 will support Common Table Expressions (the WITH ...
SELECT stuff). If you don't want to wait a year, then you would have to
work around in SQL-Server 2000, or get hold of a beta-version.

HTH,
Gert-Jan

Memduh Durmaz wrote:
> Hi,
> I'm using DB2 UDB 7.2.
> Also I'm doing some tests on SQL Server 2000 for some statements to
> use efectively.
> I didn't find any solution on Sql Server about WITH ... SELECT
> structure of DB2.
> Is there any basic structure on Sql Server like WITH ... SELECT
> structure?
> A Sample statement for WITH ... SELECT on DB2 like below
> WITH
> totals (code, amount)
> AS (SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code)
> SELECT
> code, SUM(amount)
> FROM totals
> GROUP BY code
> .....................
> Note: 'creating temp table and using it' maybe a solution.
> However i need to know the definition of the result set of Union
> clause. I don't want to use this way.
> CREATE TABLE #totals (code char(10), amount dec(15))
> GO
> INSERT INTO #totals
> SELECT code, SUM(amount) FROM trans1 GROUP BY code
> UNION ALL
> SELECT code, SUM(amount) FROM trans2 GROUP BY code
> GO
> SELECT code, sum(amount) FROM #totals GROUP BY code
> GO
> Any help would be appreciated
> Thanks in advance
> Memduh

--
(Please reply only to the newsgroup)

Any help solidifying the following?

I am trying to create a table structure that would allow users to define custom formulas for computing 1 or more values for any given number of variables.

Table Structure

FORMULA
ID | Descr
-----
1 | Rectangle

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
------------
1 | Side A | X |
1 | Size B | Y |
1 | Height | Z |
1 | Area | A | X * Y
1 | Volume | V | A * V

FORMULA_VARIABLE_VALUE
fkID | Variable | Value
-------
1 | X | 10
1 | Y | 10
1 | Z | 2

In the above, notice on FORMULA_VARIABLE the field named expression. If this is NULL, this will be a parameter that must be specified by the user, else, this value from the expression must be evaluated using the variable values.

Notice the Volume expression uses the expression for the variable A. This is where the fun begins...

I have produced the following using the code below. If you could please look at it and let me know if there are any ways to make this more effective, efficient, and stable. That would be greatly appreciated.

The code is a first time run-through!

USE Northwind
GO

CREATE TABLE FORMULA
(
FormulaID int NOT NULL,
Descr char(30)
)
GO

CREATE TABLE FORMULA_VARIABLE
(
fkFormulaID int NOT NULL,
Dimension char(30),
Variable char(10),
Units char(5),
Expression char(255)
)
GO

CREATE TABLE FORMULA_VARIABLE_VALUE
(
fkFormulaID int,
Variable char(10),
Value float
)
GO

INSERT INTO FORMULA (FormulaID, Descr)
SELECT 1, 'Rectangular'
GO

INSERT INTO FORMULA_VARIABLE (fkFormulaID, Dimension, Variable, Units, Expression)
SELECT 1, 'Side 1', 'X', 'IN', NULL UNION ALL
SELECT 1, 'Side 2', 'Y', 'IN', NULL UNION ALL
SELECT 1, 'Height', 'Z', 'IN', NULL UNION ALL
SELECT 1, 'Area', 'A', 'SI', 'X * Y' UNION ALL
SELECT 1, 'Volume', 'V', 'I3', 'A * Z'GO
GO

INSERT INTO FORMULA_VARIABLE_VALUE (fkFormulaID, Variable, Value)
SELECT 1, 'X', 10 UNION ALL
SELECT 1, 'Y', 10 UNION ALL
SELECT 1, 'Z', 2
GO

CREATE PROCEDURE usp_BuildExpressions
@.iFormula int
AS

CREATE TABLE #TempFormulaResults
(
Dimension char(30),
Value float
)

DECLARE @.cDimension char(30), @.cOldExp varchar(255), @.cNewExp varchar(4000), @.cVariable char(5)

DECLARE OldExpCursor CURSOR FOR
SELECT Dimension, Expression FROM FORMULA_VARIABLE WHERE fkFormulaID = 1 AND Expression IS NOT NULL
OPEN OldExpCursor

FETCH NEXT FROM OldExpCursor INTO @.cDimension, @.cOldExp
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Iterate through expresions, build new cursor where dimension is not equal
-- Replace expression variable with expressions
DECLARE NewExpCursor CURSOR FOR
SELECT Variable, Expression FROM FORMULA_VARIABLE
WHERE fkFormulaID = @.iFormula AND Expression IS NOT NULL AND NOT (Dimension = @.cDimension)

OPEN NewExpCursor
FETCH NEXT FROM NewExpCursor INTO @.cVariable, @.cNewExp
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cOldExp = REPLACE(RTRIM(@.cOldExp), RTRIM(@.cVariable), RTRIM(@.cNewExp))
FETCH NEXT FROM NewExpCursor INTO @.cVariable, @.cNewExp
END
CLOSE NewExpCursor
DEALLOCATE NewExpCursor

-- Get the variable values, replace values in expression and calcluate result
DECLARE @.fValue float, @.cVarName char(5)
DECLARE ValueCursor CURSOR FOR
SELECT Variable, Value FROM FORMULA_VARIABLE_VALUE
WHERE fkFormulaID = @.iFormula

OPEN ValueCursor
FETCH NEXT FROM ValueCursor INTO @.cVarName, @.fValue
WHILE(@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cOldExp = REPLACE(@.cOldExp, RTRIM(@.cVarName), CONVERT(VARCHAR, @.fValue))
FETCH NEXT FROM ValueCursor INTO @.cVarName, @.fValue
END

DECLARE @.cSelect nvarchar(4000), @.param nvarchar(4000), @.Eval float

SET @.cSelect = 'SET @.fResult = ' + @.cOldExp
SET @.Param = '@.fResult float OUTPUT'

EXEC sp_executesql @.cSelect, @.Param, @.Eval OUT
INSERT INTO #TempFormulaResults (Dimension, Value) VALUES (@.cDimension, @.Eval)
FETCH NEXT FROM OldExpCursor INTO @.cDimension, @.cOldExp
CLOSE ValueCursor
DEALLOCATE ValueCursor
END

CLOSE OldExpCursor
DEALLOCATE OldExpCursor

SELECT * FROM #TempFormulaResults
DROP TABLE #TempFormulaResults
GO

EXEC usp_BuildExpressions 1

DROP TABLE FORMULA
GO
DROP TABLE FORMULA_VARIABLE
GO
DROP TABLE FORMULA_VARIABLE_VALUE
GO
DROP PROCEDURE usp_BuildExpressions

Any thoughts?

Mike BOn flaw I see, but I am not sure how to fix is if the "nested" variables are more then 2 deep. What if I used Volume from above in a different formula?

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
------------
1 | Side A | X |
1 | Size B | Y |
1 | Height | Z |
1 | Area | A | X * Y
1 | Volume | V | A * Z
1 | 1/2 Vol | v | V / 2

Now after executing the stored proc usp_BuildExpressions, the expression for 1/2 Vol would not work and end up looking like
A * 2 / 2.

It will stop short of replacing all the variables with the appropriated nested expressions.

Any ideas?

Mike B