sql database integrity: populate

Showing posts with label populate. Show all posts

Tuesday, March 20, 2012

Creating Tables Dynamically

Hello!
I am looking for a simple routine to create and populate tables dynamically.
I get an error message that says @.tn has a problem. What could be the reason
:
declare @.table_name varchar(500)
declare @.query nvarchar(500)
declare @.parmdefinition nvarchar(500)
set @.query = 'create table @.tn (user_id nvarchar,event_id nvarchar)'
set @.table_name = 'test'
set @.parmdefinition = '@.tn varchar(500),@.query varchar(500)'
execute sp_executesql @.query, @.parmdefinition, @.tn = @.table_name
Any clues are very welcome. Thank you very much!
-BahmanYou need to declare @.tn.
Archer
"Bahman" wrote:

> Hello!
> I am looking for a simple routine to create and populate tables dynamicall
y.
> I get an error message that says @.tn has a problem. What could be the reas
on:
> declare @.table_name varchar(500)
> declare @.query nvarchar(500)
> declare @.parmdefinition nvarchar(500)
> set @.query = 'create table @.tn (user_id nvarchar,event_id nvarchar)'
> set @.table_name = 'test'
> set @.parmdefinition = '@.tn varchar(500),@.query varchar(500)'
> execute sp_executesql @.query, @.parmdefinition, @.tn = @.table_name
> Any clues are very welcome. Thank you very much!
> -Bahman
>|||@.tn is redundant. You can't parameterize a table name, even within
sp_executesql. Try:
declare @.table_name varchar(500)
declare @.query nvarchar(500)
declare @.parmdefinition nvarchar(500)
set @.table_name = 'test'
set @.query = 'create table '+@.table_name+' (user_id nvarchar,event_id
nvarchar)'
execute sp_executesql @.query
sp_executesql is redundant as well. You could have used EXEC.
The obvious question is why would you want to create tables
dynamically? Especially since the metadata is static in this example.
David Portas
SQL Server MVP
--|||Bagman3rd:
No, it still won't work. Does sp_executesql look for parameters only in the
where clause?
"bagman3rd" wrote:
> You need to declare @.tn.
> Archer
> "Bahman" wrote:
>|||>> I am looking for a simple routine to create and populate tables
Can you elaborate on why you want to do this? In most cases, such
requirements stem from poorly designed systems or people trying to come up
with short-cuts to avoid coding.
The solution for creating a table dynamically is to build a string the exact
CREATE TABLE script and execute it with EXEC or sp_ExecuteSQL. With
sp_ExecuteSQL you can only substitute variables not literal segments of a
DDL statement, which you seem to be doing here. So one way to write this
would be:
SET @.table_name = 'test'
SET @.parmdefinition = '( tn varchar(500),query varchar(500) )'
SET @.query = 'create table ' + @.table_name + @.parmdefinition
EXEC sp_executesql @.query
Before starting out, consider reading some implications at:
http://www.sommarskog.se/dynamic_sql.html
Anith|||Dave,
You are correct.
What I am looking for is the exact image of one particular table every hour
or so.
I am not looking for transaction logs, or sql statements, or monitor
traffic. All I want to know is what exactly is the content of the table at a
given time.
In MSSql you can backup the database or the trx log. But you cnnot create
images of individual tables that you can revert to on a moment's notice.
In this particular table, users can both add and delete records. Obviously,
they can only delete their own records. But to be safe, I need an image of
the content of the table itself, since add/delete permissions are not
'layered', e.g., once you have the right to 'delete', that right in not
strictly associated with what you entered yourself. That right is also
associated with what everyone else entered. Same with update.
There are other issues here as well. But the above are the basic stuff.
Thank you very much for your help.
-Bahman
"David Portas" wrote:

> @.tn is redundant. You can't parameterize a table name, even within
> sp_executesql. Try:
> declare @.table_name varchar(500)
> declare @.query nvarchar(500)
> declare @.parmdefinition nvarchar(500)
> set @.table_name = 'test'
> set @.query = 'create table '+@.table_name+' (user_id nvarchar,event_id
> nvarchar)'
> execute sp_executesql @.query
> sp_executesql is redundant as well. You could have used EXEC.
> The obvious question is why would you want to create tables
> dynamically? Especially since the metadata is static in this example.
> --
> David Portas
> SQL Server MVP
> --
>|||That's certainly an original one! Seriously, the problem is a common
one and there are various standard solutions. You don't need to copy
tables around dynamically. In fact it's hard to imagine how such a
thing could be practical let alone scalable.
Take a look at the following article on row-level security. More simply
you could just implement "logical" deletes - i.e. use a proc that marks
rows as deleted rather than actually deleting them.
http://vyaskn.tripod.com/ row_level...as
es.htm
David Portas
SQL Server MVP
--|||David,
That solution is pretty much what I am already doing. It is not robust thoug
h.
As you said, when you 'scale up' the database, you cannot create a separate
account for everyone who logs in to your website. So, the same account is
adding and deleting stuff from the same table all over the place. The
'session' variable that you have in the web application is not linked to the
'session' that you have on the Sequel. The two exist in two separate domains
.
Hence, the sequel does not know 'who' is 'on' right now judging from the
calling application, which is dont net.
That is just what our code is for. But some of it should be off-loaded to
the sequel side. The calling application should not be in charge of
everything.
In Windows 2003 you have a feature called 'shadow copy'. That is not the
same as tape backup. To me, it is a form of versioning. Good enough. In
sequel, you have the trx that you can apply after you recreated the database
from the backup. But there is nothing in the trx or the backup that tells yo
u
what the difference is between the state of an individual table that you see
there and the one you backed up earlier (unless you like lots of
programming). There is a 'disconnect' here.
MS Sql is basically treated as a data dump. There are cases where it has
very limited functionality. A simple trigger that I put in increases respons
e
time by a second. The server is not designed to be 'active'. And there is no
fine granule for treating data and data relationships. Hence the need to cod
e
a lot.
Thank you!
"David Portas" wrote:

> That's certainly an original one! Seriously, the problem is a common
> one and there are various standard solutions. You don't need to copy
> tables around dynamically. In fact it's hard to imagine how such a
> thing could be practical let alone scalable.
> Take a look at the following article on row-level security. More simply
> you could just implement "logical" deletes - i.e. use a proc that marks
> rows as deleted rather than actually deleting them.
> http://vyaskn.tripod.com/ row_level...as
es.htm
> --
> David Portas
> SQL Server MVP
> --
>|||>> What I am looking for is the exact image of one particular table every ho
ur or so. <<
I know of one ACCESS system that crashes so often, they do this. It
says that you need a better solution. You will have to do this in
hardware to get the exact image on a second identical disk drive.
Weird request -- most of us are happy with snapshots or generatianl
concurrency control.

Sunday, March 11, 2012

Creating rows based on date range from another table

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
>
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
><...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
><...>
200602 2005-12-18 75
>
CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
>
INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)

Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0

Given this table, we can write this query:

SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS
DATETIME),CAST('2005-10-03' AS DATETIME), CAST('2005-12-18' AS
DATETIME))
AH! Finally got this to work:
INSERT INTO tblDates VALUES ('200505' ,convert(datetime,
'2005-04-12'),convert(datetime,'2005-07-05'),
convert(datetime,'2005-09-12' ))

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
You solution works, which I am appreciative of, tho it will take me
working with the code to figure out why :)
Thanks for teaching me something new!
Rob

Erland Sommarskog wrote:

Quote:

Originally Posted by

rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

>
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
>
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
>
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
>
Given this table, we can write this query:
>
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS

Yes, the above format could fail. There are three date formats in SQL
Server that are safe:

YYYYMMDD
YYYYMMDDTHH:MM:SS[.fff]
YYYY-MM-DDZ

Here T and Z stand for themselves.

Other formats are interpretated depending on DATEFORMAT and LANGUAGE
setting, and can fail or produced unexpected results if you don't know
what is going on.

Quote:

Originally Posted by

Yes, that dates table is essentially a table of numbers with a different
names. In fact, it appears that it has all the numbers as well!

I used a table of numbers, as numbers is the more general concept and
can be used in more places. But in fact, I added a table of dates to
our system before I added a table of numbers.

I leave it as an exercise to you how to use the dates table instead.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sun, 20 Aug 2006 12:38:11 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to build a table based on values from another table.
>I need to populate a table between two dates from another table. Using
>the START_DT and END_DT, create records between those dates.
>I need a new column that is the days between the date and the MID_DT
>The data I wish to end with would look something like this:
>>
>PERIOD DATE DAY_NO
>200602 2005-07-06 -89
>200602 2005-07-07 -88
>200602 2005-07-08 -87
>><...>
>200602 2005-10-02 -2
>200602 2005-10-03 -1
>200602 2005-10-04 0
>200602 2005-10-05 1
>><...>
>200602 2005-12-18 75

[snip]

Quote:

Originally Posted by

Quote:

Originally Posted by

>INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)

Quote:

Originally Posted by

>Anyway, as I said in another newsgroup, you need a table of numbers. Here
>is a way to create such a table with a million numbers:

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

The one risk with a table of numbers is that if you run of numbers, you
will get an incorrect result. That is one reason why I'm reluctant to
use it, if there are alternative solutions. But for a case like this,
when you need to fill up a space, a table of numbers - or dates - is what
you need.

A loop is more complex to program, and easier go wrong. And as a generic
solution, you face scalability problems.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 21 Aug 2006 08:05:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What are the pros and cons of relying on such a table vs. using a
>WHILE loop? Based on Rob's context of student registrations, let's
>assume we're talking about a maximum of 300 iterations per row in
>the original tblDates table.

>
>The one risk with a table of numbers is that if you run of numbers, you
>will get an incorrect result. That is one reason why I'm reluctant to
>use it, if there are alternative solutions. But for a case like this,
>when you need to fill up a space, a table of numbers - or dates - is what
>you need.
>
>A loop is more complex to program, and easier go wrong.

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:

x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while

versus

select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)

Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Quote:

Originally Posted by

And as a generic
>solution, you face scalability problems.

I kind of figured. The query seems easy to get wrong, though, if
you're not familiar with the pattern; I first wrote it as "where
dateadd(first_date, n) between first_date and last_date", but that
seems like it'd be a good bit slower.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:
>
x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while
>
versus
>
select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)
>
Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Loops are particularly prone to two sorts of errors:

* They goes on forever, could be because of a sloppy mistake, of because the
logic is complicated.
* One-off errors because of incorrect loop conditions.

One-off errors are easy to make with set-based queries as well, but the
risk of infinite loops is nothing you have to lose sleep over.

Sunday, February 19, 2012

Creating index in a large table

Hello:
We have a table with millions of records. Not long ago, we added a new
column to this large table, and we like to populate this new column with data
and create an index for it.
Would there be any time difference if we create an index (non-clustered)
before we populate it with data, or after we populate it with data? In
another word, is it true that it takes lesser time to create an index in a
column with only null?
Thanks,
QQ:
I would hazard a guess that you are better off to populate the new column
with data and then create the non-clustered index rather than the other way
around. (Assumption: you have an existing Clustered Index on the table that
is hopefully on an integer field)
Rationale: When you create a non-clustered index on a table that has a
clustered index you create a B-Tree structure that has the cluster keys.
If you create the index and then populate, you are making the updates work
harder because it has to update the leaf level of the clustered index (where
the actual data is) as well as insert or manipulate the non-clustered index.
As updates occur your index will have page splits and the index pages will
become fragmented.
If you populate and then create the index you will create the index B-Tree
structure all in one shot and it will not be fragmented.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Q" <Q@.discussions.microsoft.com> wrote in message
news:A2B654F6-B13D-40E2-A7DA-852058447BE4@.microsoft.com...
> Hello:
> We have a table with millions of records. Not long ago, we added a new
> column to this large table, and we like to populate this new column with
> data
> and create an index for it.
> Would there be any time difference if we create an index (non-clustered)
> before we populate it with data, or after we populate it with data? In
> another word, is it true that it takes lesser time to create an index in a
> column with only null?
> Thanks,
> Q|||Hello David:
Thanks for your detail reply. Good to know that it would create more work
if there is already a clustered index and then if we add a new column, a
non-clustered index to the new column, then populate data to this new column.
There is no clustered index in this table. So can I assume there is no
overhead in insert/update, so there will not be fragmentation in split index
page?
Thanks
Q
"David Lundell" wrote:
> Q:
> I would hazard a guess that you are better off to populate the new column
> with data and then create the non-clustered index rather than the other way
> around. (Assumption: you have an existing Clustered Index on the table that
> is hopefully on an integer field)
> Rationale: When you create a non-clustered index on a table that has a
> clustered index you create a B-Tree structure that has the cluster keys.
> If you create the index and then populate, you are making the updates work
> harder because it has to update the leaf level of the clustered index (where
> the actual data is) as well as insert or manipulate the non-clustered index.
> As updates occur your index will have page splits and the index pages will
> become fragmented.
> If you populate and then create the index you will create the index B-Tree
> structure all in one shot and it will not be fragmented.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:A2B654F6-B13D-40E2-A7DA-852058447BE4@.microsoft.com...
> > Hello:
> >
> > We have a table with millions of records. Not long ago, we added a new
> > column to this large table, and we like to populate this new column with
> > data
> > and create an index for it.
> >
> > Would there be any time difference if we create an index (non-clustered)
> > before we populate it with data, or after we populate it with data? In
> > another word, is it true that it takes lesser time to create an index in a
> > column with only null?
> >
> > Thanks,
> >
> > Q
>
>|||Create the index *after* updating the column with data. Creating the
index first will add overhead during the update process.
On Tue, 10 Jan 2006 15:34:01 -0800, "Q" <Q@.discussions.microsoft.com>
wrote:
>Hello:
>We have a table with millions of records. Not long ago, we added a new
>column to this large table, and we like to populate this new column with data
>and create an index for it.
>Would there be any time difference if we create an index (non-clustered)
>before we populate it with data, or after we populate it with data? In
>another word, is it true that it takes lesser time to create an index in a
>column with only null?
>Thanks,
>Q|||Q:
You actually still get the overhead even with a non-clustered index on a
table that does not have a clustered index. Although it is fairly unusual
for heavily used tables to not have a clustered index. It would be wise to
take an overall look at your indexing strategy
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Q" <Q@.discussions.microsoft.com> wrote in message
news:056EE00B-AD6E-464E-8598-C2872BC1BDCB@.microsoft.com...
> Hello David:
> Thanks for your detail reply. Good to know that it would create more work
> if there is already a clustered index and then if we add a new column, a
> non-clustered index to the new column, then populate data to this new
> column.
> There is no clustered index in this table. So can I assume there is no
> overhead in insert/update, so there will not be fragmentation in split
> index
> page?
> Thanks
> Q
> "David Lundell" wrote:
>> Q:
>> I would hazard a guess that you are better off to populate the new column
>> with data and then create the non-clustered index rather than the other
>> way
>> around. (Assumption: you have an existing Clustered Index on the table
>> that
>> is hopefully on an integer field)
>> Rationale: When you create a non-clustered index on a table that has a
>> clustered index you create a B-Tree structure that has the cluster keys.
>> If you create the index and then populate, you are making the updates
>> work
>> harder because it has to update the leaf level of the clustered index
>> (where
>> the actual data is) as well as insert or manipulate the non-clustered
>> index.
>> As updates occur your index will have page splits and the index pages
>> will
>> become fragmented.
>> If you populate and then create the index you will create the index
>> B-Tree
>> structure all in one shot and it will not be fragmented.
>> --
>> David Lundell
>> Principal Consultant and Trainer
>> www.MutuallyBeneficial.com
>> David@.MutuallyBeneficial.com
>> "Q" <Q@.discussions.microsoft.com> wrote in message
>> news:A2B654F6-B13D-40E2-A7DA-852058447BE4@.microsoft.com...
>> > Hello:
>> >
>> > We have a table with millions of records. Not long ago, we added a new
>> > column to this large table, and we like to populate this new column
>> > with
>> > data
>> > and create an index for it.
>> >
>> > Would there be any time difference if we create an index
>> > (non-clustered)
>> > before we populate it with data, or after we populate it with data? In
>> > another word, is it true that it takes lesser time to create an index
>> > in a
>> > column with only null?
>> >
>> > Thanks,
>> >
>> > Q
>>|||Q wrote:
> Hello:
> We have a table with millions of records. Not long ago, we added a
> new column to this large table, and we like to populate this new
> column with data and create an index for it.
> Would there be any time difference if we create an index
> (non-clustered) before we populate it with data, or after we populate
> it with data? In another word, is it true that it takes lesser time
> to create an index in a column with only null?
> Thanks,
> Q
Definitely update before the index. The update process is going to cause
excessive logging because of the number of rows - which means you should
update in batches (say 100,000 rows at a time if possible). Each update will
require a corresponding index update if th eindex is in place, causing even
more work. Create the index after the column is loaded with data for the
rows in question.
--
David Gugick
Quest Software|||Thanks!
"David Gugick" wrote:
> Q wrote:
> > Hello:
> >
> > We have a table with millions of records. Not long ago, we added a
> > new column to this large table, and we like to populate this new
> > column with data and create an index for it.
> >
> > Would there be any time difference if we create an index
> > (non-clustered) before we populate it with data, or after we populate
> > it with data? In another word, is it true that it takes lesser time
> > to create an index in a column with only null?
> >
> > Thanks,
> >
> > Q
> Definitely update before the index. The update process is going to cause
> excessive logging because of the number of rows - which means you should
> update in batches (say 100,000 rows at a time if possible). Each update will
> require a corresponding index update if th eindex is in place, causing even
> more work. Create the index after the column is loaded with data for the
> rows in question.
> --
> David Gugick
> Quest Software
>
>|||Thanks!
"bradsbulkmail@.comcast.net" wrote:
> Create the index *after* updating the column with data. Creating the
> index first will add overhead during the update process.
> On Tue, 10 Jan 2006 15:34:01 -0800, "Q" <Q@.discussions.microsoft.com>
> wrote:
> >Hello:
> >
> >We have a table with millions of records. Not long ago, we added a new
> >column to this large table, and we like to populate this new column with data
> >and create an index for it.
> >
> >Would there be any time difference if we create an index (non-clustered)
> >before we populate it with data, or after we populate it with data? In
> >another word, is it true that it takes lesser time to create an index in a
> >column with only null?
> >
> >Thanks,
> >
> >Q
>

Tuesday, March 20, 2012

Creating Tables Dynamically

Sunday, March 11, 2012

Creating rows based on date range from another table

Sunday, February 19, 2012

Creating index in a large table

Creating index in a large table

Creating index in a large table

sql database integrity

Blog Archive

About Me