sql rowcount: table

Showing posts with label table. Show all posts

Thursday, March 22, 2012

_WA_ indexes in sysindexes

Hi
I was writing script to defrag my indexes on a regular basis.
I was checking the sysindexes table and found
_WA_ indexes and I do no see a corresponding object for the sysobjects
WHat are these indexes?
Mangesh
These are statistics rather than indexes. You can exclude statistics and
hyhothetical indexes (created by the Index Tuning Wizard) rows in sysindexes
using INDEXPROPERTY:
SELECT OBJECT_NAME(id), name
FROM sysindexes
WHERE
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:0C87800A-4467-4AD0-BCCD-DD822FFF0649@.microsoft.com...
> Hi
> I was writing script to defrag my indexes on a regular basis.
> I was checking the sysindexes table and found
> _WA_ indexes and I do no see a corresponding object for the sysobjects
> WHat are these indexes?
> Mangesh

_WA_ indexes in sysindexes

Hi
I was writing script to defrag my indexes on a regular basis.
I was checking the sysindexes table and found
_WA_ indexes and I do no see a corresponding object for the sysobjects
WHat are these indexes?
MangeshThese are statistics rather than indexes. You can exclude statistics and
hyhothetical indexes (created by the Index Tuning Wizard) rows in sysindexes
using INDEXPROPERTY:
SELECT OBJECT_NAME(id), name
FROM sysindexes
WHERE
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:0C87800A-4467-4AD0-BCCD-DD822FFF0649@.microsoft.com...
> Hi
> I was writing script to defrag my indexes on a regular basis.
> I was checking the sysindexes table and found
> _WA_ indexes and I do no see a corresponding object for the sysobjects
> WHat are these indexes?
> Mangesh

_WA_ indexes in sysindexes

Hi
I was writing script to defrag my indexes on a regular basis.
I was checking the sysindexes table and found
_WA_ indexes and I do no see a corresponding object for the sysobjects
WHat are these indexes?
MangeshThese are statistics rather than indexes. You can exclude statistics and
hyhothetical indexes (created by the Index Tuning Wizard) rows in sysindexes
using INDEXPROPERTY:
SELECT OBJECT_NAME(id), name
FROM sysindexes
WHERE
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:0C87800A-4467-4AD0-BCCD-DD822FFF0649@.microsoft.com...
> Hi
> I was writing script to defrag my indexes on a regular basis.
> I was checking the sysindexes table and found
> _WA_ indexes and I do no see a corresponding object for the sysobjects
> WHat are these indexes?
> Mangesh

_WA Statistics Error

Hello,
We have had a problem with a table recently where a load had stopped half
with through with an index problem.
After a bit of delving the problem seems to be causes by the system created
Statisitcs for a particial index or at least thats where the error message i
s
pointing to.
The database is set to auto update the statistics and we use this table to
do a lot of loads, truncates and deletes.
My question is this, has anyone seen a problem created by the System created
statisitics and what was the fix.
TIA
PatriciaParticia
What's version are you using?
Havu you ran DBCC SHOW_STATISTICS?
Also take a look at sp_createstats system stored procedure
"Patricia" <Patricia@.discussions.microsoft.com> wrote in message
news:2E80BF08-3146-4749-8142-CC7A7BC1F56D@.microsoft.com...
> Hello,
> We have had a problem with a table recently where a load had stopped half
> with through with an index problem.
> After a bit of delving the problem seems to be causes by the system
> created
> Statisitcs for a particial index or at least thats where the error message
> is
> pointing to.
> The database is set to auto update the statistics and we use this table to
> do a lot of loads, truncates and deletes.
> My question is this, has anyone seen a problem created by the System
> created
> statisitics and what was the fix.
> TIA
> Patricia

_WA Statistics Error

Hello,
We have had a problem with a table recently where a load had stopped half
with through with an index problem.
After a bit of delving the problem seems to be causes by the system created
Statisitcs for a particial index or at least thats where the error message is
pointing to.
The database is set to auto update the statistics and we use this table to
do a lot of loads, truncates and deletes.
My question is this, has anyone seen a problem created by the System created
statisitics and what was the fix.
TIA
PatriciaParticia
What's version are you using?
Havu you ran DBCC SHOW_STATISTICS?
Also take a look at sp_createstats system stored procedure
"Patricia" <Patricia@.discussions.microsoft.com> wrote in message
news:2E80BF08-3146-4749-8142-CC7A7BC1F56D@.microsoft.com...
> Hello,
> We have had a problem with a table recently where a load had stopped half
> with through with an index problem.
> After a bit of delving the problem seems to be causes by the system
> created
> Statisitcs for a particial index or at least thats where the error message
> is
> pointing to.
> The database is set to auto update the statistics and we use this table to
> do a lot of loads, truncates and deletes.
> My question is this, has anyone seen a problem created by the System
> created
> statisitics and what was the fix.
> TIA
> Patricia

_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.

Tuesday, March 20, 2012

[varchar] (100) and empty space

Hello

I am creating the following table

CREATE TABLE [dbo].[myT] (
[name] [varchar] (100) NULL
)

if I do

INSERT INTO myT (name) VALUES ('A')

i get in the table a column with

A ...

the 100 char place is full even if there is a data with only 1 char

how is it possible to avoid it ?
I want 100 char maximum but not full with nothing

thank you for helpingWhat do you get when you run this?:

select len([Name]), '[' + [Name] + ']' from myT

Name is a reserved word, and so it is not a good label for a column, but I don't think this would cause the problem you are seeing.|||the 100 char place is full even if there is a data with only 1 charHow do you know?

Could this be a display issue of whatever client program you use to display the data?
Only CHAR columns are padded to the full length not VARCHAR columns|||I know it when I fill a formula with datas I am getting 99 empty spaces

blindman it is an exemple i have no column named [name]

if i run select datalength(name) from myT
i am getting 200 2 times (100)|||Strange I just ran all the scripts above & everything looks good to me

I get 1 from select datalength(name) from myT

anselme

take a deep breath reboot and start running these scripts again

If this does'nt work

Reply here and

stop using the word Char as in the 100 char place is full even if there is a data with only 1 char

explain what query editor you are using

explain what database you are using

run the script exactly as blindman suggests select len([Name]), '[' + [Name] + ']' from myT and tell us exactly the output

GW|||blindman it is an exemple i have no column named [name]You have some sort of typo, and if you expect any more help on this you need to post the actual code so we don't waste more of our time.|||I know it when I fill a formula with datas I am getting 99 empty spacesSQL Server does not have "formulas" to be "filled" (whatever that should mean).
What exactly are you doing?|||i agree with Gwilliy everything looks cool for me too....
varchar will only occupy the required number of space...
however if still problems persist you can always use LTRIM and RTRIM to get rid of the remaining whitespaces

so your query will be something like
select LTRIM(RTRIM([name])) from myT

thats the most we can get you....

However the fact that the 100 varchar place is full still mystifies me|||However the fact that the 100 varchar place is full still mystifies me
I suspect this is a front end issue.
What is this "formula" thing he is filling in?

[Transfer SQL Server Objects Task] Error: Table does not exist at the source.

Hello,

I am running a package that used to transfers data from one SQL2005 to another SQL2005. There are multiple schemas associated with the database. Until recently, this pacakage would work. Now I am getting the following error for all the tables not owned by dbo:

Any help on this would be appreciated.

Thanks, sck10

[Transfer SQL Server Objects Task] Error: Table "tblAudiocast" does not exist at the source.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600

May be it;s a direct error that in the source "tblAudiocast" table may not be exisiting or renamed!!!

Or check the connection strings if it's pointing to the correct data base!!

Dev

|||

Thanks Dev, but all the tables exist. We copy the schema from the production database to the local database, so they are duplicates. And it works for tables that belong to dbo only, but fails for all other tables.

sck10

|||

This thread describes the same problem - it's a known bug. It may have been fixed in SP2, but I'm not positive.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=207812&SiteID=1

|||

Thanks jwelch,

My SSIS package was working until recently.

sck10

sql

[Transfer SQL Server Objects Task] Error: Table does not exist at the source.

Hello,

Any help on this would be appreciated.

Thanks, sck10

[Transfer SQL Server Objects Task] Error: Table "tblAudiocast" does not exist at the source.

May be it;s a direct error that in the source "tblAudiocast" table may not be exisiting or renamed!!!

Or check the connection strings if it's pointing to the correct data base!!

Dev

|||

sck10

|||

This thread describes the same problem - it's a known bug. It may have been fixed in SP2, but I'm not positive.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=207812&SiteID=1

|||

Thanks jwelch,

My SSIS package was working until recently.

sck10

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the source

Does anyone know what could be causing the error on Transfer SQL Server Objects Task? I tried to develope a SSIS project in the Business Intelligence studio to transfer table between databases on the same server. However, I have been getting the following error:

[Transfer SQL Server Objects Task] Error: Table "XXXXXX" does not exist at the source.

Is there a setting that I need to change to make this work? Thank you for your help.

Is the table you've specified to move on the task in the source database?|||

Yes. It is in there. I can see it in the selection list after I select the database. Thanks.

And Transfer SQL Server Objecdts Task is the only task in that SSIS package.

|||I think your problem is related to the schema of the table you are trying to copy.

I created table "testtable" in testdb1. The schema was "dbo". I could transfer this table to another database named testdb2 in the same server using Transfer SQL Server Objects Task.

Then, I changed the schema of "testtable" to "guest". When I executed the same task, I got the error message "[Transfer SQL Server Objects Task] Error: Table "testtable" does not exist at the source. "|||

Thanks for your response. Where di you create the "Transfer SQL Server Objects Task"? Is it under control flow or data flow? When I had the error to only task I had was the "Transfer SQL SErver Obejcts Task" under Control Flow.

Thanks.

|||Transfer SQL Server Objects is a Control Flow task. I had my task in the control flow tab and the task I have explained above was the only task I had in the package.|||I had the exact issue when I transfer the data (both table and xml schema) from my dev. box to the production server. It seemed to me that this particular control task recognizes only the dbo schema. Is this bug fixed in SP1?|||No. We are looking to fix this issue in one of the future releases.|||I am trying to figure out exactly what the issue is here -- can we not copy tables from one database to another at all at this time? I had both tables as dbo schemas, and I had a different error, this time about the destination not being available. I set the schema owner of the tables to be the same as the authenticated user, and then I received the error that it does not exist at the source.

At this time (10/2006), is there any way to copy tables from one server to another via SSIS? I would think this basic of a task would be the first to work on a newer DTS... ?

Thanks in advance

-Chris Rasmussen|||

I am hoping someone will answer the last question. I cannot beleive you would release a product with such a simple task not working. As a workaround, I suppose I can create an SMO task to handle this, but that is more development time than I had expected to use. Please fix this with your NEXT release, not an undetermined FUTURE release. This type of missing functionality is hard to point at and convince anyone in the position of making decisions to migrate to 2005. Come on guys and gals.

Your frustrated supporter...

|||

Hi Cliff!

I'd like to share my frustration with Transfer SQL Server Object Task. I am trying to do something very simple - transfer 3 tables with primary/foreign keys, referential ingegrity and some data and spent hours of fruitless tweaking of the task. If someone offers a very simple step by step process on how to configure the "Transfer SQL Server Object task" to do that, I would be very grateful.

|||Are you planning on fixing this in SP2?|||Hi

I'd like to add my vote for this feature request. Can someone from the dev team tell if the decision of fixing this has already been taken somehow ?

Any insight will be most welcome, and will help us pick the right solution.

Thibaut Barrère|||

Hi,

I am also having problems with the "Transfer SQL Server Objects" task. It simply does not work.

The editor interface is sweet, however, and looks very promising. My issue is that I cannot debug this task. I cannot see the code that is generated by the task, and logging does not reveal what I want: the SQL code or whatever code is generated by the task. I want to see the actual table name syntax contained in the resultant command sent over, with the assumption that there is something wrong there.

The documentation is very clear, promising effective results. The reality is different, however. This is simply a management issue. The product was released too early, and the testing regimen must have been inadequate or mismatched with the document.

I have the Evaluation Version of Enterprise. The SMO connectors all test properly, and there is no other problem. The wizard works fine from SSMS, and when I save the output from the wizard to a package, the package does not use the "Transfer SQL Server Objects" task.

So, guys, what we have here is a defect.

This is very annoying, and I will write a letter to Bill Gates requesting immediate attention to this issue.

|||The fix is not released yet.

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the source

[Transfer SQL Server Objects Task] Error: Table "XXXXXX" does not exist at the source.

Is there a setting that I need to change to make this work? Thank you for your help.

Is the table you've specified to move on the task in the source database?|||

Yes. It is in there. I can see it in the selection list after I select the database. Thanks.

And Transfer SQL Server Objecdts Task is the only task in that SSIS package.

|||I think your problem is related to the schema of the table you are trying to copy.

I created table "testtable" in testdb1. The schema was "dbo". I could transfer this table to another database named testdb2 in the same server using Transfer SQL Server Objects Task.

Thanks.

At this time (10/2006), is there any way to copy tables from one server to another via SSIS? I would think this basic of a task would be the first to work on a newer DTS... ?

Thanks in advance

-Chris Rasmussen
|||

Your frustrated supporter...

|||

Hi Cliff!

|||Are you planning on fixing this in SP2?|||Hi

I'd like to add my vote for this feature request. Can someone from the dev team tell if the decision of fixing this has already been taken somehow ?

Any insight will be most welcome, and will help us pick the right solution.

Thibaut Barrère

|||

Hi,

I am also having problems with the "Transfer SQL Server Objects" task. It simply does not work.

So, guys, what we have here is a defect.

This is very annoying, and I will write a letter to Bill Gates requesting immediate attention to this issue.

|||The fix is not released yet.

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the so

[Transfer SQL Server Objects Task] Error: Table "XXXXXX" does not exist at the source.

Is there a setting that I need to change to make this work? Thank you for your help.

Is the table you've specified to move on the task in the source database?|||

Yes. It is in there. I can see it in the selection list after I select the database. Thanks.

And Transfer SQL Server Objecdts Task is the only task in that SSIS package.

|||I think your problem is related to the schema of the table you are trying to copy.

I created table "testtable" in testdb1. The schema was "dbo". I could transfer this table to another database named testdb2 in the same server using Transfer SQL Server Objects Task.

Thanks.

At this time (10/2006), is there any way to copy tables from one server to another via SSIS? I would think this basic of a task would be the first to work on a newer DTS... ?

Thanks in advance

-Chris Rasmussen|||

Your frustrated supporter...

|||

Hi Cliff!

|||Are you planning on fixing this in SP2?|||Hi

I'd like to add my vote for this feature request. Can someone from the dev team tell if the decision of fixing this has already been taken somehow ?

Any insight will be most welcome, and will help us pick the right solution.

Thibaut Barrère|||

Hi,

I am also having problems with the "Transfer SQL Server Objects" task. It simply does not work.

So, guys, what we have here is a defect.

This is very annoying, and I will write a letter to Bill Gates requesting immediate attention to this issue.

|||The fix is not released yet.

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the so

[Transfer SQL Server Objects Task] Error: Table "XXXXXX" does not exist at the source.

Is there a setting that I need to change to make this work? Thank you for your help.

Is the table you've specified to move on the task in the source database?|||

Yes. It is in there. I can see it in the selection list after I select the database. Thanks.

And Transfer SQL Server Objecdts Task is the only task in that SSIS package.

|||I think your problem is related to the schema of the table you are trying to copy.

I created table "testtable" in testdb1. The schema was "dbo". I could transfer this table to another database named testdb2 in the same server using Transfer SQL Server Objects Task.

Thanks.

At this time (10/2006), is there any way to copy tables from one server to another via SSIS? I would think this basic of a task would be the first to work on a newer DTS... ?

Thanks in advance

-Chris Rasmussen|||

Your frustrated supporter...

|||

Hi Cliff!

|||Are you planning on fixing this in SP2?|||Hi

I'd like to add my vote for this feature request. Can someone from the dev team tell if the decision of fixing this has already been taken somehow ?

Any insight will be most welcome, and will help us pick the right solution.

Thibaut Barrère|||

Hi,

I am also having problems with the "Transfer SQL Server Objects" task. It simply does not work.

So, guys, what we have here is a defect.

This is very annoying, and I will write a letter to Bill Gates requesting immediate attention to this issue.

|||The fix is not released yet.sql

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the so

[Transfer SQL Server Objects Task] Error: Table "XXXXXX" does not exist at the source.

Is there a setting that I need to change to make this work? Thank you for your help.

Is the table you've specified to move on the task in the source database?|||

Yes. It is in there. I can see it in the selection list after I select the database. Thanks.

And Transfer SQL Server Objecdts Task is the only task in that SSIS package.

|||I think your problem is related to the schema of the table you are trying to copy.

I created table "testtable" in testdb1. The schema was "dbo". I could transfer this table to another database named testdb2 in the same server using Transfer SQL Server Objects Task.

Thanks.

At this time (10/2006), is there any way to copy tables from one server to another via SSIS? I would think this basic of a task would be the first to work on a newer DTS... ?

Thanks in advance

-Chris Rasmussen|||

Your frustrated supporter...

|||

Hi Cliff!

|||Are you planning on fixing this in SP2?|||Hi

I'd like to add my vote for this feature request. Can someone from the dev team tell if the decision of fixing this has already been taken somehow ?

Any insight will be most welcome, and will help us pick the right solution.

Thibaut Barrère|||

Hi,

I am also having problems with the "Transfer SQL Server Objects" task. It simply does not work.

So, guys, what we have here is a defect.

This is very annoying, and I will write a letter to Bill Gates requesting immediate attention to this issue.

|||The fix is not released yet.

[Transfer SQL Server Objects Task] Error: Table "MySchema.MyTableName" does not ex

[Transfer SQL Server Objects Task] Error: Table "XXXXXX" does not exist at the source.

Is there a setting that I need to change to make this work? Thank you for your help.

Is the table you've specified to move on the task in the source database?|||

Yes. It is in there. I can see it in the selection list after I select the database. Thanks.

And Transfer SQL Server Objecdts Task is the only task in that SSIS package.

|||I think your problem is related to the schema of the table you are trying to copy.

I created table "testtable" in testdb1. The schema was "dbo". I could transfer this table to another database named testdb2 in the same server using Transfer SQL Server Objects Task.

Thanks.

At this time (10/2006), is there any way to copy tables from one server to another via SSIS? I would think this basic of a task would be the first to work on a newer DTS... ?

Thanks in advance

-Chris Rasmussen|||

Your frustrated supporter...

|||

Hi Cliff!

|||Are you planning on fixing this in SP2?|||Hi

I'd like to add my vote for this feature request. Can someone from the dev team tell if the decision of fixing this has already been taken somehow ?

Any insight will be most welcome, and will help us pick the right solution.

Thibaut Barrère|||

Hi,

I am also having problems with the "Transfer SQL Server Objects" task. It simply does not work.

So, guys, what we have here is a defect.

This is very annoying, and I will write a letter to Bill Gates requesting immediate attention to this issue.

|||The fix is not released yet.

[Transfer SQL Server Objects Task] Error: Table "MySchema.MyTableName" does not ex

[Transfer SQL Server Objects Task] Error: Table "XXXXXX" does not exist at the source.

Is there a setting that I need to change to make this work? Thank you for your help.

Is the table you've specified to move on the task in the source database?|||

Yes. It is in there. I can see it in the selection list after I select the database. Thanks.

And Transfer SQL Server Objecdts Task is the only task in that SSIS package.

|||I think your problem is related to the schema of the table you are trying to copy.

I created table "testtable" in testdb1. The schema was "dbo". I could transfer this table to another database named testdb2 in the same server using Transfer SQL Server Objects Task.

Thanks.

At this time (10/2006), is there any way to copy tables from one server to another via SSIS? I would think this basic of a task would be the first to work on a newer DTS... ?

Thanks in advance

-Chris Rasmussen|||

Your frustrated supporter...

|||

Hi Cliff!

|||Are you planning on fixing this in SP2?|||Hi

I'd like to add my vote for this feature request. Can someone from the dev team tell if the decision of fixing this has already been taken somehow ?

Any insight will be most welcome, and will help us pick the right solution.

Thibaut Barrère|||

Hi,

I am also having problems with the "Transfer SQL Server Objects" task. It simply does not work.

So, guys, what we have here is a defect.

This is very annoying, and I will write a letter to Bill Gates requesting immediate attention to this issue.

|||The fix is not released yet.

[Table Partitioning] What is the best method?

Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Lewhy do you want to partition them in the first place? Just because they have millions of rows?

Are these tables transactional, or do you only read from them? If you are only reading, then you can add appropriate indexes without worrying about hurting insert/update/delete perf (because you don't do any inserts/updates/deletes) and don't partition them at all.

In my job I work with tables in sql server that have 100's of millions of rows without problems (not transactional though) - the important thing is to make sure the indexes are correct. also you need decent disks of course. :)|||Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Le

I have only tested functions/ schemes in 2005, but from my testing and reading I havent seen anything that would indicate 10 would be too many. Test it out. You need to realize that this whole concept/ feature is new to 2005, so you are blazing the path for the rest of us to follow. ;)

Monday, March 19, 2012

[SQL2k5] Dynamic SQL Query Select on all user tables

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.

Forgot, for every table it selects from, the table name must be one of the returned columns.|||

Hi, try the following:

sp_msforeachtable 'SELECT *,''?'' AS TableName FROM (SELECT TOP (3) , COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
FROM ?
GROUP BY [Target IP]
ORDER BY EventNum DESC) SubQuery'

But keep in mind that this should be just used for administrative task and adhoc queries not for business logic as the procedure is not documented, therefore not supported and could be deprecated in the next version of SQL Server.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

[SQL2005] Binding defaults without using sp_bindefault

The SQL2005 documentation states the sp_bindefault will be gone in the next
version and the DEFAULT keyword should be used with CREATE TABLE or ALTER
TABLE statements. But I seem to have run into a situation where that only th
e
stored procedure approach works.
script 1:
CREATE DEFAULT abc AS 1
script 2:
CREATE TABLE xyz(
id int IDENTITY(1,1) NOT NULL,
thingy int DEFAULT abc)
This will result in the following error: "The name "abc" is not permitted in
this context. Valid expressions are constants, constant expressions, and (in
some contexts) variables. Column names are not permitted."
It works perfectly when I use the sp_bindefault procedure instead, so I do
have a solution, but can it be done without? I am probably missing something
very simple, so any help is welcome. TIA!Check documentation for CREATE DEFAULT and you will see that this will also
disappear. What BOL is
trying to say is that default objects will disappear, while default constrai
nts is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:F6F545C2-D304-46BA-A5D4-DC07660BAECE@.microsoft.com...
> The SQL2005 documentation states the sp_bindefault will be gone in the nex
t
> version and the DEFAULT keyword should be used with CREATE TABLE or ALTER
> TABLE statements. But I seem to have run into a situation where that only
the
> stored procedure approach works.
> script 1:
> CREATE DEFAULT abc AS 1
> script 2:
> CREATE TABLE xyz(
> id int IDENTITY(1,1) NOT NULL,
> thingy int DEFAULT abc)
> This will result in the following error: "The name "abc" is not permitted
in
> this context. Valid expressions are constants, constant expressions, and (
in
> some contexts) variables. Column names are not permitted."
> It works perfectly when I use the sp_bindefault procedure instead, so I do
> have a solution, but can it be done without? I am probably missing somethi
ng
> very simple, so any help is welcome. TIA!
>
>
>|||Don't bind defaults, ADD them:
alter table <table name>
add constraint abc
default (1)
for <column name>
ML
http://milambda.blogspot.com/|||I could do that, but the whole idea for me was to define a centralized
default, so when I change that, the changes will be reflected in every colum
n
binded to that default.
"ML" wrote:

> Don't bind defaults, ADD them:
> alter table <table name>
> add constraint abc
> default (1)
> for <column name>
>
> ML
> --
> http://milambda.blogspot.com/|||Is it possible to bind a single default constraint to multiple columns in
multiple tables? I.e. I have a date column in every table which should be
filled with a default value if none is provided.
At the moment I made a default for it and linked the name of the default to
the date column. In my database design tool, I only have to change the
default value in one place if I ever decide the current one is not right
anymore. Let the tool create a script for me and it is changed.
With constraints I have to change the default value in every single table
(200+), while I could do it with just one simple change when using
sp_bindefault was allowed. Or can it be done more easily?
"Tibor Karaszi" wrote:

> Check documentation for CREATE DEFAULT and you will see that this will als
o disappear. What BOL is
> trying to say is that default objects will disappear, while default constr
aints is the way to go.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
> news:F6F545C2-D304-46BA-A5D4-DC07660BAECE@.microsoft.com...
>|||As Tibor states in his post the default as a SQL object is on its last breat
h
(so to speak), as for the default as a constraint it cannot be reused as if
it were an object.
A constraint is a declaration, and if your requirement really is
object-oriented (rather than declarative) then maybe you could get what you
need by using a user-defined CLR type. But only for a default? I wouldn't go
down that road.
While being aware of the benefits a default provides as an object (e.g.
reusability), I still think a default is merely an attribute of the
individual column, and should be treated as such - kept in the meta world.
ML
http://milambda.blogspot.com/|||Ah hold on, I know what to do ;-)
Currently I have a UDDT for a mandatory date column and bind a default to it
when I need to. The solution is to create two UDDTs, one with and one withou
t
the constraint. It's gonna be some work to convert 200+ tables, but it's a
solution ...
"PaulSand" wrote:
> Is it possible to bind a single default constraint to multiple columns in
> multiple tables? I.e. I have a date column in every table which should be
> filled with a default value if none is provided.
> At the moment I made a default for it and linked the name of the default t
o
> the date column. In my database design tool, I only have to change the
> default value in one place if I ever decide the current one is not right
> anymore. Let the tool create a script for me and it is changed.
> With constraints I have to change the default value in every single table
> (200+), while I could do it with just one simple change when using
> sp_bindefault was allowed. Or can it be done more easily?
>
> "Tibor Karaszi" wrote:
>|||Oops, sorry for making a mess here, but that isn't a solution either ...
"PaulSand" wrote:
> Ah hold on, I know what to do ;-)
> Currently I have a UDDT for a mandatory date column and bind a default to
it
> when I need to. The solution is to create two UDDTs, one with and one with
out
> the constraint. It's gonna be some work to convert 200+ tables, but it's a
> solution ...
> "PaulSand" wrote:
>|||Mess? Aren't we learning anymore? :)
ML
http://milambda.blogspot.com/|||> Is it possible to bind a single default constraint to multiple columns in
> multiple tables?
No. In short:
Default objects are deprecated. Those does what you want to do.
Default constraints are supported and recommended way in the future. But you
create them at the
column level, so they cannot be defined-one-used-many.
You might want to post a wish to [url]http://lab.msdn.microsoft.com/productfeedback/.[/
url] The ANSI SQL
feature you are looking for is the "DOMAIN" (CREATE/ALTER DOMAIN), but that
is not implemented in
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:6D87E279-5C8F-4BB6-9394-28454BD4B89D@.microsoft.com...
> Is it possible to bind a single default constraint to multiple columns in
> multiple tables? I.e. I have a date column in every table which should be
> filled with a default value if none is provided.
> At the moment I made a default for it and linked the name of the default t
o
> the date column. In my database design tool, I only have to change the
> default value in one place if I ever decide the current one is not right
> anymore. Let the tool create a script for me and it is changed.
> With constraints I have to change the default value in every single table
> (200+), while I could do it with just one simple change when using
> sp_bindefault was allowed. Or can it be done more easily?
>
> "Tibor Karaszi" wrote:
>

[Sql2000] how to limit table size

hi all,
I checked out in google groups but was not able to find
the answer...
is it possible to limit size table in sql server?
how can I do it?

TIAilkaos wrote:

Quote:

Originally Posted by

hi all,
I checked out in google groups but was not able to find
the answer...
is it possible to limit size table in sql server?
how can I do it?
>
TIA

Add the table to some filegroup and then fix the total size of files in
that filegroup:

CREATE TABLE tbl
(... ) ON filegroup_xyz;

Take a look at ALTER DATABASE ... ADD FILEGROUP / FILE for more
details.

Alternatively, you could use constraints to limit the maximum number of
*rows* in a table (not necessarily comparable with the size of the
table in storage). The details of how to do this would depend on the
nature of the table and keys. For example:

/* Maximum 10,000 rows */
ALTER TABLE tbl ADD foo INT NOT NULL
CONSTRAINT ak_tbl_foo UNIQUE,
CONSTRAINT ck_tbl_foo CHECK (foo BETWEEN 1 AND 10000);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||

Quote:

Originally Posted by

>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>

thanks a lot for you (very quick!) answer.

Thursday, March 22, 2012

_WA_ indexes in sysindexes

_WA_ indexes in sysindexes

_WA_ indexes in sysindexes

_WA Statistics Error

_WA Statistics Error

_hypmv error

Tuesday, March 20, 2012

[varchar] (100) and empty space

[Transfer SQL Server Objects Task] Error: Table does not exist at the source.

[Transfer SQL Server Objects Task] Error: Table does not exist at the source.

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the source

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the source

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the so

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the so

[Transfer SQL Server Objects Task] Error: Table "XXXXXXX" does not exist at the so

[Transfer SQL Server Objects Task] Error: Table "MySchema.MyTableName" does not ex

[Transfer SQL Server Objects Task] Error: Table "MySchema.MyTableName" does not ex

[Table Partitioning] What is the best method?

Monday, March 19, 2012

[SQL2k5] Dynamic SQL Query Select on all user tables

[SQL2005] Binding defaults without using sp_bindefault

[Sql2000] how to limit table size

sql rowcount

Blog Archive

About Me