Hello,
I am looking for a way to build an SQL database from an XML schema.1. Try XSLT
2. Read XML using XML Dom, then do a loop -thru|||Also, you may want to look at the SchemaGen option of the SQLXML XML
Bulkload facility. It can give you a first cut at generating the table
definitions.
Best regards
Michael
"Neo" <pravinsable@.gmail.com> wrote in message
news:1144871606.564411.290310@.u72g2000cwu.googlegroups.com...
> Hello,
> I am looking for a way to build an SQL database from an XML schema.
>sql
Showing posts with label build. Show all posts
Showing posts with label build. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
How to Create Index
If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized?
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.
The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized?
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.
The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
How to Create Index
If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > If I run query mostly on column expressions, like I have two columns in
> > table
> > one for date and other is for time, when i want to build index, then i
> > build
> > index on both columns, but will these separate index work when i write
> > query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be
> > utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
> > If I run query mostly on column expressions, like I have two columns in table
> > one for date and other is for time, when i want to build index, then i build
> > index on both columns, but will these separate index work when i write query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
>> The expression is not sargable because you are applying functions to the
>> column value. If your data contains the default values for the time and
>> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
>> WHERE
>> saleDate = '20050101' AND
>> saleTime = '11:30:00'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Imran" <Imran@.discussions.microsoft.com> wrote in message
>> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>> > If I run query mostly on column expressions, like I have two columns in
>> > table
>> > one for date and other is for time, when i want to build index, then i
>> > build
>> > index on both columns, but will these separate index work when i write
>> > query
>> > with this expression,
>> > where convert(varchar(10),saleDate,121) + ' ' +
>> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
>> > both columns have data type of datetime.
>> > My question is, will the separate indexes of both columns will be
>> > utilized'
>> > Or what is the best practice, when you are searching on expressions in
>> > queries? not on just simply column.
>> >
>> >
>> >
>>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) ='12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
> > The expression is not sargable because you are applying functions to the
> > column value. If your data contains the default values for the time and
> > date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> >
> > WHERE
> > saleDate = '20050101' AND
> > saleTime = '11:30:00'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Imran" <Imran@.discussions.microsoft.com> wrote in message
> > news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > > If I run query mostly on column expressions, like I have two columns in
> > > table
> > > one for date and other is for time, when i want to build index, then i
> > > build
> > > index on both columns, but will these separate index work when i write
> > > query
> > > with this expression,
> > > where convert(varchar(10),saleDate,121) + ' ' +
> > > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > > both columns have data type of datetime.
> > > My question is, will the separate indexes of both columns will be
> > > utilized'
> > > Or what is the best practice, when you are searching on expressions in
> > > queries? not on just simply column.
> > >
> > >
> > >
> >
> >
> >sql
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > If I run query mostly on column expressions, like I have two columns in
> > table
> > one for date and other is for time, when i want to build index, then i
> > build
> > index on both columns, but will these separate index work when i write
> > query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be
> > utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
> > If I run query mostly on column expressions, like I have two columns in table
> > one for date and other is for time, when i want to build index, then i build
> > index on both columns, but will these separate index work when i write query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
>> The expression is not sargable because you are applying functions to the
>> column value. If your data contains the default values for the time and
>> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
>> WHERE
>> saleDate = '20050101' AND
>> saleTime = '11:30:00'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Imran" <Imran@.discussions.microsoft.com> wrote in message
>> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>> > If I run query mostly on column expressions, like I have two columns in
>> > table
>> > one for date and other is for time, when i want to build index, then i
>> > build
>> > index on both columns, but will these separate index work when i write
>> > query
>> > with this expression,
>> > where convert(varchar(10),saleDate,121) + ' ' +
>> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
>> > both columns have data type of datetime.
>> > My question is, will the separate indexes of both columns will be
>> > utilized'
>> > Or what is the best practice, when you are searching on expressions in
>> > queries? not on just simply column.
>> >
>> >
>> >
>>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) ='12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
> > The expression is not sargable because you are applying functions to the
> > column value. If your data contains the default values for the time and
> > date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> >
> > WHERE
> > saleDate = '20050101' AND
> > saleTime = '11:30:00'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Imran" <Imran@.discussions.microsoft.com> wrote in message
> > news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > > If I run query mostly on column expressions, like I have two columns in
> > > table
> > > one for date and other is for time, when i want to build index, then i
> > > build
> > > index on both columns, but will these separate index work when i write
> > > query
> > > with this expression,
> > > where convert(varchar(10),saleDate,121) + ' ' +
> > > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > > both columns have data type of datetime.
> > > My question is, will the separate indexes of both columns will be
> > > utilized'
> > > Or what is the best practice, when you are searching on expressions in
> > > queries? not on just simply column.
> > >
> > >
> > >
> >
> >
> >sql
How to Create Index
If I run query mostly on column expressions, like I have two columns in tabl
e
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in ta
ble
> one for date and other is for time, when i want to build index, then i bui
ld
> index on both columns, but will these separate index work when i write que
ry
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized
'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
>|||You would have to create a computed column containing that expression and th
en index that computed
column. Or, create view with the expression and index the view (note that op
timizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
>
> "Dan Guzman" wrote:
>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
> "Dan Guzman" wrote:
>
e
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in ta
ble
> one for date and other is for time, when i want to build index, then i bui
ld
> index on both columns, but will these separate index work when i write que
ry
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized
'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
>|||You would have to create a computed column containing that expression and th
en index that computed
column. Or, create view with the expression and index the view (note that op
timizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
>
> "Dan Guzman" wrote:
>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
> "Dan Guzman" wrote:
>
Friday, March 9, 2012
How to create a database
Can some one tell me how to build a database
I'd like to build it using assembler
http://en.wikipedia.org/wiki/Assembler
How can you do that?This is from memory, so you may have to tweak it a little:
00100101010101010101010111011011010101101011001100 10101101...|||what's a database?|||Can some one tell me how to build a database
I'd like to build it using assembler
http://en.wikipedia.org/wiki/Assembler
How can you do that?
Come on, Brett. This sounds like a homework assignment to me. What have you come up with so far?|||All I did was come here asking for help...my assignment is due in 2 hours
Someone PLEASE HELP ME|||Can some one tell me how to build a database
I'd like to build it using assembler
I personally feel INTERCAL is much better suited for this task, or perhaps BEFUNGE.
http://en.wikipedia.org/wiki/Intercal
http://en.wikipedia.org/wiki/Befunge|||It's funny that the intercal language looks a lo like the code my peers write...maybe I should be doing it that way|||The best feature of INTERCAL is that it's so "forgiving". From the wikipedia article:
Anything the compiler can’t understand, which in a normal language would result in a compilation error, is just skipped. This “forgiving” feature makes finding bugs very difficult; it also introduces a unique system for adding program comments.|||well i got an f on the project...thanks guys|||Wow, in hex that's the highest grade possible! You should be proud.
-PatP|||well i got an f on the project...thanks guys
don't worry, you can always go work for Accenture. They are always looking for the kind of top talent you clearly have. Wear a tie for the interview.|||...and wear it very tight.|||...and polished leather shoes. not sneakers. they got a big building up the way here very spiffy dressers.
i have to say I am not a big fan of the one programmer that left here for there.|||Wow, in hex that's the highest grade possible! You should be proud.
-PatP
roflmso!
Pat.owned.yoo|||Wow, in hex that's the highest grade possible! You should be proud.
-PatP
I never thought of it that way...Thanks
I finally built a database though...using stone knives and bearskins.|||I finally built a database though...using stone knives and bearskins.
And what do you call it!? MS Access?|||I think Brett meant beerskins. Beerskins are these nifty aluminum cylinders that the merchants here wrap around a 350 ml serving of a slightly flavored, slightly alcoholic beverage the locals seem quite fond of (at least most of the time). The kids at the local universities and trade schools seem to build darn near anything out of the empty skins!
-PatP|||Is that what they mean when they say "We use every part of the beer"?|||Funny you should say that...my database design is fairly cylindrical
Only problem is it is mostly an export database...nothing rarley goes in...except for perhaps sql prf tuning (boiler maker)
There is also the new shot gun sql commands...|||OK I have two windows 98 machines, with MS Access 97 on one and flash installed on the other. I need to create a web based order inventory system that produces inventory and accounting reports and it needs to support 2000 concurrent users. I have a form with some input boxes I created in flash. Now how do I make my form web visible and how do I save the user input to Access?|||I never thought of it that way...Thanks
I finally built a database though...using stone knives and bearskins.
If you need an answer this sites great
if you need an answer in less than a couple of days you could be up a very unpleasent stream without a paddle.|||I find your signature repugnant, especially in the sql server forum. If you do not like the man's software, do not use it, but you can not hate someone for being a successful capitalist and if this is your gripe, go to Cuba or Venzuela or North Korea.|||in a minute some jerk is going to say "he stole windows". From who? Apple? Apple took the idea from Xerox.|||I'm sorry it offends you, however it's a signature not something meant to be taken seriously|||joking about murder is not funny. btw, how about checking your spelling on murdering.
Now tell me what your problem with Bill Gates is? Has he not given away enough billions for you to forgive him?
Tell me what languages and environments you program in?|||OK I have two windows 98 machines, with MS Access 97 on one and flash installed on the other. I need to create a web based order inventory system that produces inventory and accounting reports and it needs to support 2000 concurrent users. I have a form with some input boxes I created in flash. Now how do I make my form web visible and how do I save the user input to Access?Oh I like the easy ones!
First, buy up one of the local high schools. I'm sure that they're just going to waste anyway with a captain of industry like yourself in the area!
Do a print-screen of your form, and run it down to the office. Ask the secretary to trace your form onto a mimeograph master for you and run off at least a hundred thousand copies. She'll roll her eyes, but I'm sure that she's used to such requests.
Ask the AV guy to run phone banks into the school to support at least 2500 stations. He may need to get some more wire and handsets, so be sure to budget a few hundres dollars... He'll be able to do if for half that, but you want to keep him happy and encourage speed.
Set up both of your machines in the library. It should already have everything else you need, atlthough you might want to bring an extension cord just in case.
Bring in several thousand folks to answer the phone and fill out the mimeographed forms. You'll need some extras to haul the boxes of completed forms to the library.
Ask the librarian to key your data for you. They type fast, and are used to requests like this too.
Voila! You are in business!
-PatP|||You see this sort of sig every so often. Not a fan myself:
http://www.dbforums.com/showpost.php?p=6251757&postcount=6180
Rather out of proportion and not in the least funny either IMO.|||If you need an answer this sites great
if you need an answer in less than a couple of days you could be up a very unpleasent stream without a paddle.
Days? I expect an answer in minutes...at least that's what I'm used to
EDIT: perhaps I need a team...a sql team|||You see this sort of sig every so often. Not a fan myself:
http://www.dbforums.com/showpost.php?p=6251757&postcount=6180
Rather out of proportion and not in the least funny either IMO.
five dollars says the dude is a VBA\Access programmer.|||If you are going to have a sql team you will need a forum to discuss database issues (typically abbreviated as db) ... a dBforum|||five dollars says the dude is a VBA\Access programmer.
Lists his occupation as "VB Developer" :D
Must not like Bill's way of doing things, but is willing to make money from his developments.|||Virtually Bombastic?|||Lists his occupation as "VB Developer" :D
Must not like Bill's way of doing things, but is willing to make money from his developments.
exactly. it's one thing to take this kind of thing from the linux\apache\php kids but most of the time it is just some hypocrite|||Who says murder can't be funny?
Anything is fair game, but the catch is IT HAS TO BE FUNNY.
Personally, I do not find timoney's sig offensive. Just cliche' and dull.|||as they say, comedy = tragedy + time
so murder may become funny if you wait long enough.|||as they say, comedy = tragedy + time
Well that pretty much describes where I work|||although it negatively impacts my career, I rarely wait for time to pass to laugh at our tragedies.|||Days? I expect an answer in minutes...at least that's what I'm used to
EDIT: perhaps I need a team...a sql team
You might get a reply in a couple of minutes but it normally takes at least a few post going back and forth to get to an answer and for people new to databases you've also got to gauge their knowledge. mind you I'm on GMT so that might add to my round time as alot of people here are from the US and Canada.
joking about murder is not funny. btw, how about checking your spelling on murdering.
Now tell me what your problem with Bill Gates is? Has he not given away enough billions for you to forgive him?
Tell me what languages and environments you program in?
my problem with Microsoft is mainly with their business practises, they have a habit of doing anything to improve their sale including stealing as long as the fines are less than the profits. their product in the past have rarely been up to standard until about a years worth of patching. i have no problem with him making money but the end doesn't justify the means.
as for what i work in it C, C++, C#, DBase, Haskell, Java and Visual Basic
environment are Unix, Linux and Windows|||i have no problem with him making money but the end doesn't justify the means.I have no problem with you having a problem with MS & Bill, nor putting it in your sig if you really feel the need. But the end doesn't justify the means.|||what is stealing in programming and tell me what he stole. Windows? Apple took that from Xerox. So is Steve Jobs to be hung out as well? Did he steal SQL Server? That looked like a legitimate deal with Sybase. Show me a programmer who has not used someone elses code or took an idea and built further upon it. What exactly did he steal that was not already stolen?
their product in the past...
OK I remember how often Windows 95 used to crash, and yes there have been a lot of security patches and problems over the years but when you have 90+ % of the market, 90+ % of the black hat hackers are going to be spending 90+ percent of their energy screwing with your systems.
Ethical business practices? I do not think I have ever worked for any business that I would say has been totally clean in all of it's actions. Business men are not saints. just open up the business section of the paper. there will likely be a story about some nefarious scheme gone awry.
everyone is a scumbag, so stop being a player hater.|||I heard of this new technology that will make my databasse "go faster"
I think it's called Mauve...anyone else hear abouth this?|||It is well known that data that is not moving is virtually worthless to you and your business. The measure of how well your data is moving is the color shift of the data. This is very analogous to the color shift of astronomical bodies. For example, if you have a data publication system such as a data dictionary, or a reporting system, then your data will be red-shifted, as your data is moving away from you to your clients. If you have an OLTP system that is collecting data from all customers, then your data will be blue shifted, as it streaks towards you. Any DBA worth knowing monitors the color shift of the data in addition to disk I/O rates, and buffer cache hit ratios. If you have never done this before, do not worry, you can pick up an interferometer that can measure the color shift at just about any electronics store.
I would not trust a mauve system, as it seems to have data flowing in and out at great speeds. The wake created by the two opposing streams of data may cause great turbulence in the luminiferous aether, which will cause your network a great deal of headaches.
(And I thought I was not going to be able to use the word "interferometer" today"|||you are all wrong. you should be coding your database in OpenGL.|||what is stealing in programming and tell me what he stole. Windows? Apple took that from Xerox. So is Steve Jobs to be hung out as well? Did he steal SQL Server? That looked like a legitimate deal with Sybase. Show me a programmer who has not used someone elses code or took an idea and built further upon it. What exactly did he steal that was not already stolen?
their product in the past...
OK I remember how often Windows 95 used to crash, and yes there have been a lot of security patches and problems over the years but when you have 90+ % of the market, 90+ % of the black hat hackers are going to be spending 90+ percent of their energy screwing with your systems.
Ethical business practices? I do not think I have ever worked for any business that I would say has been totally clean in all of it's actions. Business men are not saints. just open up the business section of the paper. there will likely be a story about some nefarious scheme gone awry.
everyone is a scumbag, so stop being a player hater.
I will admit it's hard to find documented proof due to Microsoft's practise of Buying off the opposition and having all records relating to these cases sealed, however one of the best examples is
Digital Research V Microsoft and IBM - Digital Research took Microsoft and IBM to court over over allegations that MS-DOS and its PC-DOS variant were simply rip- offs of Digital Research's CP/M operating system. In the courtroom Gary Kildall sat down at an IBM PC supplied by IBM and, using a secret code, got it to pop up a Digital Research copyright notice.
But there are numerous cases were Microsoft are accused of deliberately changing their OS to make it incompatible with competitors software, See Sun Microsystems V Microsoft, Be V Microsoft, AT&T V Microsoft, Bristol Technologies V Microsoft, Borland V Microsoft, etc
and these case involve the direct use of other companies patented products which Microsoft are accused of either stealing or obtaining via deceit
Intertrust Technology V Microsoft, Eolas V Microsoft, Goldtouch V Microsoft, Caldera V Microsoft & IBM, Spyglass V Microsoft, Syn'x Relief V Microsoft, Stac V Microsoft, etc
From http://www.aaxnet.com/topics/msinc.html
Have a look it makes some interesting reading
and You've misstated your facts Apple sued Microsoft because Gates was under contract to them when he developed windows. meaning thats they owned the intellectual property rights. Apple agreed to settle out of Court when Microsoft offered to lease the code they'd used from them. Xerox then tried to sue apple over the idea of a GUI, it lost because the idea didn't belong to Xerox. Later Microsoft did it again however the contract apple had signed to lease it's code to Microsoft made no mention of version and Microsoft sucessfully argued that it applied to all version of the code not just to the one that it was created for.
I'd like to build it using assembler
http://en.wikipedia.org/wiki/Assembler
How can you do that?This is from memory, so you may have to tweak it a little:
00100101010101010101010111011011010101101011001100 10101101...|||what's a database?|||Can some one tell me how to build a database
I'd like to build it using assembler
http://en.wikipedia.org/wiki/Assembler
How can you do that?
Come on, Brett. This sounds like a homework assignment to me. What have you come up with so far?|||All I did was come here asking for help...my assignment is due in 2 hours
Someone PLEASE HELP ME|||Can some one tell me how to build a database
I'd like to build it using assembler
I personally feel INTERCAL is much better suited for this task, or perhaps BEFUNGE.
http://en.wikipedia.org/wiki/Intercal
http://en.wikipedia.org/wiki/Befunge|||It's funny that the intercal language looks a lo like the code my peers write...maybe I should be doing it that way|||The best feature of INTERCAL is that it's so "forgiving". From the wikipedia article:
Anything the compiler can’t understand, which in a normal language would result in a compilation error, is just skipped. This “forgiving” feature makes finding bugs very difficult; it also introduces a unique system for adding program comments.|||well i got an f on the project...thanks guys|||Wow, in hex that's the highest grade possible! You should be proud.
-PatP|||well i got an f on the project...thanks guys
don't worry, you can always go work for Accenture. They are always looking for the kind of top talent you clearly have. Wear a tie for the interview.|||...and wear it very tight.|||...and polished leather shoes. not sneakers. they got a big building up the way here very spiffy dressers.
i have to say I am not a big fan of the one programmer that left here for there.|||Wow, in hex that's the highest grade possible! You should be proud.
-PatP
roflmso!
Pat.owned.yoo|||Wow, in hex that's the highest grade possible! You should be proud.
-PatP
I never thought of it that way...Thanks
I finally built a database though...using stone knives and bearskins.|||I finally built a database though...using stone knives and bearskins.
And what do you call it!? MS Access?|||I think Brett meant beerskins. Beerskins are these nifty aluminum cylinders that the merchants here wrap around a 350 ml serving of a slightly flavored, slightly alcoholic beverage the locals seem quite fond of (at least most of the time). The kids at the local universities and trade schools seem to build darn near anything out of the empty skins!
-PatP|||Is that what they mean when they say "We use every part of the beer"?|||Funny you should say that...my database design is fairly cylindrical
Only problem is it is mostly an export database...nothing rarley goes in...except for perhaps sql prf tuning (boiler maker)
There is also the new shot gun sql commands...|||OK I have two windows 98 machines, with MS Access 97 on one and flash installed on the other. I need to create a web based order inventory system that produces inventory and accounting reports and it needs to support 2000 concurrent users. I have a form with some input boxes I created in flash. Now how do I make my form web visible and how do I save the user input to Access?|||I never thought of it that way...Thanks
I finally built a database though...using stone knives and bearskins.
If you need an answer this sites great
if you need an answer in less than a couple of days you could be up a very unpleasent stream without a paddle.|||I find your signature repugnant, especially in the sql server forum. If you do not like the man's software, do not use it, but you can not hate someone for being a successful capitalist and if this is your gripe, go to Cuba or Venzuela or North Korea.|||in a minute some jerk is going to say "he stole windows". From who? Apple? Apple took the idea from Xerox.|||I'm sorry it offends you, however it's a signature not something meant to be taken seriously|||joking about murder is not funny. btw, how about checking your spelling on murdering.
Now tell me what your problem with Bill Gates is? Has he not given away enough billions for you to forgive him?
Tell me what languages and environments you program in?|||OK I have two windows 98 machines, with MS Access 97 on one and flash installed on the other. I need to create a web based order inventory system that produces inventory and accounting reports and it needs to support 2000 concurrent users. I have a form with some input boxes I created in flash. Now how do I make my form web visible and how do I save the user input to Access?Oh I like the easy ones!
First, buy up one of the local high schools. I'm sure that they're just going to waste anyway with a captain of industry like yourself in the area!
Do a print-screen of your form, and run it down to the office. Ask the secretary to trace your form onto a mimeograph master for you and run off at least a hundred thousand copies. She'll roll her eyes, but I'm sure that she's used to such requests.
Ask the AV guy to run phone banks into the school to support at least 2500 stations. He may need to get some more wire and handsets, so be sure to budget a few hundres dollars... He'll be able to do if for half that, but you want to keep him happy and encourage speed.
Set up both of your machines in the library. It should already have everything else you need, atlthough you might want to bring an extension cord just in case.
Bring in several thousand folks to answer the phone and fill out the mimeographed forms. You'll need some extras to haul the boxes of completed forms to the library.
Ask the librarian to key your data for you. They type fast, and are used to requests like this too.
Voila! You are in business!
-PatP|||You see this sort of sig every so often. Not a fan myself:
http://www.dbforums.com/showpost.php?p=6251757&postcount=6180
Rather out of proportion and not in the least funny either IMO.|||If you need an answer this sites great
if you need an answer in less than a couple of days you could be up a very unpleasent stream without a paddle.
Days? I expect an answer in minutes...at least that's what I'm used to
EDIT: perhaps I need a team...a sql team|||You see this sort of sig every so often. Not a fan myself:
http://www.dbforums.com/showpost.php?p=6251757&postcount=6180
Rather out of proportion and not in the least funny either IMO.
five dollars says the dude is a VBA\Access programmer.|||If you are going to have a sql team you will need a forum to discuss database issues (typically abbreviated as db) ... a dBforum|||five dollars says the dude is a VBA\Access programmer.
Lists his occupation as "VB Developer" :D
Must not like Bill's way of doing things, but is willing to make money from his developments.|||Virtually Bombastic?|||Lists his occupation as "VB Developer" :D
Must not like Bill's way of doing things, but is willing to make money from his developments.
exactly. it's one thing to take this kind of thing from the linux\apache\php kids but most of the time it is just some hypocrite|||Who says murder can't be funny?
Anything is fair game, but the catch is IT HAS TO BE FUNNY.
Personally, I do not find timoney's sig offensive. Just cliche' and dull.|||as they say, comedy = tragedy + time
so murder may become funny if you wait long enough.|||as they say, comedy = tragedy + time
Well that pretty much describes where I work|||although it negatively impacts my career, I rarely wait for time to pass to laugh at our tragedies.|||Days? I expect an answer in minutes...at least that's what I'm used to
EDIT: perhaps I need a team...a sql team
You might get a reply in a couple of minutes but it normally takes at least a few post going back and forth to get to an answer and for people new to databases you've also got to gauge their knowledge. mind you I'm on GMT so that might add to my round time as alot of people here are from the US and Canada.
joking about murder is not funny. btw, how about checking your spelling on murdering.
Now tell me what your problem with Bill Gates is? Has he not given away enough billions for you to forgive him?
Tell me what languages and environments you program in?
my problem with Microsoft is mainly with their business practises, they have a habit of doing anything to improve their sale including stealing as long as the fines are less than the profits. their product in the past have rarely been up to standard until about a years worth of patching. i have no problem with him making money but the end doesn't justify the means.
as for what i work in it C, C++, C#, DBase, Haskell, Java and Visual Basic
environment are Unix, Linux and Windows|||i have no problem with him making money but the end doesn't justify the means.I have no problem with you having a problem with MS & Bill, nor putting it in your sig if you really feel the need. But the end doesn't justify the means.|||what is stealing in programming and tell me what he stole. Windows? Apple took that from Xerox. So is Steve Jobs to be hung out as well? Did he steal SQL Server? That looked like a legitimate deal with Sybase. Show me a programmer who has not used someone elses code or took an idea and built further upon it. What exactly did he steal that was not already stolen?
their product in the past...
OK I remember how often Windows 95 used to crash, and yes there have been a lot of security patches and problems over the years but when you have 90+ % of the market, 90+ % of the black hat hackers are going to be spending 90+ percent of their energy screwing with your systems.
Ethical business practices? I do not think I have ever worked for any business that I would say has been totally clean in all of it's actions. Business men are not saints. just open up the business section of the paper. there will likely be a story about some nefarious scheme gone awry.
everyone is a scumbag, so stop being a player hater.|||I heard of this new technology that will make my databasse "go faster"
I think it's called Mauve...anyone else hear abouth this?|||It is well known that data that is not moving is virtually worthless to you and your business. The measure of how well your data is moving is the color shift of the data. This is very analogous to the color shift of astronomical bodies. For example, if you have a data publication system such as a data dictionary, or a reporting system, then your data will be red-shifted, as your data is moving away from you to your clients. If you have an OLTP system that is collecting data from all customers, then your data will be blue shifted, as it streaks towards you. Any DBA worth knowing monitors the color shift of the data in addition to disk I/O rates, and buffer cache hit ratios. If you have never done this before, do not worry, you can pick up an interferometer that can measure the color shift at just about any electronics store.
I would not trust a mauve system, as it seems to have data flowing in and out at great speeds. The wake created by the two opposing streams of data may cause great turbulence in the luminiferous aether, which will cause your network a great deal of headaches.
(And I thought I was not going to be able to use the word "interferometer" today"|||you are all wrong. you should be coding your database in OpenGL.|||what is stealing in programming and tell me what he stole. Windows? Apple took that from Xerox. So is Steve Jobs to be hung out as well? Did he steal SQL Server? That looked like a legitimate deal with Sybase. Show me a programmer who has not used someone elses code or took an idea and built further upon it. What exactly did he steal that was not already stolen?
their product in the past...
OK I remember how often Windows 95 used to crash, and yes there have been a lot of security patches and problems over the years but when you have 90+ % of the market, 90+ % of the black hat hackers are going to be spending 90+ percent of their energy screwing with your systems.
Ethical business practices? I do not think I have ever worked for any business that I would say has been totally clean in all of it's actions. Business men are not saints. just open up the business section of the paper. there will likely be a story about some nefarious scheme gone awry.
everyone is a scumbag, so stop being a player hater.
I will admit it's hard to find documented proof due to Microsoft's practise of Buying off the opposition and having all records relating to these cases sealed, however one of the best examples is
Digital Research V Microsoft and IBM - Digital Research took Microsoft and IBM to court over over allegations that MS-DOS and its PC-DOS variant were simply rip- offs of Digital Research's CP/M operating system. In the courtroom Gary Kildall sat down at an IBM PC supplied by IBM and, using a secret code, got it to pop up a Digital Research copyright notice.
But there are numerous cases were Microsoft are accused of deliberately changing their OS to make it incompatible with competitors software, See Sun Microsystems V Microsoft, Be V Microsoft, AT&T V Microsoft, Bristol Technologies V Microsoft, Borland V Microsoft, etc
and these case involve the direct use of other companies patented products which Microsoft are accused of either stealing or obtaining via deceit
Intertrust Technology V Microsoft, Eolas V Microsoft, Goldtouch V Microsoft, Caldera V Microsoft & IBM, Spyglass V Microsoft, Syn'x Relief V Microsoft, Stac V Microsoft, etc
From http://www.aaxnet.com/topics/msinc.html
Have a look it makes some interesting reading
and You've misstated your facts Apple sued Microsoft because Gates was under contract to them when he developed windows. meaning thats they owned the intellectual property rights. Apple agreed to settle out of Court when Microsoft offered to lease the code they'd used from them. Xerox then tried to sue apple over the idea of a GUI, it lost because the idea didn't belong to Xerox. Later Microsoft did it again however the contract apple had signed to lease it's code to Microsoft made no mention of version and Microsoft sucessfully argued that it applied to all version of the code not just to the one that it was created for.
Wednesday, March 7, 2012
How to create 100 databases??
Hi,
Here is my question. I need to build 100 databases for a class of 100 students.Now what I need to know is what is the best way to do it. Like in DB2 what I did was I created a single database and gave a schema to each user to work with. But I am not sure how to go about this in SQL server.
I would really appreciate any kind of help in this regard.
Thanks,
Asha.You do not need to create 100 databases. You can simply create one database . Make 100 users and do not give them sa rights. Should work fine.|||After you create the database in sql server (enterprise manager) -
right click the database
go to all tasks
generate sql script|||I don't know...I'd think I'd have 1 created, dump it, and do:
DECLARE @.cmd varchar(4000), @.db_Name sysname, @.i int
SELECT @.db_name = 'Brett', @.i = 1
WHILE @.i < 101
BEGIN
-- Use to reset existing db's
--SELECT @.cmd = 'ALTER DATABASE ' + @.db_Name+'_'+CONVERT(varchar(3),@.i)+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
--EXEC(@.cmd)
SELECT @.cmd = 'RESTORE DATABASE ' + @.db_Name+'_'+CONVERT(varchar(3),@.i)
+ ' FROM DISK = '+ '''' + 'D:\Tax\BackUp\TaxReconDB.dmp' + ''''
+ ' WITH '
+ ' MOVE ' + '''' + 'TaxReconDB_Data' + ''''
+ ' TO ' + '''' + 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\'
+ @.db_Name+'_'+CONVERT(varchar(3),@.i)+'.MDF' + ''''
+ ', MOVE ' + '''' + 'TaxReconDB_Log' + ''''
+ ' TO ' + '''' + 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\'
+ @.db_Name+'_'+CONVERT(varchar(3),@.i)+'.LDF' + ''''
+ ', REPLACE'
SELECT @.cmd
EXEC(@.cmd)
SELECT @.cmd = 'ALTER DATABASE ' + @.db_Name+'_'+CONVERT(varchar(3),@.i) + ' SET READ_WRITE'
EXEC(@.cmd)
SELECT @.cmd
SELECT @.i = @.i + 1
END|||brett ..dont you think it would be a better idea to create 100 users and give them access to the same database .. just my thoughts|||Originally posted by Enigma
brett ..dont you think it would be a better idea to create 100 users and give them access to the same database .. just my thoughts
Think about it...
You have a class of 100 noob students...
"hey, where'd my data go? Must have been a bug..."
Can you imagine if you're pissed at someone, and their "final" is a mini system, and you placed some errant code in their sprocs to make them go booom?
"Ya mean I was suppose to keep a script or dump (what's that again?) of the database?"
F
I think (well then again, maybe I don't, no matter) he's going for isolationizm...
And I thought my method would be the laziest apporach...esp when resetting for the nect semster...
Hey I can't even keep proffesional (did I really call them that) developers from clobbering each other...
My Own Opinion (MOO)|||Originally posted by Enigma
There are 10 types of people in this world. Those that understand binary & those that don't.
Macka,
That you?
http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1622|||Didnt get that ...
Is somebody else using this line too ?|||Click on the link...
If you think dbforums is cool...
EDIT: You can't see his sig from there...but yeah it's the same...|||Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.
...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.
blindman|||Originally posted by blindman
Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.
...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.
blindman
You win!
It takes a lot of brains to truly ascend to the higher art form of lazy...|||If you do not want to give them dbcreator rights on the server, you should be able to write a simple script. Put the students' names in a table with an identity column, then loop through the table making database names like JSMITHnnn where nnn is the identity value. I am afraid if you give the students the ability to size their own files, you might get a few surprises.
do while not rs.eof
qry = "create database " & name & id & " on primary..."
conn.execute qry
rs.movenext
loop
Oh, and remember to set model to truncate logs on checkpoint first. Save yourself many questions that way.|||Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.
...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.
blindman
Blindman ...
I would have given you the certificate of being the laziest of all ... but i have a better Idea .. teach one student how to create a database and put him to the task of telling the rest of students .. no need for a teachers assistant too ...
However , I stick to my idea of a single database , would be easier to handle than 100 odd databases ...
moreover for 100 databases you would need 100 backup plans and all that ... just in case ...
Keep one database, 100 users , dont give them dbo rights .. keep the users seperate .. no cross rights.
Here is my question. I need to build 100 databases for a class of 100 students.Now what I need to know is what is the best way to do it. Like in DB2 what I did was I created a single database and gave a schema to each user to work with. But I am not sure how to go about this in SQL server.
I would really appreciate any kind of help in this regard.
Thanks,
Asha.You do not need to create 100 databases. You can simply create one database . Make 100 users and do not give them sa rights. Should work fine.|||After you create the database in sql server (enterprise manager) -
right click the database
go to all tasks
generate sql script|||I don't know...I'd think I'd have 1 created, dump it, and do:
DECLARE @.cmd varchar(4000), @.db_Name sysname, @.i int
SELECT @.db_name = 'Brett', @.i = 1
WHILE @.i < 101
BEGIN
-- Use to reset existing db's
--SELECT @.cmd = 'ALTER DATABASE ' + @.db_Name+'_'+CONVERT(varchar(3),@.i)+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
--EXEC(@.cmd)
SELECT @.cmd = 'RESTORE DATABASE ' + @.db_Name+'_'+CONVERT(varchar(3),@.i)
+ ' FROM DISK = '+ '''' + 'D:\Tax\BackUp\TaxReconDB.dmp' + ''''
+ ' WITH '
+ ' MOVE ' + '''' + 'TaxReconDB_Data' + ''''
+ ' TO ' + '''' + 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\'
+ @.db_Name+'_'+CONVERT(varchar(3),@.i)+'.MDF' + ''''
+ ', MOVE ' + '''' + 'TaxReconDB_Log' + ''''
+ ' TO ' + '''' + 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\'
+ @.db_Name+'_'+CONVERT(varchar(3),@.i)+'.LDF' + ''''
+ ', REPLACE'
SELECT @.cmd
EXEC(@.cmd)
SELECT @.cmd = 'ALTER DATABASE ' + @.db_Name+'_'+CONVERT(varchar(3),@.i) + ' SET READ_WRITE'
EXEC(@.cmd)
SELECT @.cmd
SELECT @.i = @.i + 1
END|||brett ..dont you think it would be a better idea to create 100 users and give them access to the same database .. just my thoughts|||Originally posted by Enigma
brett ..dont you think it would be a better idea to create 100 users and give them access to the same database .. just my thoughts
Think about it...
You have a class of 100 noob students...
"hey, where'd my data go? Must have been a bug..."
Can you imagine if you're pissed at someone, and their "final" is a mini system, and you placed some errant code in their sprocs to make them go booom?
"Ya mean I was suppose to keep a script or dump (what's that again?) of the database?"
F
I think (well then again, maybe I don't, no matter) he's going for isolationizm...
And I thought my method would be the laziest apporach...esp when resetting for the nect semster...
Hey I can't even keep proffesional (did I really call them that) developers from clobbering each other...
My Own Opinion (MOO)|||Originally posted by Enigma
There are 10 types of people in this world. Those that understand binary & those that don't.
Macka,
That you?
http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1622|||Didnt get that ...
Is somebody else using this line too ?|||Click on the link...
If you think dbforums is cool...
EDIT: You can't see his sig from there...but yeah it's the same...|||Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.
...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.
blindman|||Originally posted by blindman
Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.
...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.
blindman
You win!
It takes a lot of brains to truly ascend to the higher art form of lazy...|||If you do not want to give them dbcreator rights on the server, you should be able to write a simple script. Put the students' names in a table with an identity column, then loop through the table making database names like JSMITHnnn where nnn is the identity value. I am afraid if you give the students the ability to size their own files, you might get a few surprises.
do while not rs.eof
qry = "create database " & name & id & " on primary..."
conn.execute qry
rs.movenext
loop
Oh, and remember to set model to truncate logs on checkpoint first. Save yourself many questions that way.|||Lazy is good, brett. I'd be even lazier. I'd put the script to create the database objects on a shared resource, and then make each student create their own database, log into it, and run the script. It can't hurt them to learn how to create a database and run a ddl script.
...and while they were doing it I'd go to the faculty lounge and have a cup of coffee and have my teacher's assistant answer their questions. That's just how lazy I would be.
blindman
Blindman ...
I would have given you the certificate of being the laziest of all ... but i have a better Idea .. teach one student how to create a database and put him to the task of telling the rest of students .. no need for a teachers assistant too ...
However , I stick to my idea of a single database , would be easier to handle than 100 odd databases ...
moreover for 100 databases you would need 100 backup plans and all that ... just in case ...
Keep one database, 100 users , dont give them dbo rights .. keep the users seperate .. no cross rights.
Friday, February 24, 2012
how to correctly finding memory usage.
i have a box running very slow. it has 16 gb of ram, running
a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
a win2003 R2 enterprise x64 with SP2.
during the non-busy hours, Task Manager shows 4-50 KB ram available. what
is the right way to free up the memory? is there a way to see what are the
resources not released? (like top or ps -ef |grep....)
tried the neat Activity Monitor, nothing shows where the 15 gb are used.
thanks.You need to ensure that the max memory option of sql server is set to leave
memory for the OS and any other apps that may run on the server. With 16GB
total I would recommend setting it to 14GB and see how that goes. You can
set this via sp_configure or SSMS.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FD5F89D0-9942-4BB1-A08C-D87516983F85@.microsoft.com...
>i have a box running very slow. it has 16 gb of ram, running
> a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
> a win2003 R2 enterprise x64 with SP2.
> during the non-busy hours, Task Manager shows 4-50 KB ram available.
> what
> is the right way to free up the memory? is there a way to see what are
> the
> resources not released? (like top or ps -ef |grep....)
> tried the neat Activity Monitor, nothing shows where the 15 gb are used.
> thanks.|||setting the max memory is great.
how will i know what will be occupying the 14 GB?|||That 14GB will be used by the buffer pool of the instance. For the
distribution of the buffer pool buffers, I find DBCC MEMORYSTATUS handy.
Linchi
"light_wt" wrote:
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>|||wow...
thank you so much Linchi. that command is so cool.
i've g* and played with it a bit. it pulls very useful info.
just wondering here, if i want to pin point to see if certain BI things
(such as: cubes, proc, partition, aggregation, and etc) is the casue of the
memory issue. will there be a way to zoom in/ narrow down the issue?
afterall, the box is a sql05 for data mining...
Thanks.|||The memory will all be used by SQL Servers buffer pool. To see how that is
broken down have a look at the sys.dm_os_buffer_descriptors DMV in
BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:790A960F-9625-447C-A09E-A1818101F1FD@.microsoft.com...
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>
a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
a win2003 R2 enterprise x64 with SP2.
during the non-busy hours, Task Manager shows 4-50 KB ram available. what
is the right way to free up the memory? is there a way to see what are the
resources not released? (like top or ps -ef |grep....)
tried the neat Activity Monitor, nothing shows where the 15 gb are used.
thanks.You need to ensure that the max memory option of sql server is set to leave
memory for the OS and any other apps that may run on the server. With 16GB
total I would recommend setting it to 14GB and see how that goes. You can
set this via sp_configure or SSMS.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FD5F89D0-9942-4BB1-A08C-D87516983F85@.microsoft.com...
>i have a box running very slow. it has 16 gb of ram, running
> a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
> a win2003 R2 enterprise x64 with SP2.
> during the non-busy hours, Task Manager shows 4-50 KB ram available.
> what
> is the right way to free up the memory? is there a way to see what are
> the
> resources not released? (like top or ps -ef |grep....)
> tried the neat Activity Monitor, nothing shows where the 15 gb are used.
> thanks.|||setting the max memory is great.
how will i know what will be occupying the 14 GB?|||That 14GB will be used by the buffer pool of the instance. For the
distribution of the buffer pool buffers, I find DBCC MEMORYSTATUS handy.
Linchi
"light_wt" wrote:
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>|||wow...
thank you so much Linchi. that command is so cool.
i've g* and played with it a bit. it pulls very useful info.
just wondering here, if i want to pin point to see if certain BI things
(such as: cubes, proc, partition, aggregation, and etc) is the casue of the
memory issue. will there be a way to zoom in/ narrow down the issue?
afterall, the box is a sql05 for data mining...
Thanks.|||The memory will all be used by SQL Servers buffer pool. To see how that is
broken down have a look at the sys.dm_os_buffer_descriptors DMV in
BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:790A960F-9625-447C-A09E-A1818101F1FD@.microsoft.com...
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>
Subscribe to:
Posts (Atom)