Sql Server Alignment problem: records

Showing posts with label records. Show all posts

Thursday, March 29, 2012

Alternating Detail Sections

Is there a way to have two tables and have the detail section show
alternating records? (ie:51795 in one table and 51795P in the other)
Horizontallay
I need to compare records from two different datasets and have no idea how I
am going to do this?
--
Thank You, LeoHello Leo,
Insert field from different dataset is not supported in the table. However,
you may use function to transfer it. However, it still may not work as you
expect. The best option is using method to combine the data you want in one
dataset by using query or store prcedure.
Please feel free to respond back if you have further questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Alternating Detail Sections
| thread-index: AcS8VnfT0lQlTSXmSBqXC/bhzmR85A==| X-WBNR-Posting-Host: 64.91.16.201
| From: "=?Utf-8?B?VHJ1c3N3b3Jrc0xlbw==?=" <Leo@.noemail.noemail>
| Subject: Alternating Detail Sections
| Date: Wed, 27 Oct 2004 11:55:03 -0700
| Lines: 10
| Message-ID: <63592D60-D9BA-47F3-8325-FE139745CF93@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:33335
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Is there a way to have two tables and have the detail section show
| alternating records? (ie:51795 in one table and 51795P in the other)
| Horizontallay
|
| I need to compare records from two different datasets and have no idea
how I
| am going to do this?
|
|
| --
| Thank You, Leo
|

Alternate ways of deleting records - without logging

Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:

> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>

Sunday, March 25, 2012

Alter table with PRIMARY KEY

I have an existing table (with records) having the ff: structure:

CREATE TABLE [dbo].[TEMP2_WORKORDER] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[JobType] [varchar] (3) NULL ,
[JobID] [varchar] (10) NULL ,

I want to be modify the structure to add a PRIMARY KEY to the [WorkOrderID] column.

I was using ALTER TABLE but can't get the right syntax. Please help!

ThanksALTER TABLE dbo.TEMP2_WORKORDER ADD CONSTRAINT
PK_testtable PRIMARY KEY CLUSTERED
(
WorkOrderID
)|||Thank you. It did the trick!

Tuesday, March 20, 2012

Alter table db size getting increased.

Hi,
I am doing alter table and updating Nvarchar datatype to Ntext. AS there are
almost
54,09,873 records installation is taking more than 3 hours and log/database
files are getting increased beyond limit. Now there is hardly space left and
still installation is not completed.
Tasks Restore Size After installation
Datafile Size 39000 46529 MB
Log Size 4550 14032 MB
ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
54,09,873 Rows affected.
As far as I know and understand Alter basically copy the contents to some
temp table, deletes the table, recreate the table with new definition and
again copy back to original table( Sorry if sequence is wrong ?) and I thi
nk
this is the reason why this script is taking more space and impacting log
size.
IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
SIZE OF DB INCREASE.
--
SanjayYes, you might try dumping the data into an external text file, and then bc
p
or bulk insert it back into the new table structure.
"Sanjay" wrote:

> Hi,
> I am doing alter table and updating Nvarchar datatype to Ntext. AS there a
re
> almost
> 54,09,873 records installation is taking more than 3 hours and log/databas
e
> files are getting increased beyond limit. Now there is hardly space left a
nd
> still installation is not completed.
> Tasks Restore Size After installation
> Datafile Size 39000 46529 MB
> Log Size 4550 14032 MB
> ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
> 54,09,873 Rows affected.
> As far as I know and understand Alter basically copy the contents to some
> temp table, deletes the table, recreate the table with new definition and
> again copy back to original table( Sorry if sequence is wrong ?) and I t
hink
> this is the reason why this script is taking more space and impacting log
> size.
> IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
> SIZE OF DB INCREASE.
> --
> Sanjay|||Thanks for your help,
can you help me in syntax and will this be logged in log file.
i never tried that but will this help in reducing time and DB size issues.
"CBretana" wrote:
> Yes, you might try dumping the data into an external text file, and then
bcp
> or bulk insert it back into the new table structure.
> "Sanjay" wrote:
>|||Look up BCP in BOL -- it's a utility that comes w/SQL server.
Setting the logging level to SIMPLE would help too -- look up ALTER
DATABASE; the setting is SET RECOVERY
"Sanjay" wrote:
> Thanks for your help,
> can you help me in syntax and will this be logged in log file.
> i never tried that but will this help in reducing time and DB size issues.
> "CBretana" wrote:
>|||I tried but log file was increased around 5 gb and data file also.
i am doing something like this.
alter database xyz
set recovery simple
alter table and modifying nvarchar to ntext ( around 54,09,873 rows)
set recovery full.
"KH" wrote:
> Look up BCP in BOL -- it's a utility that comes w/SQL server.
> Setting the logging level to SIMPLE would help too -- look up ALTER
> DATABASE; the setting is SET RECOVERY
>
> "Sanjay" wrote:
>

Monday, March 19, 2012

Alter Table Alter Column

I would like to add an Identity to an existing column in a table using a
stored procedure then add records to the table and then remove the identity
after the records have been added or something similar.
here is a rough idea of what the stored procedure should do. (I do not know
the syntax to accomplish this can anyone help or explain this?

Thanks much,

CBL

CREATE proc dbo.pts_ImportJobs
as

/* add identity to [BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL

/* add records from text file here */

/* remove identity from BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] NOT NULL
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

here is the original table

CREATE TABLE [ItemTest] (
[BarCode Part#] [int] NOT NULL ,
[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
DEFAULT (0),
[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
DEFAULT (0),
[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT
(0),
[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode Part#]
) ON [PRIMARY]
) ON [PRIMARY]
GO"me" <me@.work.com> wrote in message
news:10crrjkjjttmbbe@.corp.supernews.com...
> I would like to add an Identity to an existing column in a table using a
> stored procedure then add records to the table and then remove the
identity
> after the records have been added or something similar.
> here is a rough idea of what the stored procedure should do. (I do not
know
> the syntax to accomplish this can anyone help or explain this?
> Thanks much,
> CBL
>
>
> CREATE proc dbo.pts_ImportJobs
> as
> /* add identity to [BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL
> /* add records from text file here */
> /* remove identity from BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] NOT NULL
> return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> here is the original table
> CREATE TABLE [ItemTest] (
> [BarCode Part#] [int] NOT NULL ,
> [File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
> [Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
> [Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
> [Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
> [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
> [Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
> DEFAULT (0),
> [Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
> [Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
> [Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
> DEFAULT (0),
> [BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#]
DEFAULT
> (0),
> [Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
> [Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
> [Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date]
DEFAULT
> (getdate()),
> CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> (
> [BarCode Part#]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

You can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.

But there are several ways to INSERT identity values into a table which
already has the IDENTITY property - I suspect that's what you're really
looking for. For loading a text file with BULK INSERT or bcp.exe, there are
options to keep identity values when you import (KEEPIDENTITY and the -E
switch, respectively). For INSERTs from another table, you can use SET
IDENTITY_INSERT ON.

Finally, DBCC CHECKIDENT is used after you've INSERTed, to make sure that
the identity seed is consistent with the table data. See Books Online for
more details on all these commands.

Simon|||Thanks for the help!

CBL

"me" <me@.work.com> wrote in message
news:10crrjkjjttmbbe@.corp.supernews.com...
> I would like to add an Identity to an existing column in a table using a
> stored procedure then add records to the table and then remove the
identity
> after the records have been added or something similar.
> here is a rough idea of what the stored procedure should do. (I do not
know
> the syntax to accomplish this can anyone help or explain this?
> Thanks much,
> CBL
>
>
> CREATE proc dbo.pts_ImportJobs
> as
> /* add identity to [BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL
> /* add records from text file here */
> /* remove identity from BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] NOT NULL
> return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> here is the original table
> CREATE TABLE [ItemTest] (
> [BarCode Part#] [int] NOT NULL ,
> [File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
> [Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
> [Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
> [Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
> [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
> [Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
> DEFAULT (0),
> [Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
> [Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
> [Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
> DEFAULT (0),
> [BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#]
DEFAULT
> (0),
> [Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
> [Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
> [Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date]
DEFAULT
> (getdate()),
> CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> (
> [BarCode Part#]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

alter table - change time evaluation

Hi,

I have a table with 70 million records. I want to change the datatype of one of the columns from int to decimal(18,3). What is the best way of doing it? and how can I evaluate the time change before I start and make the change?

thank you,

Tomer

..assuming you want to know how long the operation would take....this varies massively by table definition + indexes + hardware + load

you could use SELECT INTO to create a table of one million records and add indexes identical to those on the production table.

Then drop the index on the int column on that table,

execute an ALTER TABLE ALTER COLUMN to change the datatype

and rebuild the index.

Then multiply the result by 70 though that's only an estimate as the rebuild time may not be linear.

Thursday, March 8, 2012

ALTER how long should it take?

The following ALTER takes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.PERSON ADD
FL_CNSL_NTFY char(1) NOT NULL CONSTRAINT DF_PERSON_FL_CNSL_NTFY
DEFAULT '',
CD_INTRP_NEED smallint NOT NULL CONSTRAINT DF_PERSON_CD_INTRP_NEED
DEFAULT 0
GO
COMMIT

Thanks for any tips on this issue...(cuneyt.barutcu@.illinois.gov) writes:

Quote:

Originally Posted by

The following ALTER takes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.

When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that does take some time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.

I don't have much experience of ALTER TABLE myself, because I almost
always take the long way in my update scripts. That is, I rename the
existing table, create the table with the new definition, copy the
data, recreate indexes, triggers, and foreign keys, move referencing
foreign keys to the new table and finally drop the old definition.
When I copy data, I have a loop, so that I copy some 50000 rows at
a time.

This way of altering a table gives more flexibility to place columns
where you want, or make changes like replacing a bit column with
a char(1) column. But it also requires more care, since there are
so many steps. I have a tool that generates this for me. If you do it
by hand, you have to be very careful.

But there is certainly one thing you should check for: blocking. Maybe
some other process is blocking ALTER TABLE from running at all. Check this
with sp_who.

--
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 Jun 8, 4:17 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(cuneyt.baru...@.illinois.gov) writes:

Quote:

Originally Posted by

The followingALTERtakes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.

>
When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that doestakesome time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.
>
I don't have much experience ofALTERTABLE myself, because I almost
alwaystakethelongway in my update scripts. That is, I rename the
existing table, create the table with the new definition, copy the
data, recreate indexes, triggers, and foreign keys, move referencing
foreign keys to the new table and finally drop the old definition.
When I copy data, I have a loop, so that I copy some 50000 rows at
a time.
>
This way of altering a table gives more flexibility to place columns
where you want, or make changes like replacing a bit column with
a char(1) column. But it also requires more care, since there are
so many steps. I have a tool that generates this for me. If you do it
by hand, you have to be very careful.
>
But there is certainly one thing youshouldcheck for: blocking. Maybe
some other process is blockingALTERTABLE from running at all. Check this
with sp_who.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools. Can you also tell me how long typically takes for you
to administer this type of change.
I appreciate your help. Thanks again.|||(cuneyt.barutcu@.illinois.gov) writes:

Quote:

Originally Posted by

Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools.

It's an inhouse tool that I developed myself.

As for commercial tools on the market, I don't have a very good overview
what is available. But Microsoft offers "DataDude", that is Visual Studio
Team Suite for Database Professionals. I believe the price tag is hefty.

Many people use Red Gate's SQL Compare to generate their change scripts.

There is something called SQLFarms, which looks interesting, but I have
looked very very little on it.

Quote:

Originally Posted by

Can you also tell me how long typically takes for you
to administer this type of change.

There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the production databases
there is an unknown number of test databases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.

--
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 Jun 11, 5:28 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(cuneyt.baru...@.illinois.gov) writes:

Quote:

Originally Posted by

Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools.

>
It's an inhouse tool that I developed myself.
>
As for commercial tools on the market, I don't have a very good overview
what is available. But Microsoft offers "DataDude", that is Visual Studio
Team Suite for Database Professionals. I believe the price tag is hefty.
>
Many people use Red Gate'sSQLCompareto generate their change scripts.
>
There is something called SQLFarms, which looks interesting, but I have
looked very very little on it.
>

Quote:

Originally Posted by

Can you also tell me how long typically takes for you
to administer this type of change.

>
There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the productiondatabases
there is an unknown number of testdatabases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.
>
--
Erland Sommarskog,SQLServerMVP, esq...@.sommarskog.se
>
Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi there,

you may want to check out our xSQL Object (http://
www.xsqlsoftware.com) for generating those change scripts - we have a
free lite edition available also.

Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Saturday, February 25, 2012

alter column

hi,
I have a production table with 2.6 million records, I need
to alter one column from numeric to varchar, does it will
affect the users? blocking the table? ThanksHi,
Obviously Yes....Dont do it when users are accessing the table.
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:088d01c3ced5$34b021e0$a601280a@.phx.gbl...
> hi,
> I have a production table with 2.6 million records, I need
> to alter one column from numeric to varchar, does it will
> affect the users? blocking the table? Thanks|||Changing the column data type (ALTER TABLE ... ALTER COLUMN) will acquire a
schema modification lock for the duration of the operation. In this case,
all data pages be updated so you probably want to do this during a
maintenance window.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:088d01c3ced5$34b021e0$a601280a@.phx.gbl...
> hi,
> I have a production table with 2.6 million records, I need
> to alter one column from numeric to varchar, does it will
> affect the users? blocking the table? Thanks

Friday, February 24, 2012

almost duplicate records

Hi,
Is there a way to 'merge' almost duplicate records. I have records which
are the same but the other duplicate record can differ from the
other. Normally i just delete the duplicate records, but in this case i
can't.
For example (in a 1-N relation):
id | field1 | field2 | field3
1 | 11.5 | 52.0 | null
1 | null | null | yes
should be:
id | field1 | field2 | field3
1 | 11.5 | 52.0 | yes
Is this possible?Maybe like this:
SELECT id, SUM(f1), SUM(f2), MAX(f3)
FROM YourTable
GROUP BY id ;
David Portas
SQL Server MVP
--|||David Portas wrote:
> Maybe like this:
> SELECT id, SUM(f1), SUM(f2), MAX(f3)
> FROM YourTable
> GROUP BY id ;
>
Hi David,
I tried that, but that will only work with numeric values. I have
numeric as well as varchars.
Any idea how i can solve this?|||SUM will only work with numerics. MIN and MAX will work with VARCHARs
too.
The answer all depends on what you want to achieve. In you example data
what result would you have required if Field3 on the first row had been
"No" instead of NULL?. You would have to devise an expression that
makes some sense based on your knowledge of the data.
There may be other alternatives. For example you could pick the row for
each ID that has the fewest NULL attributes. Example:
ALTER TABLE YourTable ADD i INTEGER IDENTITY
GO
SELECT P.id, P.f1, P.f2, P.f3
FROM YourTable AS P,
(SELECT MIN(i) AS i
FROM YourTable AS T2
WHERE
CASE WHEN T2.f1 IS NULL THEN 1 ELSE 0 END+
CASE WHEN T2.f2 IS NULL THEN 1 ELSE 0 END+
CASE WHEN T2.f3 IS NULL THEN 1 ELSE 0 END =
(SELECT MIN(
CASE WHEN T3.f1 IS NULL THEN 1 ELSE 0 END+
CASE WHEN T3.f2 IS NULL THEN 1 ELSE 0 END+
CASE WHEN T3.f3 IS NULL THEN 1 ELSE 0 END)
FROM YourTable AS T3
WHERE T2.id = T3.id)
GROUP BY id) AS Q
WHERE P.i = Q.i ;
David Portas
SQL Server MVP
--

Monday, February 13, 2012

Allocate to records, randomize rounded amount

I'd like to build a stored procedure that would allocate a single value
among records. If there was a rounded remainder I would like to randomly
pick one of the records to receive the "extra" rounded amount. I'd like to
pass my procedure 2 variables: @.AccountType and @.Amount. The amount
allocated should always be to 2 decimal places.
If the variables were @.AccountType = "A' and @.Amount = 10 the AccountValue
for both AccountIDs 1 and 2 would be 5.00
If the variables were @.AccountType = "A' and @.Amount = 11 the AccountValue
for both AccountIDs 1 and 2 would be 5.50
If the variables were @.AccountType = "B' and @.Amount = 10 the AccountValue
would be 3.33 for 2 of the accounts and 3.34 for the third account. I'd like
to randomize which account gets 3.34, the extra penny.
Can anyone help me write this stored procedure?
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountValue] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(1,'A',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(2,'A',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(3,'B',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(4,'B',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(5,'B',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(6,'C',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(7,'C',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(8,'C',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(9,'C',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(10,'C',NULL)e.g.
alter procedure AllocateToAccounts(@.AccountType char(1), @.TotalValue
decimal(18,2))
as
declare
@.Allocation decimal(18,2),
@.Count int, @.RandID int
select @.Count = count(*)
from Accounts
where AccountType = @.AccountType
select @.RandID = (
select top 1 AccountID
from Accounts
where AccountType=@.AccountType
order by NewID()
)
select @.Allocation = @.TotalValue / @.Count
update Accounts
set AccountValue =
case AccountID
when @.RandID then @.Allocation + (@.TotalValue-(@.Allocation*@.Count))
else @.Allocation
end
where AccountType = @.AccountType
Terri wrote:
> I'd like to build a stored procedure that would allocate a single value
> among records. If there was a rounded remainder I would like to randomly
> pick one of the records to receive the "extra" rounded amount. I'd like to
> pass my procedure 2 variables: @.AccountType and @.Amount. The amount
> allocated should always be to 2 decimal places.
> If the variables were @.AccountType = "A' and @.Amount = 10 the AccountValue
> for both AccountIDs 1 and 2 would be 5.00
> If the variables were @.AccountType = "A' and @.Amount = 11 the AccountValue
> for both AccountIDs 1 and 2 would be 5.50
> If the variables were @.AccountType = "B' and @.Amount = 10 the AccountValue
> would be 3.33 for 2 of the accounts and 3.34 for the third account. I'd li
ke
> to randomize which account gets 3.34, the extra penny.
> Can anyone help me write this stored procedure?
> CREATE TABLE [dbo].[Accounts] (
> [AccountID] [int] NOT NULL ,
> [AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AccountValue] [decimal](18, 2) NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (1,'A',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (2,'A',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (3,'B',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (4,'B',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (5,'B',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (6,'C',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (7,'C',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (8,'C',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (9,'C',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (10,'C',NULL)
>|||Great, thanks, works perfectly. I have a related allocation method that I
need a stored procedure for so I am going to continue this thread and hope
for the continued expertise and generosity of this group.
I've modified the structure of my table to include an additional field,
Assets. In this method I want to allocate based on the assets of an account.
If I was allocating to 10.00 to all Accounts of AccountType 'A' and the
assets of AccountID 1 is 90.00 and AccountID 2 had assets of 10.00, Account
1 would be allocated 9.00 and account 2 would get 1.00.
Logically, add the assets of all AccountTypes 'A' (100.00) and determine
each accounts percentage of the total. Account 1 has 90% of total and
Account 2 has 10% of total, then allocate based on these percentages.
In this method I don't want to assign the rounded amount randomly but
instead based on "assets". If the number is rounded up it should be
allocated to the account with the most assets. If the number is allocated
down it should go to the account with the least assets.
My actual asset figures are in the millions so ties would be extremely
unlikely.
Sample data and expected results for AccountType 'B'; Amount to be
allocated: 100.00
AccountID, AccountType,Assets,Expected Result
3,'B',33.35,17.33
4,'B',85.01,44.19
5,'B',74.02,38.48
Thanks to anyone who might help.
DROP TABLE Accounts
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountValue] [decimal](18, 2) NULL,
[Assets] [decimal](18, 2) NULL,
) ON [PRIMARY]
GO
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(1,'A',NULL,90)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(2,'A',NULL,10)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(3,'B',NULL,33.35)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(4,'B',NULL,85.01)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(5,'B',NULL,74.02)
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:#3xiFQEAGHA.2656@.tk2msftngp13.phx.gbl...
> e.g.
> alter procedure AllocateToAccounts(@.AccountType char(1), @.TotalValue
> decimal(18,2))
> as
> declare
> @.Allocation decimal(18,2),
> @.Count int, @.RandID int
> select @.Count = count(*)
> from Accounts
> where AccountType = @.AccountType
> select @.RandID = (
> select top 1 AccountID
> from Accounts
> where AccountType=@.AccountType
> order by NewID()
> )
> select @.Allocation = @.TotalValue / @.Count
> update Accounts
> set AccountValue =
> case AccountID
> when @.RandID then @.Allocation + (@.TotalValue-(@.Allocation*@.Count))
> else @.Allocation
> end
> where AccountType = @.AccountType
>

Allocate Range of AutoNumber ID's to each user

I have a non standard requirement that about 5 different users from differen
t
departments require sequential numbering for there records, however I want
all records to be in the one central table. This comes about as each
departments records ID's have a 4 letter acronym before the AutoNumber ID.
Is this possible or should I create seperate tables and then join them using
a view with union queries?
Any suggestions would be greatly appreciated.
ThanksHi
You can use INSTEAD OF TRIGGER to achieve this business logic
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Hi,
Not sure how to use this, do you have a link or sample I could work from.
Much appreciated.
"Chandra" wrote:
> Hi
> You can use INSTEAD OF TRIGGER to achieve this business logic
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Hi,
You can try this way
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE>
INSTEAD OF INSERT
AS
INSERT INTO <TABLE>
SELECT <your logic>, required columns
FROM INSERTED
WHERE INSERTED.Key = Key
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Hi,
> Not sure how to use this, do you have a link or sample I could work from.
> Much appreciated.
> "Chandra" wrote:
>|||Thanks.
So best to set up my 5 tables which fulfills the business requirement and
then uses 'Instead of Triggers' to generate a master table with all results.
The only other query I have is how does the key part of the query work.
Thanks
"Chandra" wrote:
> Hi,
> You can try this way
> CREATE TRIGGER <TRIGGER_NAME>
> ON <TABLE>
> INSTEAD OF INSERT
> AS
> INSERT INTO <TABLE>
> SELECT <your logic>, required columns
> FROM INSERTED
> WHERE INSERTED.Key = Key
> GO
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Key is the primary key value in the table. If you are sure that you will be
inserting only one row at a time, then you can avoiding the key.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Thanks.
> So best to set up my 5 tables which fulfills the business requirement and
> then uses 'Instead of Triggers' to generate a master table with all result
s.
> The only other query I have is how does the key part of the query work.
> Thanks
> "Chandra" wrote:
>|||First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that make
s
no sense whatsoever. This is NOT a "non standard" requirement and is is quit
e
common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
mess of a system. This requirement is known as pre-allocated numbers. Most
banks I know do this.
I don't know what the numbers are for, so I'll assume Orders. Here's how
it's typically done:
TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
PROCEDURE Create_New_Order (@.Dept_Id,...) {
BEGIN TRANSACTION
@.Ord_Num =
SELECT Order_Num
FROM Avaiable_Order_Numbers
WHERE Dept_Id = @.Dept_Id
INSERT INTO Orders (@.Ord_Num, ...)
DELETE FROM Avaiable_Order_Numbers
WERE Order_Num = @.Order_Num
COMMIT
}
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Thanks for your assistance Alex. Looked at the Instead of Trigger option an
d
it seemed messy!
Basically if you use 5 tables one for each department with there own
sequential identity value incrementing concatenated with the Dept. acronym
and then put a trigger on Insert, Update (There is no delete facility) to
build a consolidated/master table for analysis, this will provide a robust
solution.
Any comments appreciated!
"Alex Papadimoulis" wrote:
> First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that ma
kes
> no sense whatsoever. This is NOT a "non standard" requirement and is is qu
ite
> common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
> mess of a system. This requirement is known as pre-allocated numbers. Most
> banks I know do this.
> I don't know what the numbers are for, so I'll assume Orders. Here's how
> it's typically done:
> TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
> TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
> PROCEDURE Create_New_Order (@.Dept_Id,...) {
> BEGIN TRANSACTION
> @.Ord_Num =
> SELECT Order_Num
> FROM Avaiable_Order_Numbers
> WHERE Dept_Id = @.Dept_Id
> INSERT INTO Orders (@.Ord_Num, ...)
> DELETE FROM Avaiable_Order_Numbers
> WERE Order_Num = @.Order_Num
> COMMIT
> }
>
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "MarkCapo" wrote:
>

Sunday, February 12, 2012

All records within x minutes of each other

Consider a table that holds Internet browsing history for users/machines,
date/timed to the minute. The object is to tag all times that are separated
by previous and subsequent times by x number of minutes or less (it could
vary, and wouldn't necessarily be a convenient round number). This will
enable reporting "active time" for users (a dubious inference, but hey).
There are a lot of derivative ways of seeing this information that might be
good to get to. What's the fist and last of these sets of times? What
percentage of a given period is spanned by active times, and not? What is
the average duration of such periods? What is the average interval between
web hits during such periods? During other times?
Blah, blah. The basic problem is my principal problem. I don't have much
experience with cursors, but from what I understand it would be very good
indeed to spare them, given the number of records I anticipate working
with.
I'd be glad of any pointers.
ScottBasics: Time comes in durations, so evetns have a start and stop time.
The really good stuff can be found at the University of Arizona
website where they have a PDF copy of the Rick Snodgrass book and his
research paper.|||There are many way to accomplish this, for instance:
get a set of beginnings of active periods:
select ...
from events where not exists(
-- no events in the preceding ... minutes
)
and exists(
-- events in the followinging ... minutes
)
get a set of endings of active periods:
select ...
from events where not exists(
-- no events in the following ... minutes
)
and exists(
-- events in the preceding ... minutes
)
That done, you need to match every beginning to its corresponding end.
This is very simple using row_number() available in SQL 2005
In earlier versions, you can either emulate row_number() using
identity() column in a result set, or use a join condition like this:
...
from beginnings b join ends e on b.time<e.time
where not exists(select 1 from beginnings b1 where b.time< b1.time and
b1.time<e.time)
and not exists(select 1 from endings e1 where b.time< e1.time and
e1.time<e.time)|||> The really good stuff can be found at the University of Arizona
> website where they have a PDF copy of the Rick Snodgrass book and his
> research paper.
Thanks for the info.
AMB
"--CELKO--" wrote:

> Basics: Time comes in durations, so evetns have a start and stop time.
> The really good stuff can be found at the University of Arizona
> website where they have a PDF copy of the Rick Snodgrass book and his
> research paper.
>|||On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:

> Basics: Time comes in durations, so evetns have a start and stop time.
> The really good stuff can be found at the University of Arizona
> website where they have a PDF copy of the Rick Snodgrass book and his
> research paper.
Joe, without passing judgement on your basic assertion "Time comes in
durations", you must be aware that web requests, like most other events in
computing, are not ever logged as durations, but as instants. Unless you
intend to win over the writers and administrators of every web server on
the planet, we're going to have to damn well DEAL with them as events, not
durations.|||Ross Presser opined thusly on Aug 30:
> On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:
>
> Joe, without passing judgement on your basic assertion "Time comes in
> durations", you must be aware that web requests, like most other events in
> computing, are not ever logged as durations, but as instants. Unless you
> intend to win over the writers and administrators of every web server on
> the planet, we're going to have to damn well DEAL with them as events, not
> durations.
Well, OTOH the telos of the click is to digest content, which consumes time
(duration). But we don't have eyeball trackers on our desktops yet, so
we're left to infer from events that there's subsequent eyeball activity --
users don't do http GETs for no reason.
But that's an abstraction -- a problematic one -- whereas indeed these are
events. Still, like vertices on a triangle, to get from one to another of
these moments you have to traverse the length of a side.
Grats to Joe for the sensible reply. I'm always slapping my forehead. Just
now I'm having trouble even with that simplicity. :-/
Scott|||"Ross Presser" <rpresser@.NOSPAMgmail.com.invalid> wrote in message
news:1w3cyv7dg377n$.dlg@.rosspresser.dyndns.org...
> On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:
>
> Joe, without passing judgement on your basic assertion "Time comes in
> durations", you must be aware that web requests, like most other events in
> computing, are not ever logged as durations, but as instants. Unless you
> intend to win over the writers and administrators of every web server on
> the planet, we're going to have to damn well DEAL with them as events, not
> durations.
You might as well give up. Joe and I had this argument I think it was a
year ago and he was just as wrong then as he is now.|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:549939
AK opined thusly on Aug 30:
> There are many way to accomplish this, for instance:
Thanks. It's been my only clue, and was sure sensible. I thanked Joe
earlier, remiss. However, I owe him thanks for leading me toward that big
paper on temporal SQL -- that stuff's dynamite. It certainly gives an
answer to the duration/event argument: "yes." ;-)
NOW my fun is that the times I'm working with have only minute precision,
so I often get several identical times for a given user (the most logical
grouping). This presents all kinds of problems for the kinds of reporting
I'm looking at. It's fine for determining periods of activity when one's
after a minute mark, but anything beyond that starts getting hairy. Sure
wish I had even second precision. The client-side use of shdoc401.dll
namespace(34) seems to preclude this,

ly.
Scott|||>> you must be aware that web requests, like most other events in computing,
are not ever logged as durations, but as instants.<<
Do not confuse the recording of the data with the data model. Think
about a sign-in and sign-out sheet or timeclock. Each line is "half a
fact"; the whole fact is the duration spent on the job. In this case,
the user logs onto a site, stays there for x-minutes. He is not there
for a Chronon (that is the term for a point in time in temporal
databases). So his table MIGHT look like this:
CREATE TABLE Browsing
(user_id VARCHAR(30) NOT NULL,
website VARCHAR(255) NOT NULL,
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
logout_time TIMESTAMP, -- null means still there
CHECK (login_time <= logout_time), -- less than one minute problem
PRIMARY KEY (user_id, website, login_time));
The real problem in this situaiton is having to round to the minute.
We can force a convention on the stopping time to keep it away from the
starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
to 'yyyy-mm-ddThh:mm:59.997').
Ever read the paradoxes of Zeno? He went thru what happens when you
believe in Chronons. He lived in a time when Gr

math was "integer
only" and without a continuum.|||--CELKO-- opined thusly on Aug 31:

> The real problem in this situaiton is having to round to the minute.
> We can force a convention on the stopping time to keep it away from the
> starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
> to 'yyyy-mm-ddThh:mm:59.997').
As in my further woes (see recent reply in thread), at issue is the
adequacy of the data in describing the phenomena In this case, users cannot
simultaneously click a mouse on more than one thing -- though it's not
impossible to identify ways that http GETs can occur concurrently on one
machine under one user's context. At any rate, it should be obvious that
minute precision for web browsing event recording is insanely coarse. But I
doubt anyone planned for local Internet History to be used for purposes I'm
stretching it to. A proxy server is a choke-point that allows for more
precise dating, because it's an ideal platform for doing so. A Microsoft
DLL is not necessarily designed to meet needs its coders never had in mind,
alas.
Dang, if they'd only gone to second precision. I'd be content with that, I
swear! ;-)
Scott

All records within x minutes of each other

There are a lot of derivative ways of seeing this information that might be
good to get to. What's the fist and last of these sets of times? What
percentage of a given period is spanned by active times, and not? What is
the average duration of such periods? What is the average interval between
web hits during such periods? During other times?

Blah, blah. The basic problem is my principal problem. I don't have much
experience with cursors, but from what I understand it would be very good
indeed to spare them, given the number of records I anticipate working
with.

I'd be glad of any pointers.

ScottBasics: Time comes in durations, so evetns have a start and stop time.
The really good stuff can be found at the University of Arizona
website where they have a PDF copy of the Rick Snodgrass book and his
research paper.|||There are many way to accomplish this, for instance:

get a set of beginnings of active periods:

select ...
from events where not exists(
-- no events in the preceding ... minutes
)
and exists(
-- events in the followinging ... minutes
)

get a set of endings of active periods:

select ...
from events where not exists(
-- no events in the following ... minutes
)
and exists(
-- events in the preceding ... minutes
)

That done, you need to match every beginning to its corresponding end.
This is very simple using row_number() available in SQL 2005
In earlier versions, you can either emulate row_number() using
identity() column in a result set, or use a join condition like this:

...
from beginnings b join ends e on b.time<e.time
where not exists(select 1 from beginnings b1 where b.time< b1.time and
b1.time<e.time)
and not exists(select 1 from endings e1 where b.time< e1.time and
e1.time<e.time)|||On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:

> Basics: Time comes in durations, so evetns have a start and stop time.
> The really good stuff can be found at the University of Arizona
> website where they have a PDF copy of the Rick Snodgrass book and his
> research paper.

Joe, without passing judgement on your basic assertion "Time comes in
durations", you must be aware that web requests, like most other events in
computing, are not ever logged as durations, but as instants. Unless you
intend to win over the writers and administrators of every web server on
the planet, we're going to have to damn well DEAL with them as events, not
durations.|||Ross Presser opined thusly on Aug 30:
> On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:
>> Basics: Time comes in durations, so evetns have a start and stop time.
>> The really good stuff can be found at the University of Arizona
>> website where they have a PDF copy of the Rick Snodgrass book and his
>> research paper.
> Joe, without passing judgement on your basic assertion "Time comes in
> durations", you must be aware that web requests, like most other events in
> computing, are not ever logged as durations, but as instants. Unless you
> intend to win over the writers and administrators of every web server on
> the planet, we're going to have to damn well DEAL with them as events, not
> durations.

Well, OTOH the telos of the click is to digest content, which consumes time
(duration). But we don't have eyeball trackers on our desktops yet, so
we're left to infer from events that there's subsequent eyeball activity --
users don't do http GETs for no reason.

But that's an abstraction -- a problematic one -- whereas indeed these are
events. Still, like vertices on a triangle, to get from one to another of
these moments you have to traverse the length of a side.

Grats to Joe for the sensible reply. I'm always slapping my forehead. Just
now I'm having trouble even with that simplicity. :-/

Scott|||"Ross Presser" <rpresser@.NOSPAMgmail.com.invalid> wrote in message
news:1w3cyv7dg377n$.dlg@.rosspresser.dyndns.org...
> On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:
> > Basics: Time comes in durations, so evetns have a start and stop time.
> > The really good stuff can be found at the University of Arizona
> > website where they have a PDF copy of the Rick Snodgrass book and his
> > research paper.
> Joe, without passing judgement on your basic assertion "Time comes in
> durations", you must be aware that web requests, like most other events in
> computing, are not ever logged as durations, but as instants. Unless you
> intend to win over the writers and administrators of every web server on
> the planet, we're going to have to damn well DEAL with them as events, not
> durations.

You might as well give up. Joe and I had this argument I think it was a
year ago and he was just as wrong then as he is now.|||AK opined thusly on Aug 30:
> There are many way to accomplish this, for instance:

Thanks. It's been my only clue, and was sure sensible. I thanked Joe
earlier, remiss. However, I owe him thanks for leading me toward that big
paper on temporal SQL -- that stuff's dynamite. It certainly gives an
answer to the duration/event argument: "yes." ;-)

NOW my fun is that the times I'm working with have only minute precision,
so I often get several identical times for a given user (the most logical
grouping). This presents all kinds of problems for the kinds of reporting
I'm looking at. It's fine for determining periods of activity when one's
after a minute mark, but anything beyond that starts getting hairy. Sure
wish I had even second precision. The client-side use of shdoc401.dll
namespace(34) seems to preclude this, sadly.

Scott|||>> you must be aware that web requests, like most other events in computing, are not ever logged as durations, but as instants.<<

Do not confuse the recording of the data with the data model. Think
about a sign-in and sign-out sheet or timeclock. Each line is "half a
fact"; the whole fact is the duration spent on the job. In this case,
the user logs onto a site, stays there for x-minutes. He is not there
for a Chronon (that is the term for a point in time in temporal
databases). So his table MIGHT look like this:

CREATE TABLE Browsing
(user_id VARCHAR(30) NOT NULL,
website VARCHAR(255) NOT NULL,
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
logout_time TIMESTAMP, -- null means still there
CHECK (login_time <= logout_time), -- less than one minute problem
PRIMARY KEY (user_id, website, login_time));

The real problem in this situaiton is having to round to the minute.
We can force a convention on the stopping time to keep it away from the
starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
to 'yyyy-mm-ddThh:mm:59.997').

Ever read the paradoxes of Zeno? He went thru what happens when you
believe in Chronons. He lived in a time when Greek math was "integer
only" and without a continuum.|||--CELKO-- opined thusly on Aug 31:

> The real problem in this situaiton is having to round to the minute.
> We can force a convention on the stopping time to keep it away from the
> starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
> to 'yyyy-mm-ddThh:mm:59.997').

As in my further woes (see recent reply in thread), at issue is the
adequacy of the data in describing the phenomena In this case, users cannot
simultaneously click a mouse on more than one thing -- though it's not
impossible to identify ways that http GETs can occur concurrently on one
machine under one user's context. At any rate, it should be obvious that
minute precision for web browsing event recording is insanely coarse. But I
doubt anyone planned for local Internet History to be used for purposes I'm
stretching it to. A proxy server is a choke-point that allows for more
precise dating, because it's an ideal platform for doing so. A Microsoft
DLL is not necessarily designed to meet needs its coders never had in mind,
alas.

Dang, if they'd only gone to second precision. I'd be content with that, I
swear! ;-)

Scott|||You are fabricating an example to suit your definitions while makeing no
effort to distingush between instants and intervals. For someone who
recommends Snodgrass' work, you should really read chapters 3 and 11 of his
book.

--
Anith|||On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:

>Basics: Time comes in durations, so evetns have a start and stop time.

Hi Joe,

Really?

In the Usenet headers of your message is this line:
>NNTP-Posting-Date: Tue, 30 Aug 2005 20:28:41 +0000 (UTC)
This denotes the time you decided to hit the "send" button (or whatever
it's called in your software) and publish your message to the Usenet.
Please tell me the start and stop time of posting this message?

On my desk is a letter. The poststamp on the envelope is stamped by the
Dutch postal service. This stamp includes a date: "22 VIII 05". Please
don't tell me that this means that they started stamping it on midnight
and took a full 24 hours before the stamping was done.

Think about tracking when a web advertisement was served. The NNTP
protocol can't track how long I look at the ad. (IIRC, it's even
impossible to track if I have an ad blocker active). All web advertising
contracts are based on how often the ad is served. What is the start and
stop time of serving an ad?

How about police work? An officer is checking the streets, and at 11:47
AM he sees you driving through a red light. What's the start and stop
times of that? Or if you are caught speeding? Sure, you started speeding
before the officer caught you, and you might have continued after that,
but there's no way that the dept of Justice will ever find out - but
they do know the exact time that an officer of the law saw on his
equipment that you were driving 7.3 mph too fast.

Need I go on, or do you now have enough examples to know that in the
real world, time does NOT always have start and stsop time.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||AK opined thusly on Aug 30:

> That done, you need to match every beginning to its corresponding end.
> This is very simple using row_number() available in SQL 2005
> In earlier versions, you can either emulate row_number() using
> identity() column in a result set, or use a join condition like this:
> ...
> from beginnings b join ends e on b.time<e.time
> where not exists(select 1 from beginnings b1 where b.time< b1.time and
> b1.time<e.time)
> and not exists(select 1 from endings e1 where b.time< e1.time and
> e1.time<e.time)

OK, here's the result in all its gory(sic). Still playing. It's interesting
to vary @.interval and see the consequences. Now I have to figure out how to
justify any particular value for that. Geeez . . .

| CREATE FUNCTION ie_begin(@.interval datetime)
| RETURNS TABLE
| AS
|
| Return
| (
| select i0.Username, i0.vDate
| from ieHist i0 where not exists
| (
| select vDate from ieHist where vDate > i0.vDate - @.interval and vDate < i0.vDate and i0.username = username
| )
| or exists
| (
| select vDate from ieHist where vDate < i0.vDate - @.interval and vDate > i0.vDate and i0.username = username
| )
| group by i0.username, i0.vDate
| )
| go

| CREATE FUNCTION ie_end(@.interval datetime)
| RETURNS TABLE
| AS
|
| Return
| (
| select i0.Username, i0.vDate
| from ieHist i0 where not exists
| (
| select vDate from ieHist where vDate < i0.vDate + @.interval and vDate > i0.vDate and i0.username = username
| )
| or exists
| (
| select vDate from ieHist where vDate > i0.vDate + @.interval and vDate < i0.vDate and i0.username = username
| )
| group by i0.username, i0.vDate
| )
| go

And here's my sandbox:

With 5 minutes for @.interval, this was typical:

User_one 8/30/2005 1:28 PM 8/30/2005 1:30 PM 2
User_one 8/30/2005 1:36 PM 8/30/2005 1:37 PM 1
User_two 8/26/2005 12:40 PM 8/26/2005 12:42 PM 2
User_two 8/29/2005 6:52 AM 8/29/2005 6:55 AM 3
User_two 8/29/2005 10:34 AM 8/29/2005 10:38 AM 4
User_three 8/30/2005 3:52 PM 8/30/2005 3:59 PM 7
User_three 8/30/2005 4:06 PM 8/30/2005 4:07 PM 1
User_four 8/25/2005 12:17 PM 8/25/2005 12:18 PM 1
User_four 8/25/2005 1:33 PM 8/25/2005 2:02 PM 29
User_four 8/25/2005 2:02 PM 8/25/2005 2:21 PM 19
User_four 8/25/2005 2:28 PM 8/25/2005 2:32 PM 4
User_four 8/25/2005 2:44 PM 8/25/2005 3:27 PM 43
User_four 8/25/2005 4:28 PM 8/25/2005 4:30 PM 2
User_four 8/26/2005 3:17 PM 8/26/2005 3:19 PM 2
User_four 8/30/2005 4:28 PM 8/30/2005 4:29 PM 1

There's a LOT of work to do yet on this. Not bad for starters though.

Thanks again for pulling the cord on this old lawn-mower.

Scott|||Scott Marquardt opined thusly on Aug 31:

> OK, here's the result [...]

>| on b.vtime < e.vtime and b.username = e.username
>|Aaugh where
>| not exists

Pardon that. That was supposed to go into an instant message, not this
post. ;-)

Scott|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Need I go on, or do you now have enough examples to know that in the
> real world, time does NOT always have start and stsop time.

When I read your post, it was quite clear that there was something
fundamentally wrong with it, but I could not just put my finger on it.

Until I came to this last paragraph. You are seriously trying to refer
to real world in an argument with Joe Celko?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> The poststamp on the envelope is stamped by the Dutch postal service. This stamp includes a date: "22 VIII 05". Please
don't tell me that this means that they started stamping it on midnight
and took a full 24 hours before the stamping was done. <<

Do you really use that format?? I thought that Roman Numeral dates
went out with the NATO Standards under De Gaul. My age is showing.

For legal purposes in the US, that postmark would be the duration
('2005-08-22 00:00:00' to '2005-08-22 23:59:59.9999..)

>> How about police work? An officer is checking the streets, and at 11:47 AM he sees you driving through a red light. What's the start and stop times of that? <<

That deals with rounding errors and precision. The way I drive, it
means that the cop did not have a watch that goes to microseconds :)
The conceptual model is that it took some time for me to go thru the
intersection at 100 MPH.

I am starting to like the MySQL convention of 'yyyy-mm-00' for a whole
month range and ''yyy-00-00' for a whole year range, but I have trouble
with '0000-00-00' for eternity.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1125513734.900268.311850@.g49g2000cwa.googlegr oups.com...
> >> you must be aware that web requests, like most other events in
computing, are not ever logged as durations, but as instants.<<
> Do not confuse the recording of the data with the data model.

No one is.

> Think
> about a sign-in and sign-out sheet or timeclock. Each line is "half a
> fact"; the whole fact is the duration spent on the job. In this case,
> the user logs onto a site, stays there for x-minutes. He is not there
> for a Chronon (that is the term for a point in time in temporal
> databases).

What you're missing is most website log information is stateless. The
concept of a "duration" doesn't necessarily exist with webpages.

I.e you go to www.google.com and get a page.

Google records you requested a page. They have no idea how long you look at
it. You could get up, go have lunch, go for a walk, etc.

Shut down your computer, go to a different site, etc.

Celko, I suggest you go look at the logs of a webserver sometime. A
webpage is recorded as an instant in time.

Yes, one can try to model a visitors travel through a site, but one is not
necessarily modelling reality. They may pull up a page, go away for 5
minutes, and hit a link.

From that you can derive a "duration" they were on that page, but not
necessarily.

As I said, they could close their browser. You record no duration.
They could click a link to another site, nothing gets recorded in your logs.
Again, no duration.
They could simply type in a different URL, nothing gets recorded in your
logs. Again, no duration.
Or, they could go out for lunch, and come back and hit another page on your
site. But does that really mean that they spent a duration of an hour on
your site? Not raelly.

> So his table MIGHT look like this:
> CREATE TABLE Browsing
> (user_id VARCHAR(30) NOT NULL,
> website VARCHAR(255) NOT NULL,
> login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> logout_time TIMESTAMP, -- null means still there
> CHECK (login_time <= logout_time), -- less than one minute problem
> PRIMARY KEY (user_id, website, login_time));
> The real problem in this situaiton is having to round to the minute.
> We can force a convention on the stopping time to keep it away from the
> starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
> to 'yyyy-mm-ddThh:mm:59.997').
>
> Ever read the paradoxes of Zeno? He went thru what happens when you
> believe in Chronons. He lived in a time when Greek math was "integer
> only" and without a continuum.|||On Wed, 31 Aug 2005 16:49:20 -0500, Scott Marquardt wrote:

> Scott Marquardt opined thusly on Aug 31:
>> OK, here's the result [...]
>>| on b.vtime < e.vtime and b.username = e.username
>>|Aaugh where
>>| not exists
> Pardon that. That was supposed to go into an instant message, not this
> post. ;-)

But it's so appropriate! Reminiscent of the required "PLEASE" statements
in INTERCAL (Language Without A Good Acronym).|||I did not say that very well. The model is that the traffic light was
modeled by Lights (color, start_time, end_time). and that I was in the
intersection with ('red', 2005-09-01 12:00:00', '2005-09-01
12:20:00'). I need to talk to the city about a traffic light with a 20
minute duration.|||Joe,

That's a very valid observation about the seperation of the data model
from the data recorded; there are two logistical problems raised by
this, however:

1. The tools that collect web traffic information (typically firewall
syslog) are at best a proxy measure for web usage; what they really
capture is connection information. The firewall doesn't know what
happens to a packet when it passes by; all it can say is that at this
instant (Chronon; neat term), a packet passed through the firewall from
one computer to the next.

Many people use this information to try and gather web usage, but it's
an imperfect model. There is no accurate way (as Scott said earlier)
to indicate how much time a person actually spent interacting with a
web site. All that can be said for certain is that a packet passed
from a machine that's associated with that user to the Internet at x
time.

2. The other logistical problem that is raised is the issue of
multitasking. Right now, I have 6 browser tabs open (in Firefox on a
multi-monitor system); I switch back and forth looking at different
information. How much time am I spending on a website? It can't be
measured looking at syslog data because every time I interact with a
different web page, a connection event gets recorded. Any report that I
run trying to decipher my web behavior would be nearly impossible to
interpret (e.g., Stu went to CNN then to Google Groups back to CNN, to
email, back to CNN, and then to Google Groups. All in the span of a
few seconds).

Stu|||"Time is what keeps everythign from happening at once!" -- George
Karlin.

Yes, parallelism is a bitch. I'd handle the multitasking by modeling
the session/connection rather than the user, then attach the user to
each of those. But the truth is that things were in durations -- maybe
short ones (Aunt Mabel's photos) or long ones (disgusting_porno.com)
and we have the problem of only being able to catch "half a fact".

I did a schema for a company that does a electronic timeclock system
that recorded nothing but the id of a fob and a UTC time. The fobs are
color coded for each job and you touch them to the mil spec timeclock
-- it looks like a pad lock that can take a bullet at point blank
range. If you think about it, you can do a lot with a minimal amount
of data.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1125612157.839762.92160@.o13g2000cwo.googlegro ups.com...
> "Time is what keeps everythign from happening at once!" -- George
> Karlin.
> Yes, parallelism is a bitch. I'd handle the multitasking by modeling
> the session/connection rather than the user, then attach the user to
> each of those. But the truth is that things were in durations -- maybe
> short ones (Aunt Mabel's photos) or long ones (disgusting_porno.com)
> and we have the problem of only being able to catch "half a fact".

Joe, again, I will repeat the point that many websits/pages don't have any
concept of session/connection. It's stateless.

So yes, exactly the problem is being able to catch "half a fact". The
"fact" in this case is the user requested the page at time A. Nothing more,
nothing less.

> I did a schema for a company that does a electronic timeclock system
> that recorded nothing but the id of a fob and a UTC time. The fobs are
> color coded for each job and you touch them to the mil spec timeclock
> -- it looks like a pad lock that can take a bullet at point blank
> range. If you think about it, you can do a lot with a minimal amount
> of data.|||On 31 Aug 2005 15:40:46 -0700, --CELKO-- wrote:

>>> The poststamp on the envelope is stamped by the Dutch postal service. This stamp includes a date: "22 VIII 05". Please
>don't tell me that this means that they started stamping it on midnight
>and took a full 24 hours before the stamping was done. <<
>Do you really use that format?? I thought that Roman Numeral dates
>went out with the NATO Standards under De Gaul. My age is showing.

Hi Joe,

I don't. But the Dutch postal service does.

>For legal purposes in the US, that postmark would be the duration
>('2005-08-22 00:00:00' to '2005-08-22 23:59:59.9999..)

Ah. So you really believe that the letter was in the machine that stamps
the stamps for a full 24 hours?

>>> How about police work? An officer is checking the streets, and at 11:47 AM he sees you driving through a red light. What's the start and stop times of that? <<
>That deals with rounding errors and precision. The way I drive, it
>means that the cop did not have a watch that goes to microseconds :)
>The conceptual model is that it took some time for me to go thru the
>intersection at 100 MPH.

But the warrant (is that the correct term? Bablefish thinks it is) does
not show how long it took you to go through an intersection. It shows at
what instant in the time continuum the officer measured your speed, the
result of that measurement, and the fine you'll have to pay.

>I am starting to like the MySQL convention of 'yyyy-mm-00' for a whole
>month range and ''yyy-00-00' for a whole year range,

Really?

What will MySQL return if you execute

SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
WHERE TheDay < '2005-08-00'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> What will MySQL return if you execute
SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
WHERE TheDay < '2005-08-00' <<

I don't know; I have not played with MySQL. However, Snodgrass defined
a set of relationships between internvals -- overlaps, precedes,
during, etc. to replace the usual scalar comparisons.|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> What will MySQL return if you execute
> SELECT *
> FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
> WHERE TheDay < '2005-08-00'

It produces a syntax error. It appears that it does not support
naming the columns for the derived table in that fashion. But
this query:

SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-09-01' AS datetime)) AS X
WHERE TheDay < '2005-08-00'

Returns:

+-------+
| TheDay |
+-------+
| 2005-07-20 00:00:00 |
+-------+
1 row in set (0.00 sec)

I also tried:

SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-08-01' AS datetime)) AS X
WHERE TheDay < CAST('2005-08-00' AS datetime)

With the same result, whereas changing 2005-08-01 to 2005-07-31 added
that date to the result set.

After all, the concept is not that difficult. 2005-08-00 is just a point
between 2007-07-31 and 2007-08-01. The internal representation is probably
not a plain numeric value as in SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> After all, the concept is not that difficult. 2005-08-00 is just a point
> between 2007-07-31 and 2007-08-01. The internal representation is probably
> not a plain numeric value as in SQL Server.

Some more findings. What would you expect this to return:

From what Joe said I would expect two rows, but I got no rows back.

And, yes, this works:

mysql> CREATE TABLE xf(a datetime NOT NULL)
-> ;
Query OK, 0 rows affected (2.24 sec)

mysql> INSERT xf (a) VALUES('2008-05-00')
-> ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from xf
-> ;
+-------+
| a |
+-------+
| 2008-05-00 00:00:00 |
+-------+
1 row in set (0.01 sec)

In fact, this was accepted too:

INSERT xf (a) VALUES('2008-05-420')

This "date" is then presented as 0000-00-00.

Explicit conversion to integer does not seem to be permitted, but implicit
appears to happen here:

mysql> select 1 + a from xf;
+------+
| 1 + a |
+------+
| 20080500000001 |
| 1 |
+------+

Funny creature, MySQL!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sat, 3 Sep 2005 09:46:22 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> What will MySQL return if you execute
>>
>> SELECT *
>> FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
>> SELECT CAST('2005-08-10' AS datetime) UNION ALL
>> SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
>> WHERE TheDay < '2005-08-00'
>It produces a syntax error. It appears that it does not support
>naming the columns for the derived table in that fashion. But
>this query:
> SELECT *
> FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-09-01' AS datetime)) AS X
> WHERE TheDay < '2005-08-00'
>Returns:
> +-------+
> | TheDay |
> +-------+
> | 2005-07-20 00:00:00 |
> +-------+
> 1 row in set (0.00 sec)
>I also tried:
> SELECT *
> FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-08-01' AS datetime)) AS X
> WHERE TheDay < CAST('2005-08-00' AS datetime)
>With the same result, whereas changing 2005-08-01 to 2005-07-31 added
>that date to the result set.
>After all, the concept is not that difficult. 2005-08-00 is just a point
>between 2007-07-31 and 2007-08-01. The internal representation is probably
>not a plain numeric value as in SQL Server.

Hi Erland,

Thanks for giving this a try. The enxt step (I actually intended this to
be the first step, but typo-ed when writing the message) would be to
test:

SELECT *
FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
SELECT CAST('2005-08-10' AS datetime) UNION ALL
SELECT CAST('2005-09-01' AS datetime)) AS X
WHERE TheDay > '2005-08-00'

Based on the concvept of using yyyy-mm-00 as a range, I'd expect one
row. But based on your other findings, I'm afraid that you will actually
get two rows.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 2 Sep 2005 18:50:50 -0700, --CELKO-- wrote:

>>> What will MySQL return if you execute
>SELECT *
>FROM (SELECT CAST('2005-07-20' AS datetime) UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-09-01' AS datetime)) AS X(TheDay)
>WHERE TheDay < '2005-08-00' <<
>I don't know; I have not played with MySQL. However, Snodgrass defined
>a set of relationships between internvals -- overlaps, precedes,
>during, etc. to replace the usual scalar comparisons.

Hi Joe,

If you haven't tested how this non-ANSI, non-portable, super-proprietary
MySQL daterange representation behaves in actual queries, then what
exactly do you base this statement on:

>I am starting to like the MySQL convention of 'yyyy-mm-00' for a whole
>month range and ''yyy-00-00' for a whole year range,

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 3 Sep 2005 18:10:52 +0000 (UTC), Erland Sommarskog wrote:

(snip)
>Funny creature, MySQL!

Hi Erland,

You can say that again.

But it's also quite worrying to see a guru as Joe Celko actually endorse
the way MySQL handles dates...

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Thanks for giving this a try. The enxt step (I actually intended this to
> be the first step, but typo-ed when writing the message) would be to
> test:
> SELECT *
> FROM (SELECT CAST('2005-07-20' AS datetime) AS TheDay UNION ALL
> SELECT CAST('2005-08-10' AS datetime) UNION ALL
> SELECT CAST('2005-09-01' AS datetime)) AS X
> WHERE TheDay > '2005-08-00'
> Based on the concvept of using yyyy-mm-00 as a range, I'd expect one
> row. But based on your other findings, I'm afraid that you will actually
> get two rows.

Confirmed.

> But it's also quite worrying to see a guru as Joe Celko actually endorse
> the way MySQL handles dates...

Joe will have to come up with in his own excuses, but we know that he
is often wrong about MS SQL Server, so why should he know MySQL any
better? :-)

Anyway, if you want more musings about MySQL, have a look at
http://sql-info.de/mysql/gotchas.html. Section 1.14 relates to
this thread. (In fairness, there are enough weirdnesses in SQL Server
to warrant a similar list for SQL Server.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hey, all I said was that I liked the syntax as a shorthand for an
interval. I do not like MySQL and consider it to be a file system and
not much of a DB yet.

Thursday, March 29, 2012

Alternating Detail Sections

Alternate ways of deleting records - without logging

Sunday, March 25, 2012

Alter table with PRIMARY KEY

Tuesday, March 20, 2012

Alter table db size getting increased.

Monday, March 19, 2012

Alter Table Alter Column

alter table - change time evaluation

Thursday, March 8, 2012

ALTER how long should it take?

Saturday, February 25, 2012

alter column

Friday, February 24, 2012

almost duplicate records

Monday, February 13, 2012

Allocate to records, randomize rounded amount

Allocate Range of AutoNumber ID's to each user

Sunday, February 12, 2012

All records within x minutes of each other

All records within x minutes of each other

Sql Server Alignment problem

Blog Archive

About Me