Showing posts with label reorder. Show all posts
Showing posts with label reorder. Show all posts

Saturday, February 25, 2012

Reorder the table definition of a SQL 2005 table

Hi,

Is it possible to reorder the table definition in a SQL 2005 table?

Example : i want to put to table definition in alphabetical order.

Thanks in advance

I dont understand. do you mean re-ordering of columns in the table?

|||

When i look at my table, i see that the columns are in this order :

CatId
SubCatId
ImageId
PhoneNumber
Adress
Fax
Cell

But if i want to have this order (alphabetical order) :

Adress
CatId
Cell
Fax
ImageId
PhoneNumber
SubCatId

How can i do that?

|||

The order of columns in a table does not matter. So does order of rows in a table.

You can always write your SELECT statement to return the rows in the column order you want.

SELECT Adress, CatId, Cell, Fax, ImageId, PhoneNumber, SubCatId

FROM YoutYable


|||

Ok but if i want to arrange the order of columns when i open the table with SQL Server Management Studio to see them in alphabetical order...

Can i do that?

|||

The bigger question is why? It is just a GUI thing.

|||

I know that!

It's just that i like my things to be ordered ;-)

So?

|||

Right click on the table --> Design view, you can drag the columns up or down.

|||

Ok Thanks!

Re-order table after many inserts

I have a stored proc that is long and performs many separate intserts into my table. Is there a way that I can reorder the records themselves physically in the table after the inserts are done?

Hello,

http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspx

I think this will help you!

Happy coding...

|||

Hi,

I don′t know if the article below is applying to your case, but this can be only done if you have a clustered key based on the tables, which is defining the physical o′rder of a key, and therefore the physical order of the table (based on x columns). But if you only want to sort the tables via a specific column, then you have to look a bit deeper in the specifications of a table. Its a unordered set of data, unless you don′t have a clustered sorted index on a it, you will only get the orderd data with querying the data with the ORDER clause.

HTH, Jens Suessmeyer.

reorder size of secondary data files?

Hello,
I have a database which secondary files are splitted into
4 pieces. Each Piece on a severall harddisk (18GB).
Now i have the following situation:
file1(.ndf)=5GB
file2(.ndf)=11GB
file3(.ndf)=17GB
file4(.ndf)=7GB
Is there a possibility to balance the filesize?
The harddisk for file3 is at the limit and doesn't spend
any performance!!!
10GB for Each file and a balanced growth would be nice!!!
Thanks a lot
CharibertYou can shrink the bigger files so that data from it gets "pushed" over to the smaller file (I
suggest you pre-increase the size of the smaller files before this).
Above is only way I can think of, unless you want to go through unload/import route.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Charibert Greif" <Greif@.gob.de> wrote in message news:44f401c376af$0ebe9030$a301280a@.phx.gbl...
> Hello,
> I have a database which secondary files are splitted into
> 4 pieces. Each Piece on a severall harddisk (18GB).
> Now i have the following situation:
> file1(.ndf)=5GB
> file2(.ndf)=11GB
> file3(.ndf)=17GB
> file4(.ndf)=7GB
> Is there a possibility to balance the filesize?
> The harddisk for file3 is at the limit and doesn't spend
> any performance!!!
> 10GB for Each file and a balanced growth would be nice!!!
> Thanks a lot
> Charibert|||Have you tried DBCC SHRINKFILE
For more details please refer to BOL
"Charibert Greif" <Greif@.gob.de> wrote in message
news:44f401c376af$0ebe9030$a301280a@.phx.gbl...
> Hello,
> I have a database which secondary files are splitted into
> 4 pieces. Each Piece on a severall harddisk (18GB).
> Now i have the following situation:
> file1(.ndf)=5GB
> file2(.ndf)=11GB
> file3(.ndf)=17GB
> file4(.ndf)=7GB
> Is there a possibility to balance the filesize?
> The harddisk for file3 is at the limit and doesn't spend
> any performance!!!
> 10GB for Each file and a balanced growth would be nice!!!
> Thanks a lot
> Charibert|||Hello,
I have noticed that SQL server tries to automatically
balance infill of the files in filegroup.
Recently I've added one file to each filegroup in
my DB but existed files weren't filled completely and
now I see that server insert new data into the new
files only and execution of INDEXDEFRAG is actively
moving data into new files!
So, in your case, it may be useful to increase size of
some files, run some reorganisation work (rebuilding
indexes is the best way), and later shrink files which
will get free space.
> I have a database which secondary files are splitted into
> 4 pieces. Each Piece on a severall harddisk (18GB).
> Now i have the following situation:
> file1(.ndf)=5GB
> file2(.ndf)=11GB
> file3(.ndf)=17GB
> file4(.ndf)=7GB
> Is there a possibility to balance the filesize?
> The harddisk for file3 is at the limit and doesn't spend
> any performance!!!
> 10GB for Each file and a balanced growth would be nice!!!
Serge Shakhov

Reorder packages in BIDS

How or is there a way to reorder the packages in BIDS? Right now they are ordered by when they are created with the newest ones on the bottom. I'd like to organize these in more of a logical order but am unsure of how to do so.

Any advise?

Thanks,

Beac

I have downloaded/used this without a problem; it only gives you the ability of sorting the items alphabetically:

http://www.sqldbatips.com/showarticle.asp?ID=78

|||Thanks for the replay Rafael. I'll give it a try.

Reorder my rows

Howdie y'all,

Im adding rows to my database like this:

GroupID = Identity
GroupUser = UserId
GroupColumn = 1
GroupPosition = Count (*) where UserId = UserId AND Column = 1

Int this way my table looks like this on a certain moment:

1 ; 1 ; 1 ; 0
2 ; 1 ; 1 ; 1
3 ; 1 ; 1 ; 2
4 ; 1 ; 1 ; 3

But then i would, for example, like to delete the 3rd row and i like to renumber the last column so form this:
1 ; 1 ; 1 ; 0
2 ; 1 ; 1 ; 1
4 ; 1 ; 1 ; 3 <- this is my problem

to this:
1 ; 1 ; 1 ; 0
2 ; 1 ; 1 ; 1
4 ; 1 ; 1 ; 2 <- here it is solved

Whats the simplest sql stored procedure to perform this reordering task?

Thanks,

Wes

Why not use the identity column to generate the 'position' at runtime?
SELECT
GroupID,
GroupUser,
GroupColumn,
COUNT(*) AS Position
FROM YourTable y1
JOIN YourTable y2 ON y2.GroupID <= y1.GroupID
GROUP BY
GroupID,
GroupUser,
GroupColumn
ORDER BY
Position
|||

Thanks you for your fast reply!

Some people would like to have group number 1 on the 3rd place or order them by groupname(not mentioned in the previous post)... they can do so by moving them up or down by changing the position or by clicking the order by name button...

So it might look like this before renumbering the position:
1 ; 1 ; 1 ; 0
4 ; 1 ; 1 ; 1
2 ; 1 ; 1 ; 3

So I really would like to renumber the Position field to cut out the 'empty' rows...

Can I loop through each record in an sql procedure?(sorry I'm such a newbie on sql-server... I've worked with small MSAccess databases only!!!!)

Thanks for thinking along with me!

Wesley

|||

I think I'm getting there...... I just get one ANNOYING ERROR! With this:

CREATE PROCEDURE [dbo].[spRenumberUserColumnGroups]
@.GroupColumn TINYINT,
@.GroupUserId INT
AS
UPDATE tblGroups
SET GroupPosition = ((SELECT COUNT(*) FROM tblGroups gr2 WHERE gr2.GroupPosition <= GroupPosition AND gr2.GroupUserId = @.GroupUserId AND gr2.GroupColumn = @.GroupColumn)-1)
WHERE GroupUserId = @.GroupUserId AND GroupColumn = @.GroupColumn
ORDER BY GroupPosition;
GO

I get this: Incorrect syntax near the keyword 'order'

WHY???Hmm [^o)] I'm starting to cryCrying [:'(] simply because I don't have a clue whats wrong with this ORDER BY. GroupName or GroupId all gives me the same nasty error. The moment I drop the ORDER BY it compiles just fine!!!!

I HATE SQL SERVER!!!Super Angry [8o|] Please help me...

Cheers,

Wes

|||You can't order an UPDATE. Simple as that. There is noguarantee what order the rows will be updated in. I haven'tstudied your code in any detail, so I don't know if you're depending onthat. If so, don't!Big Smile [:D]
Also, think about concurrency -- what happens if two people removedifferent rows at the same time? Will this still result in acorrect answer? Make sure to think about that -- there may besome traps you haven't thought of.
|||

Thanks all for thinking along... after a long search and combining several examples into one I got to this and for know it works like a charm.

CREATE PROCEDURE [dbo].[spRenumberGroups]
@.GroupColumn TINYINT,
@.GroupUserId INT
AS
CREATE TABLE #RowNumber (
RowNumber int IDENTITY (0, 1),
GroupId int )

INSERT #RowNumber (GroupId)
SELECT GroupId
FROM tblGroups
WHERE GroupUserId = @.GroupUserId AND GroupColumn = @.GroupColumn
ORDER BY GroupPosition

UPDATE tblGroups
SET GroupPosition = #RowNumber.RowNumber
FROM tblGroups, #RowNumber
WHERE tblGroups.GroupId = #RowNumber.GroupId

DROP TABLE #RowNumber
GO

Cheers,

Wes