How can I leave the table name in a create statement as a variable?
DECLARE @.tname varchar(32)
SET @.tname = 'ralph'
CREATE TABLE @.tname...
I need to create tables/temp tables for multiple ado.net users and I
hope to gain some performance improvements over dynamic sql.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You do this via dynamic SQL, like so:
DECLARE @.tname varchar(32)
SET @.tname = 'ralph'
DECLARE @.CMD varchar(1000)
set @.CMD = 'CREATE TABLE ' + rtrim(@.tname) + ' (Id int, code int)'
exec (@.cmd)
insert into ralph values (1, 1)
select * from ralph
drop table ralph
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"M D" <mardukes@.aol.com> wrote in message
news:ery$E95DFHA.3536@.TK2MSFTNGP15.phx.gbl...
> How can I leave the table name in a create statement as a variable?
> DECLARE @.tname varchar(32)
> SET @.tname = 'ralph'
> CREATE TABLE @.tname...
> I need to create tables/temp tables for multiple ado.net users and I
> hope to gain some performance improvements over dynamic sql.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Tuesday, March 27, 2012
Create a Table dynamically
Is it possible to create a table dynamically ? I would like to do
something like this:
DECLARE @.TableName varchar(100)
SET @.TableName = 'toto'
CREATE TABLE @.TableName
It returns a syntax error.
Thank you.jerome.bellan...@.lycos.com wrote:
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.
Why would you want to do that though? Good design practice generally
requires a schema that is static at runtime.
Creating tables dynamically might occassionally be useful during
development and you can use TSQL's EXEC statement for that. If you
attempt the same in transactional production environment however you
would pay a heavy price in terms of scalability, security and
manageability.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||try this...
declare @.tablename varchar(20)
select @.tablename = 'table'
EXEC ('CREATE TABLE ' + @.tablename + ' ( col1 varchar(20))')
Immy
<jerome.bellanger@.lycos.com> wrote in message
news:1141039414.594591.289750@.i39g2000cwa.googlegroups.com...
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.
>|||Thank you. It is perfect.|||Not like this.
Try this, instead:
DECLARE @.TableName varchar(100)
SET @.TableName = 'toto'
exec ('CREATE TABLE ' + @.TableName + ' (column1 int)' )
Cheers.
Deck
jerome.bellanger@.lycos.com wrote:
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.
something like this:
DECLARE @.TableName varchar(100)
SET @.TableName = 'toto'
CREATE TABLE @.TableName
It returns a syntax error.
Thank you.jerome.bellan...@.lycos.com wrote:
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.
Why would you want to do that though? Good design practice generally
requires a schema that is static at runtime.
Creating tables dynamically might occassionally be useful during
development and you can use TSQL's EXEC statement for that. If you
attempt the same in transactional production environment however you
would pay a heavy price in terms of scalability, security and
manageability.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||try this...
declare @.tablename varchar(20)
select @.tablename = 'table'
EXEC ('CREATE TABLE ' + @.tablename + ' ( col1 varchar(20))')
Immy
<jerome.bellanger@.lycos.com> wrote in message
news:1141039414.594591.289750@.i39g2000cwa.googlegroups.com...
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.
>|||Thank you. It is perfect.|||Not like this.
Try this, instead:
DECLARE @.TableName varchar(100)
SET @.TableName = 'toto'
exec ('CREATE TABLE ' + @.TableName + ' (column1 int)' )
Cheers.
Deck
jerome.bellanger@.lycos.com wrote:
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.
Saturday, February 25, 2012
Coverting varchar to numeric
I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
--
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
--
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com
Coverting varchar to numeric
I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.com
The CAST() function allows you to convert between data types. Books-on-line
has several examples.
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.com
The CAST() function allows you to convert between data types. Books-on-line
has several examples.
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com
Coverting varchar to numeric
I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com
Friday, February 24, 2012
coverting to date problem
SELECT DISTINCT CAST(YEAR(classdate) AS varchar(4)) + '/' + CAST(MONTH(classdate) AS varchar(2)) as ok ,{fn MONTH(dbo.classgiven.classdate)} as monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) order by 1", conNorthwind)
hello how do convert "CAST(YEAR(classdate) AS varchar(4)) + '/1/' + CAST(MONTH(classdate) AS varchar(2)) as ok" to a date in my sql statement?
so that it comes out as MM/DD/YYYY
thank you all
agian "OK" has to come out as a date..thanks
you can use
convert(varchar(20),classdate,101)
in you query, this will converts the date in the MM/dd/yyyy format.
|||It looks as thoughclassdate is a date type anyway so, rather than create unnecessary function calls in your SQL statement, simply return the date and format it in whatever control you are using to display the data.
Covering Index Question
we have a table with the following structure (not full but should give an
idea how it looks like)
CREATE TABLE [dbo].[JK_Product] (
[Creator] [varchar] (50) NOT NULL ,
[ID] [int] NOT NULL ,
[Product] [int] NOT NULL ,
[PartNum] [char] (32) NOT NULL ,
..
..
..
[CheckShipping] [char] (1) NULL
) ON [PRIMARY]
GO
We have these 3 indexes already on this table
CLUSTERED INDEX1
([Creator], [Product])
PRIMARY KEY NONCLUSTERED INDEX1
([Creator],[ID])
NONCLUSTERED INDEX2
([Creator], [Product], [PartNum])
ITW suggested me these 3 new indexes based on some workload i gave
NONCLUSTERED INDEX3
([ID])
NONCLUSTERED INDEX4
([PartNum])
NONCLUSTERED INDEX5
([Product], [Creator])
is it necessary to create INDEX3 and INDEX4 since we already have those
covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
use of the existing indexes effectively? what if we create a covering NCI
consisting of Creator, ID, Product, PartNum columns and drop other NCIs
TIAHi
You need not create Index 3 and 4 as Index 1 and 2 takes care of it.
Creating an Index depends on the query that you frequently use.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"para
a" wrote:
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer ma
ke
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||First, it is meaningless to talk about a clustered index covering a query. A
clustered indexes
covers all queries against the table.
So lets focus on the other indexes. If you don't have what you search for in
the first column of the
index, SQL Server cannot s
for that condition. It can scan the index, but
not s. That might be
why it suggested an index in the ID, PartNum and Product, Creator. In short,
without knowing your
workload, selectivity etc, we cannot say whether the recommendations from IT
W are good
recommendations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer ma
ke
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||Hi
As well , read this article
http://www.sql-server-performance.c...ing_indexes.asp
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer
> make
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||tibor thx for the reply. yeah, i know a clustered index covers all queries
against the table. i was more interested to find out if instead of having 3-
4
small NCIs to cover some X queries, wouldn't it be better to have one long
NCI in general (I haven't fixed the order of columns in it yet...and maybe
the order can be fixed on further analysis of %age of its use in those X
queries)?
"Tibor Karaszi" wrote:
> First, it is meaningless to talk about a clustered index covering a query.
A clustered indexes
> covers all queries against the table.
> So lets focus on the other indexes. If you don't have what you search for
in the first column of the
> index, SQL Server cannot s for that condition. It can scan the index, b
ut not s. That might be
> why it suggested an index in the ID, PartNum and Product, Creator. In shor
t, without knowing your
> workload, selectivity etc, we cannot say whether the recommendations from
ITW are good
> recommendations...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
>|||The ordering of the columns is one big issue. If you have a query with a whe
re clause in which you
only search for colA, then an index on (colB, colA) will not be searchable.
SQL Server can still
scan the index (assuming it covers the query), but a search is much better (
especially if the index
covers the query as no bookmark lookups are needed).
So, you could say that it is better to create a bunch of one-column indexes
and let SQL Server join
them as the query is processed (index intersection) so that the indexes toge
ther will cover the
query. Now you can search for whichever column you have ion the indexes and
you can always have a
s. OTOH, you will pay the price for the index intersections as the query
is processed (in the
cases then SQL Server joins the indexes in run-time).
So, we cannot say which is better. You can, but either analyzing the queries
etc. Or by running a
load test for your particular load and see which index alternative is pest f
or your load test.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
> tibor thx for the reply. yeah, i know a clustered index covers all queries
> against the table. i was more interested to find out if instead of having
3-4
> small NCIs to cover some X queries, wouldn't it be better to have one long
> NCI in general (I haven't fixed the order of columns in it yet...and maybe
> the order can be fixed on further analysis of %age of its use in those X
> queries)?
> "Tibor Karaszi" wrote:
>|||In addition you may consider the space requirements for the nonclustered
indexes. This can vary but can be high for a table with a large number of
rows and although not recommended - a large clustered index key (will be
dupped in each nc index).
HTH
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1CSKoZzFHA.2212@.TK2MSFTNGP15.phx.gbl...
> The ordering of the columns is one big issue. If you have a query with a
> where clause in which you only search for colA, then an index on (colB,
> colA) will not be searchable. SQL Server can still scan the index
> (assuming it covers the query), but a search is much better (especially if
> the index covers the query as no bookmark lookups are needed).
> So, you could say that it is better to create a bunch of one-column
> indexes and let SQL Server join them as the query is processed (index
> intersection) so that the indexes together will cover the query. Now you
> can search for whichever column you have ion the indexes and you can
> always have a s. OTOH, you will pay the price for the index
> intersections as the query is processed (in the cases then SQL Server
> joins the indexes in run-time).
> So, we cannot say which is better. You can, but either analyzing the
> queries etc. Or by running a load test for your particular load and see
> which index alternative is pest for your load test.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
>
idea how it looks like)
CREATE TABLE [dbo].[JK_Product] (
[Creator] [varchar] (50) NOT NULL ,
[ID] [int] NOT NULL ,
[Product] [int] NOT NULL ,
[PartNum] [char] (32) NOT NULL ,
..
..
..
[CheckShipping] [char] (1) NULL
) ON [PRIMARY]
GO
We have these 3 indexes already on this table
CLUSTERED INDEX1
([Creator], [Product])
PRIMARY KEY NONCLUSTERED INDEX1
([Creator],[ID])
NONCLUSTERED INDEX2
([Creator], [Product], [PartNum])
ITW suggested me these 3 new indexes based on some workload i gave
NONCLUSTERED INDEX3
([ID])
NONCLUSTERED INDEX4
([PartNum])
NONCLUSTERED INDEX5
([Product], [Creator])
is it necessary to create INDEX3 and INDEX4 since we already have those
covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
use of the existing indexes effectively? what if we create a covering NCI
consisting of Creator, ID, Product, PartNum columns and drop other NCIs
TIAHi
You need not create Index 3 and 4 as Index 1 and 2 takes care of it.
Creating an Index depends on the query that you frequently use.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"para
a" wrote:> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer ma
ke
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||First, it is meaningless to talk about a clustered index covering a query. A
clustered indexes
covers all queries against the table.
So lets focus on the other indexes. If you don't have what you search for in
the first column of the
index, SQL Server cannot s
for that condition. It can scan the index, butnot s
. That might bewhy it suggested an index in the ID, PartNum and Product, Creator. In short,
without knowing your
workload, selectivity etc, we cannot say whether the recommendations from IT
W are good
recommendations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"para
a" <paraa@.discussions.microsoft.com> wrote in messagenews:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer ma
ke
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||Hi
As well , read this article
http://www.sql-server-performance.c...ing_indexes.asp
"para
a" <paraa@.discussions.microsoft.com> wrote in messagenews:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer
> make
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||tibor thx for the reply. yeah, i know a clustered index covers all queries
against the table. i was more interested to find out if instead of having 3-
4
small NCIs to cover some X queries, wouldn't it be better to have one long
NCI in general (I haven't fixed the order of columns in it yet...and maybe
the order can be fixed on further analysis of %age of its use in those X
queries)?
"Tibor Karaszi" wrote:
> First, it is meaningless to talk about a clustered index covering a query.
A clustered indexes
> covers all queries against the table.
> So lets focus on the other indexes. If you don't have what you search for
in the first column of the
> index, SQL Server cannot s
for that condition. It can scan the index, but not s
. That might be> why it suggested an index in the ID, PartNum and Product, Creator. In shor
t, without knowing your
> workload, selectivity etc, we cannot say whether the recommendations from
ITW are good
> recommendations...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "para
a" <paraa@.discussions.microsoft.com> wrote in message> news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
>|||The ordering of the columns is one big issue. If you have a query with a whe
re clause in which you
only search for colA, then an index on (colB, colA) will not be searchable.
SQL Server can still
scan the index (assuming it covers the query), but a search is much better (
especially if the index
covers the query as no bookmark lookups are needed).
So, you could say that it is better to create a bunch of one-column indexes
and let SQL Server join
them as the query is processed (index intersection) so that the indexes toge
ther will cover the
query. Now you can search for whichever column you have ion the indexes and
you can always have a
s
. OTOH, you will pay the price for the index intersections as the query is processed (in the
cases then SQL Server joins the indexes in run-time).
So, we cannot say which is better. You can, but either analyzing the queries
etc. Or by running a
load test for your particular load and see which index alternative is pest f
or your load test.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"para
a" <paraa@.discussions.microsoft.com> wrote in messagenews:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
> tibor thx for the reply. yeah, i know a clustered index covers all queries
> against the table. i was more interested to find out if instead of having
3-4
> small NCIs to cover some X queries, wouldn't it be better to have one long
> NCI in general (I haven't fixed the order of columns in it yet...and maybe
> the order can be fixed on further analysis of %age of its use in those X
> queries)?
> "Tibor Karaszi" wrote:
>|||In addition you may consider the space requirements for the nonclustered
indexes. This can vary but can be high for a table with a large number of
rows and although not recommended - a large clustered index key (will be
dupped in each nc index).
HTH
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1CSKoZzFHA.2212@.TK2MSFTNGP15.phx.gbl...
> The ordering of the columns is one big issue. If you have a query with a
> where clause in which you only search for colA, then an index on (colB,
> colA) will not be searchable. SQL Server can still scan the index
> (assuming it covers the query), but a search is much better (especially if
> the index covers the query as no bookmark lookups are needed).
> So, you could say that it is better to create a bunch of one-column
> indexes and let SQL Server join them as the query is processed (index
> intersection) so that the indexes together will cover the query. Now you
> can search for whichever column you have ion the indexes and you can
> always have a s
. OTOH, you will pay the price for the index> intersections as the query is processed (in the cases then SQL Server
> joins the indexes in run-time).
> So, we cannot say which is better. You can, but either analyzing the
> queries etc. Or by running a load test for your particular load and see
> which index alternative is pest for your load test.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "para
a" <paraa@.discussions.microsoft.com> wrote in message> news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
>
Friday, February 17, 2012
Counting Results in a field in SQL 2000
I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?
Thanks
Quote:
Originally Posted by Alpenk
I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?
Thanks
Please send an example of your table or tables
|||select count(column_name) from table_nameIn this case you count all not null values in this column.
If you want to count distinct values in this colun do following:
Select count(distinct column_name) from table_name
Good Luck.
Counting NULL columns?
If I had a basic table with 3 VARCHAR fields; let's say A, B, C
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right
the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls
3
2
0
1
0
0
0
2
0
3
0
2
1
at least my query actually produces something useful
:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D
And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.
... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable
Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.
Thanks Rudy and blindman!
FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern
suppose i had a Three Stooges table with this row:
'curly','larry',null
you're saying it's okay to overwrite this with
'tom','dick','harry'
but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:
Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey
This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!
-PatP|||Only when you are in the dark...|||nice
might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right
the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls
3
2
0
1
0
0
0
2
0
3
0
2
1
at least my query actually produces something useful
:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D
And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.
... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:
select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable
Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.
Thanks Rudy and blindman!
FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern
suppose i had a Three Stooges table with this row:
'curly','larry',null
you're saying it's okay to overwrite this with
'tom','dick','harry'
but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:
Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey
This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!
-PatP|||Only when you are in the dark...|||nice
might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?
Subscribe to:
Posts (Atom)