table hosed: February 2012

Friday, February 24, 2012

Is primar key/foreign key evil in SQL Server?

I worked with Oracle/Sybase before, and I used PK/FK quite often, I had PK
for every table.
Since I started working in SQL Server, I saw serveral big applications (over
2000 concurrent users) don't use PK at all, they just use clustered unique
index and there's no foreign keys either, the integrities are enforced by
triggers.
Can anybody shed some light on me?It seems you've been to a lot of really bad places. :)
I've seen some pretty lousy models myself - that's database models (not
super models). They were in production and had all the problems one would
expect (and more). But I'm pretty sure the ones I've seen would be just as
bad on any other platform (if the same people were involved in designing
them).
ML
http://milambda.blogspot.com/|||Where is Joe?
This should be your favorite topic.
"Matthew" wrote:

> I worked with Oracle/Sybase before, and I used PK/FK quite often, I had PK
> for every table.
> Since I started working in SQL Server, I saw serveral big applications (ov
er
> 2000 concurrent users) don't use PK at all, they just use clustered unique
> index and there's no foreign keys either, the integrities are enforced by
> triggers.
> Can anybody shed some light on me?|||> Since I started working in SQL Server, I saw serveral big applications
> (over
> 2000 concurrent users) don't use PK at all, they just use clustered unique
> index and there's no foreign keys either, the integrities are enforced by
> triggers.
I've seen this too. Fortunately, bad design is not restricted to a single
platform. Your experiences are indicative of you confronting environments
with bad designers, not bad database platforms. This could easily have been
Oracle, DB2, Access, dBase IV, mySQL, etc.
A|||If the same developers have created an Oracle database, then I would expect
to see the same poor implementation there as well. This type of design is
typical when the database is implemented by application developers. They
consider referential integrity constraints to be too rigid and blame them
for making their applications blow up. What they are probably doing is
inserting invalid data into the system and then re-coding it later in some
batch process or at the reporting level.
"Matthew" <Matthew@.discussions.microsoft.com> wrote in message
news:FEF63455-FF98-4AA2-B2B6-C8C0D0FC82F9@.microsoft.com...
>I worked with Oracle/Sybase before, and I used PK/FK quite often, I had PK
> for every table.
> Since I started working in SQL Server, I saw serveral big applications
> (over
> 2000 concurrent users) don't use PK at all, they just use clustered unique
> index and there's no foreign keys either, the integrities are enforced by
> triggers.
> Can anybody shed some light on me?|||Yes, Aaron,
You're absolutely right. Now I know it's the problem from the designers.
I have seen three big SQL Server systems having the same problem, so I
thought it's database specific.
"Aaron Bertrand [SQL Server MVP]" wrote:

> I've seen this too. Fortunately, bad design is not restricted to a single
> platform. Your experiences are indicative of you confronting environments
> with bad designers, not bad database platforms. This could easily have be
en
> Oracle, DB2, Access, dBase IV, mySQL, etc.
> A
>
>|||One day, a law will be passed mandating that only certified database
engineers (preferably Microsoft certified) be allowed to implement a
production database. Until then, we must tolerate these shade tree DBAs.
I'll bet their butt cleavage shows when they bend over to reboot the server.
;-P
"Matthew" <Matthew@.discussions.microsoft.com> wrote in message
news:321963C4-6FE0-4D64-B183-4A75F236B16C@.microsoft.com...
> Yes, Aaron,
> You're absolutely right. Now I know it's the problem from the designers.
> I have seen three big SQL Server systems having the same problem, so I
> thought it's database specific.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Yeah, thanks a lot for THAT mental image.
"JT" <someone@.microsoft.com> wrote in message
news:u%23zfILSTGHA.5440@.TK2MSFTNGP10.phx.gbl...
> One day, a law will be passed mandating that only certified database
> engineers (preferably Microsoft certified) be allowed to implement a
> production database. Until then, we must tolerate these shade tree DBAs.
> I'll bet their butt cleavage shows when they bend over to reboot the
> server. ;-P|||While no longer a developer, I was one and I designed and implements
many a production database. I am proud to say, that for the most part,
if the database server could do it, I tried to make use of it and that
included maintaining referential integrity. Two reasons: 1) The DBMS
was a tried and true method for maintaining referential integrity and
2) why would I want to write a bunch of validation code when all I need
to do is setup the proper PKs, FKs and relationships, and then trap an
error message should bad data attempt to get in. I subscribe to the
theory that never letting bad data in is always better than trying to
figure how to deal with it once it got in. Not all developers are
neanderthal database designers. Its the ones who are that give the rest
of us a bad name.|||> Not all developers are
> neanderthal database designers.
I never said that. Though I do believe there is substantial value in
separating the tasks, if and when you can.
A

Is possible?

I have an vb4 application (i know it's old, but it's not my fault), and i
need to change the used database (sybase) to sql (sql 2000). They are using
VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
database with this .vbx i can't log in.Hi
I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
logins with profiler so you can see how it is trying to connect. I would
assume that you have the configured/changed the servername and also any
other setting that are require such as a DSN.
John
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
>I have an vb4 application (i know it's old, but it's not my fault), and i
> need to change the used database (sybase) to sql (sql 2000). They are
> using
> VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> database with this .vbx i can't log in.|||This vbx receive a Server, BD, User and Pass it's supposed to return a value
different from 0 when log in, but it's returning a 0.
In advance, thank you for your help.
"John Bell" wrote:
> Hi
> I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
> logins with profiler so you can see how it is trying to connect. I would
> assume that you have the configured/changed the servername and also any
> other setting that are require such as a DSN.
> John
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
> >I have an vb4 application (i know it's old, but it's not my fault), and i
> > need to change the used database (sybase) to sql (sql 2000). They are
> > using
> > VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> > database with this .vbx i can't log in.
>
>|||VBSQL.VBX is soo old. It uses the old DbLibrary API... Anyhow, does the SQL Server allow SQL Server
logins? Can you login from QA on the same machine using the same credentials?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...
> This vbx receive a Server, BD, User and Pass it's supposed to return a value
> different from 0 when log in, but it's returning a 0.
> In advance, thank you for your help.
> "John Bell" wrote:
>> Hi
>> I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
>> logins with profiler so you can see how it is trying to connect. I would
>> assume that you have the configured/changed the servername and also any
>> other setting that are require such as a DSN.
>> John
>> "Logan" <Logan@.discussions.microsoft.com> wrote in message
>> news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
>> >I have an vb4 application (i know it's old, but it's not my fault), and i
>> > need to change the used database (sybase) to sql (sql 2000). They are
>> > using
>> > VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
>> > database with this .vbx i can't log in.
>>|||Yes, using the credentials, i can log in the QA.
"Tibor Karaszi" wrote:
> VBSQL.VBX is soo old. It uses the old DbLibrary API... Anyhow, does the SQL Server allow SQL Server
> logins? Can you login from QA on the same machine using the same credentials?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...
> > This vbx receive a Server, BD, User and Pass it's supposed to return a value
> > different from 0 when log in, but it's returning a 0.
> >
> > In advance, thank you for your help.
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
> >> logins with profiler so you can see how it is trying to connect. I would
> >> assume that you have the configured/changed the servername and also any
> >> other setting that are require such as a DSN.
> >>
> >> John
> >>
> >> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> >> news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
> >> >I have an vb4 application (i know it's old, but it's not my fault), and i
> >> > need to change the used database (sybase) to sql (sql 2000). They are
> >> > using
> >> > VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> >> > database with this .vbx i can't log in.
> >>
> >>
> >>
>|||It is not a named SQL Server instance, I hope?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:38232FBF-885E-41DF-9B34-BFC5C87C3C46@.microsoft.com...
> Yes, using the credentials, i can log in the QA.
> "Tibor Karaszi" wrote:
>> VBSQL.VBX is soo old. It uses the old DbLibrary API... Anyhow, does the SQL Server allow SQL
>> Server
>> logins? Can you login from QA on the same machine using the same credentials?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Logan" <Logan@.discussions.microsoft.com> wrote in message
>> news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...
>> > This vbx receive a Server, BD, User and Pass it's supposed to return a value
>> > different from 0 when log in, but it's returning a 0.
>> >
>> > In advance, thank you for your help.
>> >
>> > "John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
>> >> logins with profiler so you can see how it is trying to connect. I would
>> >> assume that you have the configured/changed the servername and also any
>> >> other setting that are require such as a DSN.
>> >>
>> >> John
>> >>
>> >> "Logan" <Logan@.discussions.microsoft.com> wrote in message
>> >> news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
>> >> >I have an vb4 application (i know it's old, but it's not my fault), and i
>> >> > need to change the used database (sybase) to sql (sql 2000). They are
>> >> > using
>> >> > VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
>> >> > database with this .vbx i can't log in.
>> >>
>> >>
>> >>
>>|||No, i'm using the IP
"Tibor Karaszi" wrote:
> It is not a named SQL Server instance, I hope?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:38232FBF-885E-41DF-9B34-BFC5C87C3C46@.microsoft.com...
> > Yes, using the credentials, i can log in the QA.
> >
> > "Tibor Karaszi" wrote:
> >
> >> VBSQL.VBX is soo old. It uses the old DbLibrary API... Anyhow, does the SQL Server allow SQL
> >> Server
> >> logins? Can you login from QA on the same machine using the same credentials?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> >> news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...
> >> > This vbx receive a Server, BD, User and Pass it's supposed to return a value
> >> > different from 0 when log in, but it's returning a 0.
> >> >
> >> > In advance, thank you for your help.
> >> >
> >> > "John Bell" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
> >> >> logins with profiler so you can see how it is trying to connect. I would
> >> >> assume that you have the configured/changed the servername and also any
> >> >> other setting that are require such as a DSN.
> >> >>
> >> >> John
> >> >>
> >> >> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> >> >> news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
> >> >> >I have an vb4 application (i know it's old, but it's not my fault), and i
> >> >> > need to change the used database (sybase) to sql (sql 2000). They are
> >> >> > using
> >> >> > VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> >> >> > database with this .vbx i can't log in.
> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Hi
Does this mean that profiler says the login is successful? In which case the
issue is not with SQL Server!
John
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...
> This vbx receive a Server, BD, User and Pass it's supposed to return a
> value
> different from 0 when log in, but it's returning a 0.
> In advance, thank you for your help.
> "John Bell" wrote:
>> Hi
>> I am not familiar with sybase and this vbx! But with SQL 2000 you audit
>> the
>> logins with profiler so you can see how it is trying to connect. I would
>> assume that you have the configured/changed the servername and also any
>> other setting that are require such as a DSN.
>> John
>> "Logan" <Logan@.discussions.microsoft.com> wrote in message
>> news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
>> >I have an vb4 application (i know it's old, but it's not my fault), and
>> >i
>> > need to change the used database (sybase) to sql (sql 2000). They are
>> > using
>> > VBSQL.VBX to access the sybase database, it's possible to use the sql
>> > 2000
>> > database with this .vbx i can't log in.
>>|||Thank you for your help
"Logan" wrote:
> I have an vb4 application (i know it's old, but it's not my fault), and i
> need to change the used database (sybase) to sql (sql 2000). They are using
> VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> database with this .vbx i can't log in.

Is possible?

Is possible to import registered SQL Servers to another Enterprise Manager

Hi,
Is it possible to export registered SQL Server information
and import it to the Enterprise Manager on the other
server?
ThanksHi Alan,
Go to Enterprise Manager>Tools Menu>Options
Clear checkbox for Read/Store user...
Register required servers. Now these registrations can be shared.
From other computer in your network, goto:
Enterprise Manager>Tools Menu>Options
Choose "Read from remote" and enter the name of computer that you registered
servers in step above.
The settings must appear in your Enterprise Manager.
Amin
"Alan" <SQL@.discussions.microsoft.com> wrote in message
news:1ee101c49ce0$f66a5e70$3a01280a@.phx.gbl...
> Hi,
> Is it possible to export registered SQL Server information
> and import it to the Enterprise Manager on the other
> server?
> Thanks|||Hi Amin,
Thanks. But I need to copy remote registries and store it
locally. I tried exporting from the registry
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X\SQL Server
Group], but only groups show up, not individual sql
server. So there're some other information stored
somewhere needed but I couldn't identify them.
Thanks
Alan
>--Original Message--
>Hi Alan,
>Go to Enterprise Manager>Tools Menu>Options
>Clear checkbox for Read/Store user...
>Register required servers. Now these registrations can be
shared.
>From other computer in your network, goto:
>Enterprise Manager>Tools Menu>Options
>Choose "Read from remote" and enter the name of computer
that you registered
>servers in step above.
>The settings must appear in your Enterprise Manager.
>Amin
>
>"Alan" <SQL@.discussions.microsoft.com> wrote in message
>news:1ee101c49ce0$f66a5e70$3a01280a@.phx.gbl...
>> Hi,
>> Is it possible to export registered SQL Server
information
>> and import it to the Enterprise Manager on the other
>> server?
>> Thanks
>
>
>.
>|||Alan,
I exported:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X
Deleted servers and imported that file. Everything was ok when I opened
EM(individual servers appeared again).
Is it what you need to do?
By the way, have a look to this address:
HKEY_USERS\S-1-5-21-1482476501-842925246-854245398-500\Software\Microsoft\Mi
crosoft SQL Server\80\Tools\SQLEW\Registered Servers X\SQL Server Group
Amin
<anonymous@.discussions.microsoft.com> wrote in message
news:0f0f01c49f37$92cf66e0$7d02280a@.phx.gbl...
> Hi Amin,
> Thanks. But I need to copy remote registries and store it
> locally. I tried exporting from the registry
> [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered Servers X\SQL Server
> Group], but only groups show up, not individual sql
> server. So there're some other information stored
> somewhere needed but I couldn't identify them.
> Thanks
> Alan
> >--Original Message--
> >Hi Alan,
> >Go to Enterprise Manager>Tools Menu>Options
> >Clear checkbox for Read/Store user...
> >Register required servers. Now these registrations can be
> shared.
> >From other computer in your network, goto:
> >Enterprise Manager>Tools Menu>Options
> >Choose "Read from remote" and enter the name of computer
> that you registered
> >servers in step above.
> >The settings must appear in your Enterprise Manager.
> >Amin
> >
> >
> >
> >"Alan" <SQL@.discussions.microsoft.com> wrote in message
> >news:1ee101c49ce0$f66a5e70$3a01280a@.phx.gbl...
> >> Hi,
> >>
> >> Is it possible to export registered SQL Server
> information
> >> and import it to the Enterprise Manager on the other
> >> server?
> >>
> >> Thanks
> >
> >
> >
> >
> >.
> >|||Hi Alan
I've got the same problem (empty tree structure after export & import)
If you look in the regesutry you'll see that the individual servers are
listed as keys but the key values are binary values (displayed in hex). I
wonder if these values (server login details) are encrypted and therefore
cant be read when imported to another machine?
Steve
"anonymous@.discussions.microsoft.com" wrote:
> Hi Amin,
> Thanks. But I need to copy remote registries and store it
> locally. I tried exporting from the registry
> [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered Servers X\SQL Server
> Group], but only groups show up, not individual sql
> server. So there're some other information stored
> somewhere needed but I couldn't identify them.
> Thanks
> Alan
> >--Original Message--
> >Hi Alan,
> >Go to Enterprise Manager>Tools Menu>Options
> >Clear checkbox for Read/Store user...
> >Register required servers. Now these registrations can be
> shared.
> >From other computer in your network, goto:
> >Enterprise Manager>Tools Menu>Options
> >Choose "Read from remote" and enter the name of computer
> that you registered
> >servers in step above.
> >The settings must appear in your Enterprise Manager.
> >Amin
> >
> >
> >
> >"Alan" <SQL@.discussions.microsoft.com> wrote in message
> >news:1ee101c49ce0$f66a5e70$3a01280a@.phx.gbl...
> >> Hi,
> >>
> >> Is it possible to export registered SQL Server
> information
> >> and import it to the Enterprise Manager on the other
> >> server?
> >>
> >> Thanks
> >
> >
> >
> >
> >.
> >
>|||I've sussed thats there's 2 seperate sets of entries for server regs in Ent
Manager:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X
and
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X
Any entries in the second location are shown if you untick the "user
independent option" i.e this tick box tells EM whether to look in
HKEY_CURRENT_USER (private) or HKEY_LOCAL_MACHINE (shared).
Question is how do you copy all the entries from the private structure into
the publick one?
Steve
"Amin Sobati" wrote:
> Alan,
> I exported:
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered Servers X
> Deleted servers and imported that file. Everything was ok when I opened
> EM(individual servers appeared again).
> Is it what you need to do?
> By the way, have a look to this address:
> HKEY_USERS\S-1-5-21-1482476501-842925246-854245398-500\Software\Microsoft\Mi
> crosoft SQL Server\80\Tools\SQLEW\Registered Servers X\SQL Server Group
> Amin
> <anonymous@.discussions.microsoft.com> wrote in message
> news:0f0f01c49f37$92cf66e0$7d02280a@.phx.gbl...
> > Hi Amin,
> >
> > Thanks. But I need to copy remote registries and store it
> > locally. I tried exporting from the registry
> > [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> > Server\80\Tools\SQLEW\Registered Servers X\SQL Server
> > Group], but only groups show up, not individual sql
> > server. So there're some other information stored
> > somewhere needed but I couldn't identify them.
> >
> > Thanks
> > Alan
> >
> > >--Original Message--
> > >Hi Alan,
> > >Go to Enterprise Manager>Tools Menu>Options
> > >Clear checkbox for Read/Store user...
> > >Register required servers. Now these registrations can be
> > shared.
> > >From other computer in your network, goto:
> > >Enterprise Manager>Tools Menu>Options
> > >Choose "Read from remote" and enter the name of computer
> > that you registered
> > >servers in step above.
> > >The settings must appear in your Enterprise Manager.
> > >Amin
> > >
> > >
> > >
> > >"Alan" <SQL@.discussions.microsoft.com> wrote in message
> > >news:1ee101c49ce0$f66a5e70$3a01280a@.phx.gbl...
> > >> Hi,
> > >>
> > >> Is it possible to export registered SQL Server
> > information
> > >> and import it to the Enterprise Manager on the other
> > >> server?
> > >>
> > >> Thanks
> > >
> > >
> > >
> > >
> > >.
> > >
>
>
>

Is possible to import registered SQL Servers to another Enterprise Manager

Is possible to design a localizable report?

We are developing an application that must work in two different languages,
and we have the following doubt:
Is it possible to design a localizable report ?, that is, that the report
appears in different languages depending on a parameter or the browser
configuration?
We are interested in develop only one version of every report, and that the
report server send to the user the report localized depending on a parameter
or the web browser configuration.
Is there something like the resource files for Windows forms?.
Thanks in advance for your help.Hi
I have found a workaround for this. Use the report parameters in your code
and set their default values in the code itself from the resource files and
then render the report. Since the default parameters will be based on the
culture settings the report would be rendered based on the culture.So all the
fields in the report that would vary with culture would be parameters
accepting the values form the resource file in the code.
Hope that helps.
Kalpna
"Eurofirms." wrote:
> We are developing an application that must work in two different languages,
> and we have the following doubt:
> Is it possible to design a localizable report ?, that is, that the report
> appears in different languages depending on a parameter or the browser
> configuration?
> We are interested in develop only one version of every report, and that the
> report server send to the user the report localized depending on a parameter
> or the web browser configuration.
> Is there something like the resource files for Windows forms?.
> Thanks in advance for your help.
>

Is possible to delete LOG file in SQL

can i delete the LOG file in the sql bcoz of large file size.

will it be able to use it without the LOG file and only with DAT file.

help me

SQL Server can't function without a log file. Regular log backups keeps your log small.

If you are not interested in the records in the log you can issue a BACKUP LOG dbname WITH TRUNCATE_ONLY. Maybe issue a DBCC SHRINKFILE (Filename, Size) WITH TRUNCATEONLY after that.

Using the SIMPLE recovery model also limits the size of the transaction log because it automatically truncates the log under certain circumstances. But a normal production database usually requires the FULL recovery model and regular backups.

Is possible to create dynamic reports based on cubes?

Hi,

I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria.

Thanks.

I thought you could do that with report builder, but I never use that feature anyway... One thing you can do is have users connect to the cube via Excel. In excel 2003, go to Data->Import External Data->New Database Query. Then, they can drag and drop cube dimensions and measures into an excel style pivot table. It is very easy to use. I've heard the excel 2007 version has more features so you may look at that. -Joe
|||

It's almost imposible to achieve it directly in SSRS. I would recommend Proclarity, Excel 2007, Excel 2003 or other 3rd party products (Business Objects Voyager). Report Builder may be used as well, just the result is always limited to the chosen layout, parameters - simply the report content cannot be changed.

|||

I have done my question before it was given me permissions for a connection to generate model in report manager and after sort out that issue and opening report builder i could open the data source related with cube. But now i have other situation that i need to confirm: Why the hierarchies created on the cubes and many of the attributes arent present on the model?Is any workaround that can be done?

In the meantime i tried to connect to the cube from the excel(the version installed is 2002) and i dont have the information about the data source on the OLAP Cube option even if i tried to create a new server and connect to the server. Any idea?

Thanks.

Is possible to create a new permanent table by a procedure?

Is possible to create a new permanent table by a procedure? I mean not a temporary table. If it is how do I do with the name? Because I would like put for each new table created as name as "forum1", "forum2", etc ... and not just numbers as I'll show you in the example. I tried to create the procedure, but I got stuck with permissions e primary key.

createprocedure new_forum

@.user_idint,

@.titlevarchar(50),

@.descriptionvarchar(200)

begin

declare @.totalForunsint

select @.totalForuns=count(*)from foruns

set @.totalForuns= @.totalForuns+ 1

Insertinto foruns(title,creation,country,[views],[description])

values(@.title,getdate(),'England',0,@.description)

CREATETABLE [dbo].[@.totalForuns](

[thread_id] [int]IDENTITY(1,1)NOTNULL,

[user_id] [int]NOTNULL,

[last_user_id] [int]NOTNULL,

[title] [nvarchar](50)NOTNULL,

[creation] [datetime]NOTNULL,

[last_answer] [datetime]NOTNULL,

[answers] [int]NOTNULLCONSTRAINT [DF_threads_answers] DEFAULT((0)),

[vizuais] [int]NOTNULLCONSTRAINT [DF_threads_views] DEFAULT((0)),

[fixed] [bit]NOTNULLCONSTRAINT [DF_threads_fixed] DEFAULT((0)),

[votos] [int]NOTNULLCONSTRAINT [DF_threads_votos] DEFAULT(''),

CONSTRAINT [PK_threads]PRIMARYKEYCLUSTERED

(

[@.totalForuns]ASC

)WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY]

)ON [PRIMARY]

ALTERTABLE [dbo].[@.totalForuns] WITHCHECKADD CONSTRAINT [FK_threads_users]FOREIGNKEY([user_id])

REFERENCES [dbo].[users]([user_id])

ALTERTABLE [dbo].[@.totalForuns]CHECKCONSTRAINT [FK_threads_users]

end

Thank you very much.

declare @.PKtotalForunsnvarchar

set@.PKtotalForuns = "PK_"+@.totalForuns

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

CONSTRAINT [@.PKtotalForuns]PRIMARYKEYCLUSTERED

Try does it work?

|||

Thank you for reply omerkamal

The name worked fine, but is not possible to create a table with foreign keys if this table doesn't exist yet. So first I have to create this table and after configure the FKs. To create a table I just cut the last lines, but I still got error in the last line, which just says there is a error near [PRIMARY]. Here is the code:

createprocedure new_forum

@.user_idint,

@.titlevarchar(50),

@.descriptionvarchar(200)

begin

declare @.totalForunsint

declare @.PKtotalForunsnvarchar

select @.totalForuns=count(*)from foruns

set @.totalForuns= @.totalForuns+ 1

set @.PKtotalForuns= "PK_"+@.totalForuns

Insertinto foruns(title,creation,country,[views],[description])

values(@.title,getdate(),'England',0,@.description)

CREATETABLE [dbo].[@.PKtotalForuns](

[thread_id] [int]IDENTITY(1,1)NOTNULL,

[user_id] [int]NOTNULL,

[last_user_id] [int]NOTNULL,

[title] [nvarchar](50)NOTNULL,

[creation] [datetime]NOTNULL,

[last_answer] [datetime]NOTNULL,

[answers] [int]NOTNULLCONSTRAINT [DF_threads_answers] DEFAULT((0)),

[vizuais] [int]NOTNULLCONSTRAINT [DF_threads_views] DEFAULT((0)),

[fixed] [bit]NOTNULLCONSTRAINT [DF_threads_fixed] DEFAULT((0)),

[votos] [int]NOTNULLCONSTRAINT [DF_threads_votos] DEFAULT(''),

CONSTRAINT [PK_threads]PRIMARYKEYCLUSTERED

( [@.PKtotalForuns]ASC)WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY]

)ON [PRIMARY]

Thank you again.

|||

CREATE TABLE [dbo].[@.totalForuns]( [thread_id] [int]IDENTITY(1,1)NOT NULL, [user_id] [int]NOT NULL, [last_user_id] [int]NOT NULL, [title] [nvarchar](50)NOT NULL, [creation] [datetime]NOT NULL, [last_answer] [datetime]NOT NULL, [answers] [int]NOT NULLCONSTRAINT [DF_threads_answers]DEFAULT ((0)), [vizuais] [int]NOT NULLCONSTRAINT [DF_threads_views]DEFAULT ((0)), [fixed] [bit]NOT NULLCONSTRAINT [DF_threads_fixed]DEFAULT ((0)), [votos] [int]NOT NULLCONSTRAINT [DF_threads_votos]DEFAULT (''),CONSTRAINT [@.PKtotalForuns]PRIMARY KEY CLUSTERED ( [thread_id]ASC )WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON)ON [PRIMARY])ON [PRIMARY]

|||

Actually you will need some dynamic SQL as follows:

EXEC('CREATE TABLE [dbo].[' + @.tblname +']( [thread_id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [last_user_id] [int] NOT NULL, [title] [nvarchar](50) NOT NULL, [creation] [datetime] NOT NULL, [last_answer] [datetime] NOT NULL, [answers] [int] NOT NULL CONSTRAINT [DF_threads_answers] DEFAULT ((0)), [vizuais] [int] NOT NULL CONSTRAINT [DF_threads_views] DEFAULT ((0)), [fixed] [bit] NOT NULL CONSTRAINT [DF_threads_fixed] DEFAULT ((0)), [votos] [int] NOT NULL CONSTRAINT [DF_threads_votos] DEFAULT (''''), CONSTRAINT [' + @.PKtotalForuns +'] PRIMARY KEY CLUSTERED ( [thread_id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]')

|||

I think I'll put all threads created in the same table distinguishing the forum by another FK. May be that's not the best way, because of the DB performance, but it's the quickest that I can do.

Thank you both very much.

Is possible to concatenate a column?

Hey all

Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?

Thanks
tiborSure you can! Although we need more info regarding your table construction and keys and the like.

To concatinate the memo columns from different rows is easy, in the INSERT, you will just use the concatination operator (i.e., "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).

The tricky part comes in building the select so that it pulls the memo parts based on the customer number and memo number association, and in the proper order/sequence (which, hopefully, your existing memoparts table provides a mechanism for). There is probably already a memopartnumber sequence construction there (maybe the key that "associates it all as one big text field in the application"?).

We just need to see the DDL on the associated tables.|||OR

You could use some SkullDugery in your Select Statement that will insert data into your New table (or even eliminate the need for a new table) - tehe

SELECT OuterTable.PKey , InnerTab.ConcatResultField
FROM OuterTable
LEFT JOIN(SELECT TabA.PKey,
(SELECT Memo + Memo
FROM OuterTable TabB
WHERE TabB.PKey = TabA.PKey
ORDER BY [Whatever]
FOR XML PATH('')
) ConcatResultField
FROM OuterTable TabA
GROUP BY PKey
) InnerTab ON OuterTable.PKey = InnerTab.PKey
PS. your choice whether to use the FOR XML Path Trickery for the Order By - may not be supported in future versions

GW|||"...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).when i do this with TEXT columns, i get the error message "Operand data type text is invalid for add operator."|||Why isn't this a presentaion issue?

Someone go find the blind dude|||You know, this really sounds more like a presentation issue.|||*rofl*!

Oh and I agree with the blind dude.|||Sure you can! Although we need more info regarding your table construction and keys and the like.

To concatinate the memo columns from different rows is easy, in the INSERT, you will just use the concatination operator (i.e., "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).

The tricky part comes in building the select so that it pulls the memo parts based on the customer number and memo number association, and in the proper order/sequence (which, hopefully, your existing memoparts table provides a mechanism for). There is probably already a memopartnumber sequence construction there (maybe the key that "associates it all as one big text field in the application"?).

We just need to see the DDL on the associated tables.

Sorry for the delay...

Yes there is a SeqNo field for each memo and a memo number but the problem is there is no key but there is a customer number to play with instead.

thanks for the reply, I just wanted to know if it was even possible and now the problem of creating that SELECT statement ensues, lol.

Thanks
tibor|||You know, this really sounds more like a presentation issue.

thank you

this mess is too shot|||deleted for duplicate post (more or less...)|||when i do this with TEXT columns, i get the error message "Operand data type text is invalid for add operator."Text? What is "text"?? I pretty much always use char() or varchar() and it works peachy for those types.

Text data types are for use by those losers that need well...ummm..."text" data types ;)

Besides, this is really a presentation issue anyway.|||Text? What is "text"?? oh, i dunno, it's just something i saw in post #1 --

... "the memo text field"|||Ok, so I am confused. What exactly is a "presentation issue"?|||oh, i dunno, it's just something i saw in post #1 --

... "the memo text field"Oh for crissake Rudy, you'll find this whole "guru" thing much easier if you don't allow yourself to become involved in the muddled world of technical accuracy and/or functionality details.

It's much more valuable to the newbie to be given only tangentally-accurate information so that he/she can learn by the application of technically flawed advice. This method allow the inquiring person to learn by experience.

So much to teach, so little time ;)|||LOL :cool:|||A presentation issue is an issue regarding how the data is displayed to the user, rather than having to do with the content of the data itself. SQL should not be used to handle presentation issues for several reasons. First, the SQL language was not designed with the intention of formatting data, but is optimized for retrieving and filtering data. Second, the database server should deliver the data in a generic format allowing different applications to display the same dataset in different ways. Third...
...ummm...third...
...thirdly, never talk about Fight Club.|||Ah, ok.

Well it seemed like a valid question as the application itself gives very very little leeway as to what can be done through its customizer unless you tap into the source code which we can not do. Thanks for the help.|||Ah, ok.

Well it seemed like a valid question as the application itself gives very very little leeway as to what can be done through its customizer unless you tap into the source code which we can not do. Thanks for the help.

oh for the love of...

How many "4GL" pieces of garbage are actually out there...

silver bullet my a$$|||if you have nothing nice to say, keep it to yourself...it is that simple. i come here for help, not criticism.|||what are you talking about?

Did you write the app? Sounds like a 3rd party off the shelf application that you are stuck with or did you write, in which case why don't you open up the code.

And if you did not write it, then you shouldn't touch the code because I'm sure it will invalidate any support aggreement

What was that line in the first Lethal weapon when Riggs first meets Roger?|||if you have nothing nice to say, keep it to yourself...it is that simple. i come here for help, not criticism.I don't think Brett was aiming the criticism at you, but rather railing at the plethora, or veritible cornucopia, as it were, of crap applications out there that were initially sold as do-all, cure-all bits-o-bytes.

FYI, seldom on this or any internet bulletin board does saying something like "if you have nothing nice to say, keep it to yourself..." result in that direction being taken.|||ok, i do apologize but it just seemed directed towards me. the 3rd party app is an erp packack and we are upgrading from one tier to the next and the table structures are totally different. when the client says they need this mod, well you cant just say "no its not possible" nor can you just hop into the source so you have to make do with what ya got. in this case, we have to create a custom table to pull the info from the former erp and how it had the info stored.

again, i do apologize.|||Well why don't you post the ddl of the old and new tables and what you have to map|||Ok this is the function that I have now in order to do the deeds:

CREATE FUNCTION dbo.GetMemo(@.MemoID Varchar(15))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SELECT @.Output = COALESCE(@.Output + '', '') + X.MemoText
FROM dbo.ARP_CustomerMemoHeader X

WHERE X.MemoNumber = @.MemoID
ORDER BY X.SeqNumber

RETURN @.Output
END
GO

And when I run this:

SELECT Distinct dbo.GetMemo('0221AD')
FROM ARP_CustomerMemoHeader
Where MemoNumber = '0221AD'

It gives me the results needed.

Now there is the matter of creating an update statement for the custom table that has everything but the memotext field populated and get this into that table.

thanks for the help and I owe a beer to those I got short with.:beer:|||thanks for the help and I owe a beer to those I got short with.:beer:Isn't it amazing that we are such a diverse and widely sprinkled lot, and yet we all really speak the same language where it is most important to do so ;)|||thanks for the help and I owe a beer to those I got short with.:beer:I found your comments deeply offensive. About 12 oz deep, actually.|||tallboy for me please!|||I found your comments deeply offensive. About 12 oz deep, actually.

I thought he was refering to about 2 inches.....and that's what yo uwere offended about

Lion King: Ah yes, the truth hurts|||Tibor: We need DDL|||tallboy for me please!
do what?

Oh, you are talking about glass size...scared me for a minute. ;)

Is possible restore only a MDF file from backup file?

Hi,
have a problem, I need restore only MDF file from my backup file named
data.BAK, beacause the LDF file is too big, is possible?
After restore data file, I need create a new empty log file for my db.
thx!I'm afraid not. When you do a RESTORE, SQL Server need to be able to create
the same amount of file, with the
same logical name and the same file size as you had when the backup was take
n.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Luca" <luca_milan@.hotmail.com> wrote in message news:5027603c.0403241209.210d6711@.posting.
google.com...
> Hi,
> have a problem, I need restore only MDF file from my backup file named
> data.BAK, beacause the LDF file is too big, is possible?
> After restore data file, I need create a new empty log file for my db.
> thx!|||Sorry,
I posted below the response for command restore filelistonly, any
suggestion for save this DB, I have only a db_2.BAK file:
db_2_Data|D:\MSSQLDataAndLogs\db_2_Data.mdf D PRIMARY 218693632 35184372
080640
db_2_Log|D:\MSSQLDataAndLogs\db_2_Log.ldf L NULL 54643130368 35184372080
640
Thanks a lot for any Help!!!
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Did you see my earlier post?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"luca milan" <luca_milan@.hotmail.com> wrote in message news:uo862BeEEHA.1272@.TK2MSFTNGP11.p
hx.gbl...
> Sorry,
> I posted below the response for command restore filelistonly, any
> suggestion for save this DB, I have only a db_2.BAK file:
> db_2_Data|D:\MSSQLDataAndLogs\db_2_Data.mdf D PRIMARY 218693632 35184372
> 080640
> db_2_Log|D:\MSSQLDataAndLogs\db_2_Log.ldf L NULL 54643130368 35184372080
> 640
> Thanks a lot for any Help!!!
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||No, sorry,
but I have find a valid MDF file and with sp_attach_single_file_db all
work fine!
Thx
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Is possible of using one single statement for inserting two tables?

Hi,

I need to know whether it is possible of using one single SQL statemnet to implement inserting and updating operations on two tables.

The back ground information is that the two tables are related with the following structure:

user_group_A table
id int -- PK,
field1 varchar(20),
field2 varchar(20)

user_group_B table
id int -- PK reference user_group_B,
field3 varchar(20),
field4 varchar(20)

that is the group A has field1 and field2, while the B has field1, field2, field3, and field3.

Thanks for you input.

v.Originally posted by Vernon
Hi,

I need to know whether it is possible of using one single SQL statemnet to implement inserting and updating operations on two tables.

The back ground information is that the two tables are related with the following structure:

user_group_A table
id int -- PK,
field1 varchar(20),
field2 varchar(20)

user_group_B table
id int -- PK reference user_group_B,
field3 varchar(20),
field4 varchar(20)

that is the group A has field1 and field2, while the B has field1, field2, field3, and field3.

Thanks for you input.

v.
You don't say what DBMS. For Oracle, the short answer is "no".

However, there is a way in which this could be accomplished:

1) Create a view based on the join between A and B
2) Create an INSTEAD OF INSERT trigger on the view, and in the trigger code perform inserts into both A and B.|||Thanks Tony for your advice.

I prefer a genertc solution over the DB special one. I currently use PostgreSQL for this project.

I have a look at the View. As the name indicated, it is for view only. Only a selection query can be operated on it. I also find another mechanism called rule, which can be used to insertion and update queries. I don't know whether it is a standard or not.

Can you elaborate the second point you stated?

Thank again.

Vernon|||Originally posted by Vernon
Thanks Tony for your advice.

I prefer a genertc solution over the DB special one. I currently use PostgreSQL for this project.

I have a look at the View. As the name indicated, it is for view only. Only a selection query can be operated on it. I also find another mechanism called rule, which can be used to insertion and update queries. I don't know whether it is a standard or not.

Can you elaborate the second point you stated?

Thank again.

Vernon
My second point refers to an Oracle feature, the INSTEAD OF [INSERT/UPDATE/DELETE] trigger. It allows you to override the default action for an insert etc. on a view. For example, for a view V based on tables A and B you might define a trigger:

CREATE OR REPLACE TRIGGER v_trg1
INSTEAD OF INSERT ON V
FOR EACH ROW
BEGIN
INSERT INTO A( id, ... ) VALUES (:NEW.id, ... );
INSERT INTO B( id, ... ) VALUES (:NEW.id, ...);
END;

Is Point in time recovery the same in all versions of SQL Server 2005?

Hello everyone,

Our company will be using SQL Server 2005 Enterprise on our main server and either SQL Server Express or workgroup for our disconnected users. Our users will be syncing their local database with the main server using replication.

The question that was brought up is about the PIT recovery in SQL Server Express and workgroup. Please correct me if I am wrong. From what I understood, Point in time recovery is the same in both the Express and the WG editions. The database restores the full backup then incremental backups (if any) then the transactional logs. The only exception is that WG edition supports log shipping. Am I right?

I also watch one of the sql server 2005 webcasts series that talked about the online piecemeal recovery. From my understanding, online restore feature is an enterprise edition feature which allows the server to restore backup and logs, server goes online then roll back uncommited transactions. My question is if we are to use Express or WG, can we restore filegroups or pages (offline)? In another word, can we restore specific tables to point in time instead of the whole database.

Thanks a lot.

Point in time recovery is the same in all versions of sql2005.

Minor point: we do not support incremental backup. I'm sure you are refering to differential backups.

As far as I know, log shipping is only supported by the enterprise edition. That refers to our automatic tools to setup and maintain the log shipping.

The underlying engine supports log shipping in all versions, but you'd have to "roll your own" automated procedures.

Regarding piecemeal restore: we only support online restore in the enterprise edition. File/fileGroup/Page restore is offline in all other editions.

It is not possible to restore filegroups (and the tables they contain) to different points in time. The only way to perform a table restore:

- perform a partial restore to a working database. Just restore the filegroups containing the tables of interest.

- then export the tables (or sets of rows) of interest and merge them back into your online database. It is possible that referential integrity constraints will require more work than just inserting the rows of interest (you may have to delete/update other tables to maintain consistency).

Hope that helps.

Is Physical server name stored in the Master database

Hi,
We have a situation where we want to move our current database
server to a different hardware and rename the server.
If we change the Physical server name, do we have to go thru the whole
process of changing it in sql server by running sp_dropserver,
sp_addserver?
Can we have an alias for the server name in DNS and can sql server
resolve the server name internally by going thru DNS?

For eg: Our current database server name is FFSQL-PRD01. We have it
registered in EM as FFSQL-PRD01. Now we renamed the server to
STLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias of
FFSQL-PRD01( the same old name as alias). After doing this when I go
to EM and click on FFSQL-PRD01 would it give me an error? or can it
resolve the server name by going to DNS.

When you install SQl server the default instance has to be the physical
server name, Why? Is the physical server name stored in Master
database( SYSSERVERS table)?

Thanks for your help.
Geetha<gdabbara@.brownshoe.com> wrote in message
news:1112383277.914231.158550@.o13g2000cwo.googlegr oups.com...
> Hi,
> We have a situation where we want to move our current database
> server to a different hardware and rename the server.
> If we change the Physical server name, do we have to go thru the whole
> process of changing it in sql server by running sp_dropserver,
> sp_addserver?

Yes - but this should be a quick task, and it's easily scripted if you have
many servers to change.

> Can we have an alias for the server name in DNS and can sql server
> resolve the server name internally by going thru DNS?
> For eg: Our current database server name is FFSQL-PRD01. We have it
> registered in EM as FFSQL-PRD01. Now we renamed the server to
> STLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias of
> FFSQL-PRD01( the same old name as alias). After doing this when I go
> to EM and click on FFSQL-PRD01 would it give me an error? or can it
> resolve the server name by going to DNS.

As far as I know, EM will resolve the name using a SQL client alias (if you
created one in the client network utility), or the operating system's name
resolution method (DNS, in this case). But why not just change the
registration name in EM, and connect to the real name? If you have a lot of
EM registrations, then you can script them using the SQLDMO ServerGroup and
RegisteredServer objects.

> When you install SQl server the default instance has to be the physical
> server name, Why? Is the physical server name stored in Master
> database( SYSSERVERS table)?

If you have multiple instances, you need to define what 'default' means.
Since Microsoft don't know what you will call your instances, they probably
decided to go with just the physical name as the default; it also means you
can upgrade from previous MSSQL versions without having problems related to
instance naming.

> Thanks for your help.
> Geetha

To rename a server and change your EM registration is not usually a big job
(unless you have many servers to rename), so I'm not sure why you want to
use an alias to keep the old name - perhaps your real issue is how to
connect client applications to the new server name? If so, it depends on how
the client connects - you may have to change DSNs, or ADO connection
strings, or whatever. You might be able to use DNS aliases or client-side
aliases as part of a migration process, but in the longer run it will make
administration much easier if you use the real server name.

If this doesn't help, I suggest you give some more details about exactly why
you need some sort of alias, rather than just using the new server name.

Simon|||Simon Hayes wrote:
> <gdabbara@.brownshoe.com> wrote in message
> news:1112383277.914231.158550@.o13g2000cwo.googlegr oups.com...
> > Hi,
> > We have a situation where we want to move our current database
> > server to a different hardware and rename the server.
> > If we change the Physical server name, do we have to go thru the
whole
> > process of changing it in sql server by running sp_dropserver,
> > sp_addserver?
> Yes - but this should be a quick task, and it's easily scripted if
you have
> many servers to change.
> > Can we have an alias for the server name in DNS and can sql server
> > resolve the server name internally by going thru DNS?
> > For eg: Our current database server name is FFSQL-PRD01. We have
it
> > registered in EM as FFSQL-PRD01. Now we renamed the server to
> > STLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias
of
> > FFSQL-PRD01( the same old name as alias). After doing this when I
go
> > to EM and click on FFSQL-PRD01 would it give me an error? or can it
> > resolve the server name by going to DNS.
> As far as I know, EM will resolve the name using a SQL client alias
(if you
> created one in the client network utility), or the operating system's
name
> resolution method (DNS, in this case). But why not just change the
> registration name in EM, and connect to the real name? If you have a
lot of
> EM registrations, then you can script them using the SQLDMO
ServerGroup and
> RegisteredServer objects.
> > When you install SQl server the default instance has to be the
physical
> > server name, Why? Is the physical server name stored in Master
> > database( SYSSERVERS table)?
> If you have multiple instances, you need to define what 'default'
means.
> Since Microsoft don't know what you will call your instances, they
probably
> decided to go with just the physical name as the default; it also
means you
> can upgrade from previous MSSQL versions without having problems
related to
> instance naming.
> > Thanks for your help.
> > Geetha
> To rename a server and change your EM registration is not usually a
big job
> (unless you have many servers to rename), so I'm not sure why you
want to
> use an alias to keep the old name - perhaps your real issue is how to

> connect client applications to the new server name? If so, it depends
on how
> the client connects - you may have to change DSNs, or ADO connection
> strings, or whatever. You might be able to use DNS aliases or
client-side
> aliases as part of a migration process, but in the longer run it will
make
> administration much easier if you use the real server name.
> If this doesn't help, I suggest you give some more details about
exactly why
> you need some sort of alias, rather than just using the new server
name.
> Simon

Hi,
Thanks a lot for your quick responses. The reason we are pushing
for DNS alias, is because we have a lot of linked server connections
and DTS package jobs that we might have to change as they are using the
server name. I just used EM as an example. I would think DTS packages
would run fine based on what you have said earlier about it being able
to resolve the name from DNS or SQl client config.

Thanks
Geetha

Is password case sensitive on SQL Server?

When I configured the System DSN, seems no matter what case I entered the pa
ssword in, they all worked. Does that mean, password is not case sensitive
on SQL Server?
BingThat depends on the collation or sort order of your server. Do a
sp_helpsort. If the server sort order is case-insensitive, then your
passwords are as well. Nice feature of SQL 2000, read this for more info.
Remember the default collation of SQL (1252) is not case-sensitive
http://www.microsoft.com/sql/techin...sesensitive.asp
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Bing Du" <anonymous@.discussions.microsoft.com> wrote in message
news:C93FFA88-A7B1-4D5E-8B38-BD4F8484B3A0@.microsoft.com...

quote:

> When I configured the System DSN, seems no matter what case I entered the

password in, they all worked. Does that mean, password is not case
sensitive on SQL Server?

quote:

> Bing

|||Here's a great suggestion to, go Windows Authentication. If you, your
users, your applications and company can handle it, it is a great way to
secure SQL. Heck, SQL 2000 SP 3a tries to even force it. Gets you closer
to the goal of single sign-on, but then of course you have to worry about
everything being right so you don't get trusted connection login errors.
BTW, by sending that article about the sort order, I think that is
ridiculous. If you have databases with sort orders other than what the
master database has, you really have to watch it with temp tables and other
items in the tempdb. I've seen many a stored procedure have to be rewritten
because some database got attached to a server with a different sort order,
which caused a problem with the stored procedure making temp tables.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Bing Du" <anonymous@.discussions.microsoft.com> wrote in message
news:C93FFA88-A7B1-4D5E-8B38-BD4F8484B3A0@.microsoft.com...

quote:

> When I configured the System DSN, seems no matter what case I entered the

password in, they all worked. Does that mean, password is not case
sensitive on SQL Server?

quote:

> Bing

|||Thanks very much for the information, Andy. Since I'm not quite familiar wi
th MS SQL server, it will take me more time and learning to get your message
fully digested.
Bing

IS Package doesnt ship with password

Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.

How do I force IS to save password on any ProtectionLevel ?

Thanks,
Fahad

Fahad349 wrote:
Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad

You can't. You'll have to set it through dtexec. SSIS doesn't save passwords. There are plenty of threads in this forum on that topic.|||First, /SET option isnt working
I am trying it this way
U sa /P gama /SET "\Package.Connections[TRIMURTY.TMDB.SA].Properties[ConnectionString]";"Data Source=TRIMURTY;User ID=sa;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;Password=gama"

Second, I have to run this IS Package from another IS Package as well by using Execute Package Task. How would I supply these things on there ?

Please help me
Thanks
Fahad

Phil Brammer wrote:
Fahad349 wrote:
Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad
You can't. You'll have to set it through dtexec. SSIS doesn't save passwords. There are plenty of threads in this forum on that topic.

|||If you're promoting these packages to the MSDB database in SQL Server, have you looked at using ServerStorage for the ProtectionLevel property?|||I have read about ServerStorage, It has nothing to do with storing connections' passwords but storing the Package.|||

Fahad349 wrote:
I have read about ServerStorage, It has nothing to do with storing connections' passwords but storing the Package.

No, not true. it means it relies on SQL Server to match up the assigned user to the SQL server user account.

ServerStorage: "Protects the whole package using SQL Server database roles."|||Here I am not interested in package protection or package security. I am interested in saving password with package. or to avoid this error "login failed for user 'sa'"

ProtectionLevel ServerStorage doesnt let me save the DTS Package in designer.|||

Fahad349 wrote:
Here I am not interested in package protection or package security. I am interested in saving password with package. or to avoid this error "login failed for user 'sa'"
ProtectionLevel ServerStorage doesnt let me save the DTS Package in designer.

Right, but again, you cannot save the password with the package. Can't do it. When you save to the server, you can choose SQL Server role security. Or, try EncryptSensitiveWithPassword. Using that though, you'll have to issue the password everytime the package executes.|||

Phil Brammer wrote:

Right, but again, you cannot save the password with the package. Can't do it. When you save to the server, you can choose SQL Server role security. Or, try EncryptSensitiveWithPassword. Using that though, you'll have to issue the

Ok I will issue the password, but that password will be to execute dts and its separate from passwords in connection manager.
And my understanding with this error message is; the Executable is unable to open child package for executing, not connection manager inside this child has error.|||

Fahad349 wrote:
Phil Brammer wrote:

Right, but again, you cannot save the password with the package. Can't do it. When you save to the server, you can choose SQL Server role security. Or, try EncryptSensitiveWithPassword. Using that though, you'll have to issue the
Ok I will issue the password, but that password will be to execute dts and its separate from passwords in connection manager.
And my understanding with this error message is; the Executable is unable to open child package for executing, not connection manager inside this child has error.

Tough to say... I would think it's the connection managers that's throwing the error.|||

Search this forum for configuration files then use these to "freeze" the password.

Philippe

Is osql standalone?

Hello all,
I want to know if I can start the osql utility from a computer without SQL
Server, MSDE, etc. Is this file standalone?
Thanks,
SorinYou must have installed sql connectivity, and Osql, but the server stuff is
not required.
--
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
"Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
> Hello all,
> I want to know if I can start the osql utility from a computer without SQL
> Server, MSDE, etc. Is this file standalone?
> Thanks,
> Sorin|||ok...thanks a lot
Sorin
"Wayne Snyder" wrote:
> You must have installed sql connectivity, and Osql, but the server stuff is
> not required.
> --
> 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
> "Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
> news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
> > Hello all,
> >
> > I want to know if I can start the osql utility from a computer without SQL
> > Server, MSDE, etc. Is this file standalone?
> >
> > Thanks,
> >
> > Sorin
>
>

Is osql standalone?

Hello all,
I want to know if I can start the osql utility from a computer without SQL
Server, MSDE, etc. Is this file standalone?
Thanks,
Sorin
You must have installed sql connectivity, and Osql, but the server stuff is
not required.
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
"Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
> Hello all,
> I want to know if I can start the osql utility from a computer without SQL
> Server, MSDE, etc. Is this file standalone?
> Thanks,
> Sorin
|||ok...thanks a lot
Sorin
"Wayne Snyder" wrote:

> You must have installed sql connectivity, and Osql, but the server stuff is
> not required.
> --
> 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
> "Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
> news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
>
>

Is osql standalone?

Hello all,
I want to know if I can start the osql utility from a computer without SQL
Server, MSDE, etc. Is this file standalone?
Thanks,
SorinYou must have installed sql connectivity, and Osql, but the server stuff is
not required.
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
"Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
> Hello all,
> I want to know if I can start the osql utility from a computer without SQL
> Server, MSDE, etc. Is this file standalone?
> Thanks,
> Sorin|||ok...thanks a lot
Sorin
"Wayne Snyder" wrote:

> You must have installed sql connectivity, and Osql, but the server stuff i
s
> not required.
> --
> 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
> "Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
> news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
>
>

Is Order By affect The Query Speed

hello,
I have a query that insert insert into new table , and then i select from this table,
if i add ORDER BY in the INSERT INTO script , does it affect the speed of the SELECT
i have big table that take about 70 secyes, it will either be ignored, or it will slow the INSERT down|||I'm confused. ORDER BY is not allowed in INSERT statements, is it? Unless part of a sub-query or something along those lines. And why would you do an order-by on an insert, since it makes no logical sense to try to instill order on a table that is not ordered.

The ORDER BY has to be on the select OUT from the table.|||I use ORDER BY in INSERT INTO cause I use INSERT INTO to a SELECT Statment, i have SELECT nested into INSERT

My Question is if i add the data in order in the table do i have better SELECT query performance|||So your ORDER by is not in the INSERT itself, but rather in the SELECT that is gathering the input. That is what I meant.

You're wasting your time and typing fingers. You cannot instill order when inserting to a SQL table, because there is no valid concept of that type of order in a SQL table.

SO, the short answer is NO. It will not affect later query speed. It is also a direct affront to all things good and beautiful. Most probably it would cause a Smiting Blow from the SQL Gods were it to be implemented in a production database.

If order is important, do it on your SELECT when you are pulling data out of the table.

The only time I would put an ORDER BY in an insert statement is if it was necessary in the SUB-SELECT to assure that the right data is pulled from the source table to be slapped into the INSERT table. OR if you need to limit the input from the select...as in: INSERT dbo.MyDestinationTable
SELECT TOP 100
Myfield1,
Myfield2,
MyDate
FROM MySourceTable
ORDER BY MyDate DESC and in this case, the ORDER BY is relative to the SELECT, not the INSERT.|||Thanks man|||You're welcome, dude.|||cowboy,

all of that is straight from the orthodox canon and that's fine and good, yet before I knew any better i wrote a server side pagination thingie that does an INSERT with a SELECT and an ORDER BY into a temp table and it does the row numbering with an identity column. It worked and still works and everytime I read this thing about it not being reliable, I have never seen it not work in practice.|||SO, the short answer is NO. It will not affect later query speed. please, sir, i must disagree

i think i gave the correct answer already

this --

INSERT INTO ... SELECT ... FROM ... ORDER BY ...

is going to be slower than this --

INSERT INTO ... SELECT ... FROM ...

simply because the ORDER BY will take extra time

(and, as we all know, the ORDER BY isn't guaranteed to actually insert them in the correct order anyway)|||OK Sean, I see your point, I knew I should have loaded my Orthodox Sidearm with birdshot so it scattered better. I still think that your example is another situation where the ORDER BY is related to the SELECT rather than the INSERT. Maybe it's semantics, but an order by in your situation is actually used to populate one of the INSERT table's columns, which is the ID column. Conceptually (in my mind, anyway) this applies to the gathering of the data to be inserted. NOT the ordering of the insert table.

In your example, the insertion of the data has nothing (reliably) to do with the actual physical order of the data in the table, nor in and of itself anything to do with the speed of a subsequent select from the table, correct? Of course the speed to be gained, if any, on the select has to do with the construction of the SELECT statement, and any applicable indicies that may be in place. NOT, per se, by anything you did on the insert itself.|||But the original question was whether subsequent SELECTS from the table will be faster, which they are not. There was no question as to which method of inserting was going to be faster. Presumably that is beyond debate.

If you have some free time, and a busy multi-CPU server, do a sp_helptext sp_who2, and scroll down to the bottom. I always loved the comment there:

-- (Seems always auto sorted.) order by spid_sort

On a busy server, parallelism takes over, and you get the results in every which way. I wonder how much processing time is shaved off by commenting out the order by statement. I mean, you might even have 200 or 500 rows to order!|||please, sir, i must disagree

i think i gave the correct answer already

this --

INSERT INTO ... SELECT ... FROM ... ORDER BY ...

is going to be slower than this --

INSERT INTO ... SELECT ... FROM ...

simply because the ORDER BY will take extra time

(and, as we all know, the ORDER BY isn't guaranteed to actually insert them in the correct order anyway)Well then, I guess you've forced my hand. Unfortunately, I've got to disagree with your disagreement then. Your answer was correct in your interpretation of the original question. That is, if he/she (or is that HeShe? I get that confused sometimes, which really has cause problems in the bar at times at closing) was asking if the ORDER BY would case the INSERT to be slower. If that is the case, I am behind you (at a respectable distance, of course) 100%.

However, if heshe asked if using ORDER BY on the INSERT would have any effect on later and independent SELECTs, then I stand by my answer. Nope.|||Daft I know but I have always* wondered whether ordering an insert by the clustered index definition is better\ no different to not ordering an insert. I have a feeling I have read somewhere one way or the other but I can't remember what the upshot was. The thing is - unless SS does implicitly order the insert anyway then it will be slower than an unordered insert as there will be page splits. When using BCP you can tell BCP if the order of data in the file & clustered index order are the same and the bulk insert is faster.

It is this sort of thing that gets me to sleep at night.

*not literally|||But the original question was whether subsequent SELECTS from the table will be faster

Oops.
There was no question as to which method of inserting was going to be faster. Presumably that is beyond debate.
Double oops.|||I have a feeling I have read somewhere one way or the other but I can't remember what the upshot was. perhaps this may help:
INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted

-- Ordering guarantees in SQL Server... (http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx)|||perhaps this may help:
INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted

-- Ordering guarantees in SQL Server... (http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx) Yeah, what he said.|||Thanks Rudy. That does actually fit with my observations - truncate a table\ clustered index, pump in several million rows in a single statement, check the fragmentation and the table turns out to be totally fragmented. I think I had always hoped that SS would order the input to fill the pages up to the fillfactor. I guess it must insert them in whatever order is most efficient for the select irrespective of what might be the most effecient insert order (assuming ordering the insert < splitting pages all over the place).

I appreciate all the logical "no order in relations" stuff - I am thinking solely about the physical aspect.

Did anyone let mgsn know that the physical order of the data (i.e. the clustered index) may affect many of the queries he\ she might perform on the table?|||Yeah, what he said.Thanks to you too Sean :)|||.
.
.
On a busy server, parallelism takes over, and you get the results in every which way. ...
So then; limiting the INSERT SP to a single processor might help reduce page splitting? Not "absolute guarantee", but reduce it.

My (unwarranted?) *impression* with reading about order-by in sub-queries not necessarily returning ordered sets to the outer queries, a symptom I've seen happen on rare occasion, was that it had to do with query plans.

Multi-processing affecting this also makes perfect sense.

However; since Ordering a Clustered Insert is relevant would it follow that setting the "Insert ... from select .. ordered by ..." SP to single processor would help matters?

Again; not looking for perfection, but in the real world, achieving an ordered insert 99.9% of the time.

I'd like to comment that, if the nature of the data is that ajoining records are often the objects of the same select, then having stuff together (in the same page) would be a performance gain.

For example: Let's say you have a non-clustered Child table that's ordered by ParentID. Let's say every "Family" has about 20 children on average, and the pages just happen to fit about 30 or 40 children rows each. Now let's say the child table has 30 million rows (to keep the Query Planner from invoking a full table scan). So; selecting a given 50 families would naturally use the ParentID key to go get the children. You would wind up reading maybe 25 pages on average if the related children (for each family) are all together, and therefore always fitting in either 1 or 2pages. By contrast; if they're all hap-hazzard you would get 50 x 20 page reads in the child table. Of course; at least some of the WHERE would have to be resolved outside of the child data records.

As a 2nd comment. For similar reasons; wouldn't it make sense to put stuff together for head-movement purposes? Not relevant for nearly all tables, but the situation may come when some table is so huge and so key that you put it on it's own HDD, and due to it's nature (let's just say it's financial transactions that are Usually queried within a range of perhaps 1 month but you don't archive until data is 8 months old), then the disks could predominately stay in the same area. Same would apply if lots of ordered transfers are done, so if you built an index that resolves the Where Clause but the actual transfer usually has large amounts of pages to read (so, of 100 million rows, let's say it's selecting a group of 5 million), if the data was all over the place, the heads would thrash. I believe that EVEN IF THE TABLE weren't on it's own drive, this COULD be a thrashing-saving quality.

I'm just saying that although unordered heaps and clusters are logically equivalent to ordered ones, that there are special cases where there's a cost to being unordered and that the cost can be severe for totally random cases. While I'm no expert in Relational databases - the basic concept of "reduce physical reads" surely remains valid.|||Hi vich

I've read this a couple of times. I think I know where you are going but there are a couple of lines that don't work.
Let's say you have a non-clustered Child table that's ordered by ParentID. This is an oxymoron. Did you mean clustered child table?

I'm just saying that although unordered heaps and clusters are logically equivalent to ordered onesAre you saying that a clustered index can be unordered? The definition of a clustered index has ordering right at the centre of it so again I don't quite get it.

Just to be clear - there are two types of table in the sql server world - Steers and Que... oops not them... Heaps & Clustered Indexes. Heaps are unordered and have no B-Tree structure. CIs are ordered (at the leaf level) by the index column(s) and have a B-Tree.

Overall though you are spot on - selected data can be more efficiently retrieved if it is physically contiguous and occupies the minimum number of pages.|||Hi vich

I've read this a couple of times. I think I know where you are going but there are a couple of lines that don't work.
This is an oxymoron. Did you mean clustered child table?

Are you saying that a clustered index can be unordered? The definition of a clustered index has ordering right at the centre of it so again I don't quite get it.

Just to be clear - there are two types of table in the sql server world - Steers and Que... oops not them... Heaps & Clustered Indexes. Heaps are unordered and have no B-Tree structure. CIs are ordered (at the leaf level) by the index column(s) and have a B-Tree.

Overall though you are spot on - selected data can be more efficiently retrieved if it is physically contiguous and occupies the minimum number of pages.
On point 1, by "ordered" I meant "contiguously in sequence" and no, I did mean heap, not clustered.

x1x2x3x4x5
not x3x1x5x4x2

So; "ordered heap" is an oxymoron in strict logical terms, but the records in the heap do physically reside somewhere. My point was that IF you know what groups of rows your will often read together, then physically grouping them will reduce physical reads in direct proportion to rows-per-page. Even if it's only "sometimes", it's still an improvement over being totally random since that will gaurentee worst case page hits.

To rephrase; one aspect of maximizing page hits is through placing rows into the same page if they can predictably be read together.

The phrase "Ordered Heap" must be to a DBA as "Perpetual Motion Machine" is to an engineer so lol; hence my clarification.

On the 2nd point, I was redundant. It should have read:
I'm just saying that although unordered heaps and clusters are logically equivalent.|||Ah - I see. However, in the heap the data being physically contigious would be useless - the engine would still need to scan every page in the table. It doesn't matter how many rows there are - heaps -> scans. Or have I missed your point again :D|||Ah - I see. However, in the heap the data being physically contigious would be useless - the engine would still need to scan every page in the table. It doesn't matter how many rows there are - heaps -> scans. Or have I missed your point again :D
What about indexes? Direct Access? Heap -> Full Scan is only occassionally true, correct? Often even, but not always.

With all do respect sir; in my case of 30 million rows when it's going after about 1000 of them, wouldn't you agree we should shoot the query plan that reads them all?

EDIT - added section below:
I was thinking about what bearing .mdf file's fragmentation has. My 2:30AM conclusion is; not too much. It's all about page hits. If the pages are scattered around, then seek ahead buffering, head movement, etc would come into play, but those ramifications (I think) would be far less than finding as much as possible per page (block really). It would probably come down to row size vs. block size (what is a "Page" anyway, at least as compared with a "block").

Anyway; this all boggels my uninformed mind and I submit to the engineer's recommendations, but I also want to understand the pieces so I can make good tuning decisions some day.

This thread's complete dismissal about physical placement of indexed heap data just struck me as "file this under, question this one'".|||Do you have a NC index on ParentID in this case? And we are still talking about a heap yes (the oxymoron-ordered heap ;))?|||Do you have a NC index on ParentID in this case? And we are still talking about a heap yes (the oxymoron-ordered heap ;))?
Yes, an indexed heap. But if the indexes tell you "go get these 1000 rows", I'm just saying that if prior inserts managed to maximize paging efficiency, then obviously 50 reads are better than 1000.

Sorry; don't know what "NC" is, but I guess I assumed that from the parent/child relationship. However; it wouldn't matter for the point being made. Even if the child's indexes required a full scan; if the DB Engine determined "go get these 1000 rows", it still holds true that 50 page reads trumps 1000. The scenerio did say that all children would be read as part of the "family".

Again; I'm just saying there are special cases, and not all that uncommon ones.|||NC = nonclustered. You have a nonclustered index on parentID?|||Even if the child's indexes required a full scan; if the DB Engine determined "go get these 1000 rows", it still holds true that 50 page reads trumps 1000. I think that is my point. Without any useable index on ParentID then the engine will scan the pages. Remember that scanning the pages involves getting them into memory and checking for the particular ParentID value. It is the number of pages scanned not the number of pages that the data is on that counts. As such, having all the relevent data on a small number of pages is only beneficial if the engine can know this and limit its retrieval to these pages only.|||So then; limiting the INSERT SP to a single processor might help reduce page splitting? Not "absolute guarantee", but reduce it.

Absolutely not. Limiting the number of CPUs that process an insert has no relevance to how data is stored on disk. And creating an "ordered heap" is a bit of a fantasy in 99% of cases, since you expect some deletes to happen on occasion. What do you think SQL Server is going to do with those gaps? Use 'em, that's what.

Besides which, you have eloquently explained in your first scenario EXACTLY why you would want a clustered index on the ParentID column. So the children are all clustered (pun intended) on the same set of pages.|||Besides which, you have eloquently explained in your first scenario EXACTLY why you would want a clustered index on the ParentID column. So the children are all clustered (pun intended) on the same set of pages.Just what I have been trying to say. The crucial difference between this clustered index and the magically ordered heap is that SS also can guarentee that these values are only on these pages and not on any others AND can get there quickly via the B-Tree so it does not need to hunt through the entire table looking for them.|||Absolutely not. Limiting the number of CPUs that process an insert has no relevance to how data is stored on disk. And creating an "ordered heap" is a bit of a fantasy in 99% of cases, since you expect some deletes to happen on occasion. What do you think SQL Server is going to do with those gaps? Use 'em, that's what.

Besides which, you have eloquently explained in your first scenario EXACTLY why you would want a clustered index on the ParentID column. So the children are all clustered (pun intended) on the same set of pages.
Drrrr (que anvel falling on my head). That actually did dawn on me last night when thinking of it after posting.

If you know Parent ID will determine a lot of read clusters during future queries, and being a "parent ID" (presumably auto sequenced), it will tend to be a good cluster key (adding to End).

However; if all families tend to grow (ie: Parent table rarely grows) then would page splitting occur a lot? That's where padded indexing and fill factors would come into play, I imagine.

Example: A scientific statistical program that maintains thousands of counters and is available to the scientific community on the internet (therefore; gets LOTS of reads). For sake of arguement, let's say that different scientists are only interested in their pet statistic but when they get it, all data for that statistic type (child rows for that parent) need to be read. So; the child table (the gathered statistic data) is constantly inserted into and the inserts are for all parent IDs (the statistic type).

This would benifit greatly from physical clustering by Statistic Type (parentID) but inserts would require enough padding in each statistic type (index padding?) to prevent page splits during Insert until the next index reorg can refresh the padding amount.

Thank you. I know it's painful teaching stubborn beginners.|||Broadly yes. Don't be too scared of page splits though. If you set a fill factor whereby you never get page splits then it is set too high. One of the problems with page splits is the increase in the number of pages needing to be read as they are not "full". Fill factor has exactly the same effect so putting in such a low fill factor pages never get full is counterproductive as you are increasing the number of pages that need to be read to satisfy queries.

Friday, February 24, 2012

Is primar key/foreign key evil in SQL Server?

Is possible?

Is possible?

Is possible?

Is possible to import registered SQL Servers to another Enterprise Manager

Is possible to import registered SQL Servers to another Enterprise Manager

Is possible to design a localizable report?

Is possible to delete LOG file in SQL

Is possible to create dynamic reports based on cubes?

Is possible to create a new permanent table by a procedure?

Is possible to concatenate a column?

Is possible restore only a MDF file from backup file?

Is possible of using one single statement for inserting two tables?

Is Point in time recovery the same in all versions of SQL Server 2005?

Is Physical server name stored in the Master database

Is password case sensitive on SQL Server?

IS Package doesnt ship with password

Is osql standalone?

Is osql standalone?

Is osql standalone?

Is Order By affect The Query Speed

table hosed

Blog Archive

About Me