Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement = 'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
--
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>sql

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
Simon
Only with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan
|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon
|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegro ups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>

info about sysprocesses

Dear all,
I want to know all the possible values for the status field bring up for
sysprocesses table. Values such 'running' or 'sleeping' seems very
evident but there is one so-called 'DEF-WK...' or something like that which
I haven't idea.
In this occasion I am not be able to find it inside the BOL
Does anyone know how do I figure out such values?
Thanks in advance,
EnricHi
Look at the code from the system SP sp_who2
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:C9B83081-19A4-4A33-AD4C-F75AE450B394@.microsoft.com...
> Dear all,
> I want to know all the possible values for the status field bring up for
> sysprocesses table. Values such 'running' or 'sleeping' seems very
> evident but there is one so-called 'DEF-WK...' or something like that
> which
> I haven't idea.
> In this occasion I am not be able to find it inside the BOL
> Does anyone know how do I figure out such values?
> Thanks in advance,
> Enric

Infinity problem

Hi,

I have a table with some database fields and some calculated values. Sometimes it happens that I divide by 0 or null. As a result I get 'Infinity' in my textbox, is it possible to get rid of this 'message'?

greetz

Im not sure what the return value of that message is .... but if its a string containing the word "Infinity" you could try something like this:

Your field that sometimes returns infinity is: CalculatedField
IIf(CalculatedField = "Infinity", "Write your expression when true", CalculatedField)

That expression is used for a new calculated field and that field you can use in a textbox

|||

That could idd be a solution, but isn't there any way to use formatting. I don't like changing the value of my textbox.

greetz

|||

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code). Call that custom code function inside of performing the division directly in the expression.

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

-- Robert

Infinity

I have a calculation to determine percentage. When the two values that are
evaluated are the same the report displays Infinity as the answer rather than
100%. Anyone have any ideas how to have it read 100%?Please show your code which does the calculation
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jvjones" <jvjones@.discussions.microsoft.com> wrote in message
news:9E50B2E2-0B94-4F3A-B65C-224950FAB597@.microsoft.com...
>I have a calculation to determine percentage. When the two values that are
> evaluated are the same the report displays Infinity as the answer rather
> than
> 100%. Anyone have any ideas how to have it read 100%?

inerting/updating a collection of data values into SQL Server db all at once.

I'd like to insert/update a collection of data values from VS2005 (C#) into sql server in one insert/update statement. For instance, I'd like to insert all values from a checkboxlist that are checked without having to perform an insert statement for each value. What's the best way to go about this?

thx.

Assuming that you have the list as a joined string array, you could something like this with the following function I once wrote:

CREATE FUNCTION dbo.Split

(

@.String VARCHAR(200),

@.Delimiter VARCHAR(5)

)

RETURNS @.SplittedValues TABLE

(

OccurenceId SMALLINT IDENTITY(1,1),

SplitValue VARCHAR(200)

)

AS

BEGIN

DECLARE @.SplitLength INT

WHILE LEN(@.String) > 0

BEGIN

SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN

LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)

INSERT INTO @.SplittedValues

SELECT SUBSTRING(@.String,1,@.SplitLength)

SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)

END

RETURN

END

So this would evaluate in your case to:

Set @.ListOfIDs = '1, 2, 3, 4, 5'

INSERT INTO SomeTable
(Column)
Select SplitValue From dbo.Split(@.ListOfIDs,',')

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I thought that SQL Server 2005 allowed the insertion of VS 2005 DataTables etc. no?|||

MarilynJ wrote:

I'd like to insert/update a collection of data values from VS2005 (C#) into sql server in one insert/update statement. For instance, I'd like to insert all values from a checkboxlist that are checked without having to perform an insert statement for each value. What's the best way to go about this?

thx.

fill the dataset from data from sql server

update on the front end. vs2005 is using twoway binding

so there's not much work to be done

then

call the tableadapter update method

to commit chages to the database as a batch.

this is known as batch update

|||And remember that i you are working completly disconnected from the database that you have to specify your own Commands to update / insert / delete the data. Otherwise you could use the commandbuilder which will get you the appropiate commands if you read the schema from the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.desql

Indirect Configuration with ConfigType 'Indirect SQL Server' fails

Hi,

I have a package that uses a Configuration of type SQL Server where the property values are held.

This runs successfully using this direct configurations.

When I use an Indirect configuration using an environment variable to point to this SQL Server configuration type the package won't even validate.

The Indirect Configuration is:

[EHC-SQLD-01.].[SSISConfigsDEV];[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

which follows the standard of : db connections, config table, filter

This works by the way on my client but on the dev server the error is:

Error: The connection "[EHC-SQLD-01].SSISConfigsDEV" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I've tried every combination for the env variable using quotes, full computer name etc (I thought it was the hyphens in the name), but can't seem to get it to work.

I've also tried as both user and system env variables but made no difference (which one should we use for SSIS anyway as BOL doesn't state this ?)

Appreciate any help. I'm trying to deploy this on the Dev server for testing.

Thanks

P R W.

P R W,

I have never used the Indirect method for a SQL Server based configuration; but I use a similar approach that involves an env variable and the direct method:

1. Set the SQL server based configuration using the direct method using a connection manager called, Let's say 'Configuration'

2. Create an Env variable based configuration to set the connection string of 'Configuration' connection manager and place it at the very top of the configuration organizer, so it happens before the SQL server based one.

Make sure you create the Env variable wit the proper connection string for ‘Configuration’; and that you close and re-open BIDS so you can see the Env variable in thedropdow list for step 2.

I started using this method a while before knowing about indirect configurations and it has worked fine; maybe that is why I have not looked into the indirect ones.

|||

I'm not sure why you would want to do that.

If you deploy the package to different environments e.g. from dev to producution, you must still go into the package to change the Env variable to amend the connection string as eg. the package will now sit on a different server.

Also, you env variable sits first so the configs used will actually be the configs that run last i.e. the SQL Server based one, so what's the point of having two configs that do the same thing ?

The point of indirect configs is that you don't have to open the package. All you do is amend the Environment variable defined on the OS. This should make package deployment easier and more secure.

The actual problem lies in the fact that the Package does not recognise the Indirect config for the environment variable. Although I have successfully set this up on my client, it is not working for the Dev server.

|||

P R W wrote:

I'm not sure why you would want to do that.

If you deploy the package to different environments e.g. from dev to producution, you must still go into the package to change the Env variable to amend the connection string as eg. the package will now sit on a different server.

I guess I was not clear enough. You DO NOT have to open the package and change the Env Variable name; you have to make sure all servers you want to deploy the solution to have the same env variable (same name); then you need to make sure the connection string in the Env variable is right. That's exactly how the same way the Indirect configurations works; you reference a Env variable and then you just change its value, right?

P R W wrote:

Also, you env variable sits first so the configs used will actually be the configs that run last i.e. the SQL Server based one, so what's the point of having two configs that do the same thing ?

Ok let's try again. First, I don't have 2 configurations doing the same thing. Let me give you an scenario: My package has about 10 component/properties that need to be configured at run time; so I create a SQL Server configuration table with all those entries and define same number of SQL server configurations on every of those properties using the direct method. Since I am using the direct method I am asked to provide connection information to get access to the configuration table; is at that point where I use the 'configuration' connection manager. So far, I have create my 10 configurations based on a SQL Server table; to get access to that configuration table I am using 'Configuration' connection manager; the problem with that is that the connection string of 'configuration' needs to be changed at run time depending on which server/Environment is the package being executed. It is at this point when I create an Env. variable to set the connection string of 'configuration' and place it at the very top. That way on each run SSIS will go to the Env variable; will take the connection string, set it to 'configuration' and then all other sql server based configurations will use the right connection to get access to the table.

Notice that an Env variable with the same name needs to exists on every server your are deploying the solution to. But the same constraint exists using the indirect method. I can tell you more; using the indirect method you are constraint to use only Env variable; with this approach you could use alternatively an XML configuration file; that comes handy when you hit the wall when some weird IT policies that prevent you using Env variables

If you don't believe just give it a try; it will take just a couple of minutes to run a test.

P R W wrote:

The point of indirect configs is that you don't have to open the package. All you do is amend the Environment variable defined on the OS. This should make package deployment easier and more secure.

That is exactly what I am doing; just change a value in a Env variable.

P R W wrote:

The actual problem lies in the fact that the Package does not recognise the Indirect config for the environment variable. Although I have successfully set this up on my client, it is not working for the Dev server.

That is why I started my first post saying that I have not used the indirect method; and that I was offering an alternative approach that has worked for me.

|||

I still don't seem to follow what you are saying, as I'm confused by the terminology you're using.

If I have followed it correctly (and I must admit I had to read it several times through - maybe cos its a Fri and everyone else has gone home )., this is the scenario I have:

This is what I have set in Package Configs (this is my direct SQL Server Config):

Configuration Name : Configuration1

Configuration Type: SQL Server

Configuration String : EHC-SQLD-01.SSISConfigsDEV;[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

(The table [SSIS Configurations DEV] holds all the necessary properties and values).

So you are now saying, create another Package Configuration using Config Type of Environment Variable. ?

Type in a name - any name (lets say 'EnvVarTest') and set the Property of that variable to the Connection string of Configuration1 ?

If I've read that correct, when setting up the Env Variable on the OS, the Env variable name is X and Value 'EnvVarTest'. ?

I'm not sure that is what you are saying though, because you would still need to edit the package when deploying from one machine to another ?

Regards,

P R W.

|||

P R W wrote:

I still don't seem to follow what you are saying, as I'm confused by the terminology you're using.

If I have followed it correctly (and I must admit I had to read it several times through - maybe cos its a Fri and everyone else has gone home )., this is the scenario I have:

This is what I have set in Package Configs (this is my direct SQL Server Config):

Configuration Name : Configuration1

Configuration Type: SQL Server

Configuration String : EHC-SQLD-01.SSISConfigsDEV;[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

(The table [SSIS Configurations DEV] holds all the necessary properties and values).

So you are now saying, create another Package Configuration using Config Type of Environment Variable. ?

Type in a name - any name (lets say 'EnvVarTest') and set the Property of that variable to the Connection string of Configuration1 ?

If I've read that correct, when setting up the Env Variable on the OS, the Env variable name is X and Value 'EnvVarTest'. ?

I'm not sure that is what you are saying though, because you would still need to edit the package when deploying from one machine to another ?

Regards,

P R W.

Sorry if it sounded too confussing.

See if this clarifies it:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

Otherwise, I will give up

|||

OK, now I followed that. That was useful and solved the problem by using Direct config rather than Indirect Configuration.

I wasn't setting up the Environment Variable value correctly on the OS.

Appreciate the time you've taken to help.

(You really weren't thinking of giving up though were you ).

Still unsure as to why the Indirect Configuration failed to validate when it worked correctly on local machine. Tried all sorts of combinations for the Configuration. Anyway that's for another day.

Thanks.

P R W.

|||

PRW,

Glad you found it helpful. I also took the time to research about the indirect configurations, and I think I understood how the work, thanks to a blog post I found. Basically, the Env variable has to contain the SSIS connection manager to be used, the configuration table name, and the configuration filter. Saying that, it looks like you have to create an Env. variable for every property you want to override at run time; which make me wonder if using plain Env variable based configuration would not give the same results in a more simpler way.

The workaround I described in my blog requires only one Env variable only; then all configuration values are stored in the table.

Here is the link, in case you want to look into that other post hat talks about SQL Server Indirect configurations:

http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx

|||

You don't have to have an Env variable for every property required to by dynamic.

Basically it works in a similar way as you have used Direct configs eg. you setup a SQL Server Config type with table containing all properties required to be dynamic and their values. Then you set this up as Indirect config in Package configs. Then setup an env variable on the OS, that makes a connection to the SQL Server Config type Package config with value:

Servername.DBname;Tablename;Filtervalue;

so as you see very similar to the methodology you have used.

I have actually used it successfully as I said in my local environment and it works well. You don't need to reopen the package and all configs can be set in any of the other config types such as SQL, XML.

|||

P R W wrote:

You don't have to have an Env variable for every property required to by dynamic.

Basically it works in a similar way as you have used Direct configs eg. you setup a SQL Server Config type with table containing all properties required to be dynamic and their values. Then you set this up as Indirect config in Package configs. Then setup an env variable on the OS, that makes a connection to the SQL Server Config type Package config with value:

Servername.DBname;Tablename;Filtervalue;

so as you see very similar to the methodology you have used.

I have actually used it successfully as I said in my local environment and it works well. You don't need to reopen the package and all configs can be set in any of the other config types such as SQL, XML.

I think I am missing something here. If the Environment variable requires 'FilterValue' as part of its value; how can you set multiple values/properties at run time using only one Env variable? Can you provided a list of values for the 'Filtervalue' part?

|||

The Filter Value you provide in the OS Env variable, is used to determine the value held in the column 'ConfigurationFilter' of the SQL Server table that has been defined as the SSIS Configuration.

This table also has cols of :

ConfiguredValue - the actual property/variable value etc.

Package Path - the actual property/variable etc.

Configured Value Type.

The ConfigurationFilter is just a value I believe that determines what SSIS Configuration that the values in the table belong too. eg. I would have a table as below containing my SSIS Configs for a package named 'pkgLRD CED Import'

pkgLRD CED Import Void \Package.Variables[User::VarUnencryptionArguments].Properties[Value] String
pkgLRD CED Import C:Test\ \Package.Variables[User::VarSourceConnection].Properties[Value] String
pkgLRD CED Import C:\pkgLRD CED Import.chk \Package.Properties[CheckpointFileName] String

It made sense to me to use the Package name (since this ideally should be unique) though you could actually use any value. Thus you could hold all the Config values for all Package configs in one central table. The Filter value (in this case package name) therefore determines which configs the package should pick up.

So the OS Env variable would look like:

Servername.Dbname;Configtablename;ConfigFilter(i.e.packagename);

This worked for me although I must admit I haven't tested it for multiple packages yet, only one package.

The problem is as you have sort of stated that you would need one OS Env Variable for every package (though not every property).

This could possibly become a bit of a mess.

You could just have one OS Env variable and not define the Config Filter (haven't tested this though), but then I believe you would have to have a separate table for each package. This maybe a requirement or maybe not.

This is why I actually prefer your solution using the Direct method that you have since explained. It allows me to have one single OS env variable on each Server, and one central table for all SSIS package configs.

Regards,

P R W.

BTW I'm still learning SSIS (5 months in) so don't consider myself any sort of expert.

Indexing in SQL 2000

Hello Reader

Why I am not seeing the row values as per the index set on the table?
It appears in random manner.

I think it should appear ascending as per the index set for one of the Column in Ascending.

Please guide.

*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...73eeefbc3df9e58
*****************************************On Sat, 06 Nov 2004 22:44:24 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:

>Hello Reader
>Why I am not seeing the row values as per the index set on the table?
>It appears in random manner.
>I think it should appear ascending as per the index set for one of the Column in Ascending.

Hi Surya,

The rows will only appear in a specific order if you explicitly request
that order with an ORDER BY clause. Without that, SQL Server is free to
choose any order (and the optimizer will try to choose the order that can
be gotten the quickest).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Dear Hugo,

Thanks

I am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.

I am able to identify the best candidate for the indexing.

Below is the details I want to understand:

Area
ZIP
City
County
District
State/Province
Country

Now I want the data retrival optimization through Index. (you can suggest another idea, also)

Entities Area,..., Country have independent tables.
Example:

Area_Table
AreaID (PK)
Area
They have relationship- one to many- if you go from Country to Area.

There is one more table:

Location_Table (PK)
LocationID
AreaID
ZIPID
CityID
CountyID
DistrictID
State/ProvinceID
CountryID
(Location_ID is further related to the Address of the contact.)

GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.
& simultaniously Location_Table is also being inserted with the details.

Following is the situation of being queried these tables:

(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)

(2) Contacts have to retrived on the basis of Area, ZIP, ....County. (Necessary Groupings are required )

Example:
If Contacts are queried State Wise then the Display should be

State1
District1
County1
City1
ZIP1
Area1
Area2
ZIP2
City2

County2
District2

Please Guide.

SuryaPrakash

*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...sql-server/5074
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...b0a1ca5be7a0133
*****************************************