uninstall SQL: columns

Showing posts with label columns. Show all posts

Tuesday, March 27, 2012

Case Statement help

I am trying to create columns from calculations. Essentially what I want is
this:
Column1 = A then (Column2 - Column3) as ActualAmount else
Column1 = B then (Column2 - Column3) as BudgetAmount
Maybe I am not thinking straight, but this seems like it should be simple.
Here is what I started with:
Case When GLBA.ACTUAL_FLAG = 'A' Then
GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
ActQtrBalance,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as ActBeginBalance,
GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActPeriodNet,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActEndBalance
Else
GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
BudQtrBalance,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as BudBeginBalance,
GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudPeriodNet,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudEndBalance
End
I thought Decode was the way to go but can't seem to make that work either.
Please and Thank you
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1David,
Use as an example - listing two columns because you have two column aliases
can account for NULLs if required.
Try:
CREATE TABLE TESTTABLE300
(COLUMN1 CHAR(1) NOT NULL,
COLUMN2 INT NOT NULL,
COLUMN3 INT NOT NULL)
INSERT TESTTABLE300
VALUES('A',1000,300)
INSERT TESTTABLE300
VALUES('B',1000,600)
SELECT COLUMN1,
CASE COLUMN1 WHEN 'A' THEN (Column2 - Column3) END as ActualAmount,
CASE COLUMN1 WHEN 'B' THEN (Column2 - Column3) END as BudgetAmount
FROM TESTTABLE300
--DROP TABLE TESTTABLE300
HTH
Jerry
"David P via webservertalk.com" <u12188@.uwe> wrote in message
news:5653e494447eb@.uwe...
>I am trying to create columns from calculations. Essentially what I want is
> this:
> Column1 = A then (Column2 - Column3) as ActualAmount else
> Column1 = B then (Column2 - Column3) as BudgetAmount
> Maybe I am not thinking straight, but this seems like it should be simple.
> Here is what I started with:
> Case When GLBA.ACTUAL_FLAG = 'A' Then
> GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
> ActQtrBalance,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as
> ActBeginBalance,
> GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActPeriodNet,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
> PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActEndBalance
> Else
> GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
> BudQtrBalance,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as
> BudBeginBalance,
> GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudPeriodNet,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
> PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudEndBalance
> End
> I thought Decode was the way to go but can't seem to make that work
> either.
> Please and Thank you
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||So I just need to create a CASE statement for each calculation.
I'll give that a try.
Jerry Spivey wrote:
>David,
>Use as an example - listing two columns because you have two column aliases
>can account for NULLs if required.
>Try:
>CREATE TABLE TESTTABLE300
>(COLUMN1 CHAR(1) NOT NULL,
> COLUMN2 INT NOT NULL,
> COLUMN3 INT NOT NULL)
>INSERT TESTTABLE300
>VALUES('A',1000,300)
>INSERT TESTTABLE300
>VALUES('B',1000,600)
>SELECT COLUMN1,
> CASE COLUMN1 WHEN 'A' THEN (Column2 - Column3) END as ActualAmount,
> CASE COLUMN1 WHEN 'B' THEN (Column2 - Column3) END as BudgetAmount
>FROM TESTTABLE300
>--DROP TABLE TESTTABLE300
>HTH
>Jerry
>[quoted text clipped - 28 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1

Sunday, March 25, 2012

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they are
case sensitive?
Thanks in advance.
John Steen
It appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>
|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a crit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a crit :
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:

> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they are
> case sensitive?
> Thanks in advance.
> John Steen
>
|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
Hugo Kornelis, SQL Server MVP
|||Thanks, Hugo. And you're right, I do need to learn to write more than simple
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:

> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
>
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they ar
e
case sensitive?
Thanks in advance.
John SteenIt appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a crit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a crit :
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle m
y
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:

> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
are
> case sensitive?
> Thanks in advance.
> John Steen
>|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle
my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
Hugo Kornelis, SQL Server MVP|||Thanks, Hugo. And you're right, I do need to learn to write more than simpl
e
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:

> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
>
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they are
case sensitive?
Thanks in advance.
John SteenIt appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a écrit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a écrit :
>> It appears your database default collation is case-insensitive. You can
>> change the collation for a specific column to a case-sensitive one with
>> ALTER TABLE:
>> CREATE TABLE MyTable
>> (
>> MyPassword varchar(10)
>> )
>> INSERT INTO MyTable VALUES('password')
>> --row found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'PASSWORD'
>> ALTER TABLE MyTable
>> ALTER COLUMN MyPassword varchar(10)
>> COLLATE SQL_Latin1_General_CP1_CS_AS
>> --row not found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'PASSWORD'
>> --row found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'password'
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they are
> case sensitive?
> Thanks in advance.
> John Steen
>|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:
>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
--
Hugo Kornelis, SQL Server MVP|||Thanks, Hugo. And you're right, I do need to learn to write more than simple
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:
> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
> >Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
> >way through it that way if necessary, but is there a way to do this in
> >Enterprise Manager? Can I do this through the "Design Table" atributes?
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

Thursday, March 22, 2012

Case order by

Hello
I was wondering if you guys could help me, I have a table with Firstname and
Lastname columns.
And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can sort
Firstname ASC/DESC or Lastname ASC/DESC....
how's that done, i need to put 4 different Cases?
TIA
/LasseWhat about that
Use northwind
DECLARE @.col varchar(200)
DECLARE @.Direc varchar(200)
SET @.col = 'OrderID'
SET @.Direc = 'ASC'
EXEC('Select * from Orders Order by ' + @.col + ' ' + @.Direc)
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lasse Edsvik" <lasse@.nospam.com> schrieb im Newsbeitrag
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||SELECT <column list>
FROM <table>
ORDER BY CASE @.AscDesc WHEN 'ASC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END ASC,
CASE @.AscDesc WHEN 'DESC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END DESC,
Jacco Schalkwijk
SQL Server MVP
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||Lasse
ORDER BY CASE WHEN @.sort = 'col1' AND @.dir = 1 THEN col1 END ASC,
CASE WHEN @.sort = 'col1' AND @.dir = 0 THEN col1 END DESC,
CASE WHEN @.sort = 'col2' AND @.dir = 1 THEN col2 END ASC,
CASE WHEN @.sort = 'col2' AND @.dir = 0 THEN col2 END DESC
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Lasse Edsvik" wrote:

> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>|||The below uses one input variable to control which column to sort by, and
whether to cort asc or desc...
Use NorthWind
Declare @.Order TinyInt
Set @.Order = 1 -- 2,3,4
-- --
Select * From Customers
Order By Case @.Order
When 1 Then CompanyName
When 3 Then ContactName
End,
Case @.Order
When 2 Then CompanyName
When 4 Then ContactName
End Desc
"Lasse Edsvik" wrote:

> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>sql

Tuesday, March 20, 2012

case expression plus outer join in ole db source

I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.

In SQL Server Management Studio, this works fine:

insert into table_3(column_a, column_b)

select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b

from table_1 left outer join table_2 on table_1.column_c = table_2.column_c

That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)

But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:

There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420

The column status returned was: "The value violated the integrity constraints for the column.".

And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.

So what is SSIS doing to screw up the results of the SELECT command?

Kevin,

Can you see the values of column_b when you use the 'preview' of the OLE DB source?

Did you check twice the mapping tab in your OLE DB destination to make sure nothing is missing?

Have you used a data view right before the OLE DB Destination to check the values of column_b are shown correctly?

If the answer to those 3 questions is yes, I am affraid I could not help you.

Rafael Salas

|||Could there be an issue in executing the newid() function?|||

Argh, the problem was due to operator error: somehow the source component SQL statement had gotten out of sync with the corresponding variable. How embarrassing!

But many thanks to Rafael and Phil, who got me to examine the data flow task closely enough to discover my error.

case expression in where clause and null's

Using SQL Server 2005
I have a simple table example with two columns: FirstName varchar(20) and
LastName varchar(20)
I am doing something like this in a stored procedure where @.firstname and
@.lastname are passed in and @.lastname could be null
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, LastName)
I am also using "set ansi_nulls off".
The query should give names where LastName is null if @.Lastname = null but
that's not happening. Why?
John DalbergBecause COALESCE returns the first non-NULL value. If @.LastName is null,
then it won't return @.LastName, it will Return LastName (the column, not the
variable).
Essentially making your stmt:
select * from table where FirstName = @.firstname and LastName = LastName
or rather
select * from table where FirstName = @.firstname
"John Dalberg" <nospam@.nospam.sss> wrote in message
news:20060405191639.387$oV@.newsreader.com...
> Using SQL Server 2005
> I have a simple table example with two columns: FirstName varchar(20) and
> LastName varchar(20)
> I am doing something like this in a stored procedure where @.firstname and
> @.lastname are passed in and @.lastname could be null
> select * from table where FirstName = @.firstname and LastName =
> COALESCE(@.LastName, LastName)
> I am also using "set ansi_nulls off".
> The query should give names where LastName is null if @.Lastname = null but
> that's not happening. Why?
> John Dalberg|||>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
It sounds like this might be confusion over how NULL works.
If table.LastName IS NULL, and @.LastName IS NULL, then
COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
the test:
LastName = COALESCE(@.LastName, LastName)
resolves to:
NULL = NULL
Which comparison will never resolve to TRUE. NULL is never equal to
anything, including another NULL. Consider these comparisons:
NULL = 'banana'
NULL <> 'banana'
NULL = NULL
NULL <> NULL
None of those can ever be resolved as TRUE, because equality (or
inequality) can only result from comparing something to something.
NULL is nothing, and can not be compared at all.
Roy Harvey
Beacon Falls, CT
On 05 Apr 2006 23:06:02 GMT, nospam@.nospam.sss (John Dalberg) wrote:

>Using SQL Server 2005
>I have a simple table example with two columns: FirstName varchar(20) and
>LastName varchar(20)
>I am doing something like this in a stored procedure where @.firstname and
>@.lastname are passed in and @.lastname could be null
>select * from table where FirstName = @.firstname and LastName =
>COALESCE(@.LastName, LastName)
>I am also using "set ansi_nulls off".
>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
>John Dalberg|||Roy Harvey <roy_harvey@.snet.net> wrote:
> It sounds like this might be confusion over how NULL works.
> If table.LastName IS NULL, and @.LastName IS NULL, then
> COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
> the test:
> LastName = COALESCE(@.LastName, LastName)
> resolves to:
> NULL = NULL
> Which comparison will never resolve to TRUE. NULL is never equal to
> anything, including another NULL. Consider these comparisons:
> NULL = 'banana'
> NULL <> 'banana'
> NULL = NULL
> NULL <> NULL
> None of those can ever be resolved as TRUE, because equality (or
> inequality) can only result from comparing something to something.
> NULL is nothing, and can not be compared at all.
But when you have set ansi_nulls off and run:
select * from table where lastname = null, it returns rows where lastname=
null
Doesn't that statement translate to:
select * from table where null = null ?
and when you set ansi_nulls on
one needs to write it as: select * from table where lastname is null ?
John Dalberg|||take a look at this
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 is null and @.v2 is null
print 'Both null'
else
print 'both not null'
go
set ansi_nulls off
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
set ansi_nulls on
go
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = @.firstname and LastName =
> LastName or rather
> select * from table where FirstName = @.firstname
What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, @.LastName)
Would that translate to:
select * from table where FirstName = @.firstname and LastName = null ?
Doesn't 'set ansi_null off' make 'null =null' evaluate to true?
Anyways, I need the where clause to include the lastname if @.lastname has a
value and return null lastnames rows if @.lastname is null. I couldn't find
a way to do it in a CASE expression. I can do it in a dynamic sql.
John Dalberg
> "John Dalberg" <nospam@.nospam.sss> wrote in message
> news:20060405191639.387$oV@.newsreader.com...|||>>What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
COALESCE will return the first non NULL value
Here is an example
declare @.v1 int,@.v2 int,@.v3 int,@.v4 int
select @.v1 = null,@.v2 = null,@.v3 =4,@.v4 =8
select coalesce(@.v1,@.v2,@.v3,@.v4)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote:
> take a look at this
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 is null and @.v2 is null
> print 'Both null'
> else
> print 'both not null'
> go
> set ansi_nulls off
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> set ansi_nulls on
> go
I understand the principles. That's why I included set ansi_nulls off in my
clarification.|||It is easier to communicate using DDL. I've added DDL at the end of this tex
t so we can use that
from here on.
Let me see if I can re-phrase your question:
You have some rows which has NULL on the lastname column. You want to find t
hem using ANSI_NULLS OFF
and by passing in NULL in the @.lastname parameter of your stored procedure.
So, in the query, you
have the following condition:
AND LastName = COALESCE(@.LastName, LastName)
So, if if you pass NULL for the @.lastname parameter, the condition will tran
slate to:
AND LastName = LastName
And you wonder why that will not return the rows where you have NULL in the
lastname column. Is that
correct?
If so, read in Books Online about SET ANSI_NULLS OFF. It only comments about
comparsions between a
column and NULL, not between two columns where each has NULL. I guess that t
his is how Sybase
defined it some 20 years ago, and MS has kapt this behavior. You could do a
BOL feedback and ask
them to clarify this in the 2005 BOL.
DDL:
USE tempdb
CREATE TABLE t(firstname varchar(30) not null, lastname varchar(30) null)
insert into t (firstname, lastname)
VALUES('John', 'Dalberg')
insert into t (firstname, lastname)
VALUES('Franz', NULL)
SET ANSI_NULLS OFF
GO
CREATE PROC p
@.firstname varchar(30), @.lastname varchar(30)
AS
SELECT firstname, lastname
from t
where FirstName = @.firstname
AND LastName = COALESCE(@.LastName, LastName)
GO
EXEC p 'John', 'Dalberg'
EXEC p 'John', NULL
EXEC p 'Franz', NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060406121819.765$iB@.newsreader.c
om...
> Roy Harvey <roy_harvey@.snet.net> wrote:
> But when you have set ansi_nulls off and run:
> select * from table where lastname = null, it returns rows where lastname=
> null
> Doesn't that statement translate to:
> select * from table where null = null ?
> and when you set ansi_nulls on
> one needs to write it as: select * from table where lastname is null ?
> John Dalberg|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = ffirstname and LastName =
> LastName or rather
Right and that's the way it should work so when you have ansi_nulls off,
lastname = lastname will return rows where lastname is null and it will not
return these rows if set ansi_nulls on. I am missing what was wrong in my
statement.
so:
set ansi_nulls off;select * from table where FirstName = firstname and
LastName =lastname
&
select * from table
both return the same # of rows regardless whether lastname is null or not.
John Dalbergsql

Monday, March 19, 2012

CASE and Inserting Commas

I want to build a CASE statement in SQL to transform data. The SQL is based
on Race. I have the following columns...
RaceAA(American Indian or Alaska Native)
RaceNH(Native Hawaiian)
RaceA(Asian)
RaceW(White)
RaceB(Black)
There are 'X's in these columns designating the Race and more than one may
be 'X'ed.
I want to transform the 'X's into numbers with commas in between. For
Example, if Tiger Woods came into our DataBase, the RaceA would have an 'X'
and needs to be transposed to '2', RaceW would have an 'X' and needs to be
transformed to '5', and RaceB would have an 'X' and needs to be transposed t
o
'3'.
My result should look like...2,5,3
CASE WHEN RaceA = 'X' then '2,'
WHEN RaceW = 'X' then '5,'
WHEN RaceB = 'X' then '3,'
would give me 2,5,3,
My concern here is the trailing ",". Is there any way to CASE the comma if
multiple Race columns are checked and work in the commas appropriately?
Am I asking SQL questions in the right forum?> I have the following columns...
> RaceAA(American Indian or Alaska Native)
> RaceNH(Native Hawaiian)
> RaceA(Asian)
> RaceW(White)
> RaceB(Black)
Why? Why not have a column called "Race" and then provide values to that
column, such as 'AA','NH','A','W','B', etc.

> There are 'X's in these columns designating the Race and more than one may
> be 'X'ed.
Someone can be Asian and Black and White and American Indian?

> I want to transform the 'X's into numbers with commas in between. For
> Example, if Tiger Woods came into our DataBase, the RaceA would have an
> 'X'
> and needs to be transposed to '2', RaceW would have an 'X' and needs to be
> transformed to '5', and RaceB would have an 'X' and needs to be transposed
> to
> '3'.
Why do you want to store this race code mapping in your code? Wouldn't it
make more sense to use a numeric code *in the data* (and have a lookup
table)? Then you don't have to go read stored procedure code to figure out
what the heck kind of person a 3 is, and you don't have to worry about
making sure all queries that use this logic match.

> would give me 2,5,3,
> My concern here is the trailing ",". Is there any way to CASE the comma if
> multiple Race columns are checked and work in the commas appropriately?
Well, you could produce 2,5,3, in a subquery, and in the outer query, say:
SELECT RaceList = CASE RIGHT(List,1)
WHEN ',' THEN LEFT(List, LEN(List)-1)
ELSE List
END
FROM
(
SELECT List = '2,5,3,'
UNION
SELECT List = '4'
) x
Or, you could do something really novel, and inject the commas where they
belong, on the presentation tier.

> Am I asking SQL questions in the right forum?
Yes but you should look at alternative designs. This is far from optimal.|||CASE seems to Mutually Exclusive. Is there any way to continue the CASE
statement so it looks at all the race columns and builds the result column
accordingly. In other words, once it sees that the "White" column has an "X"
,
it goes to the end when I need it to look at ALL the race columns.
Any help you could provide me here would be GREATLY appreciated!
Thanks!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why? Why not have a column called "Race" and then provide values to that
> column, such as 'AA','NH','A','W','B', etc.
>
> Someone can be Asian and Black and White and American Indian?
>
> Why do you want to store this race code mapping in your code? Wouldn't it
> make more sense to use a numeric code *in the data* (and have a lookup
> table)? Then you don't have to go read stored procedure code to figure ou
t
> what the heck kind of person a 3 is, and you don't have to worry about
> making sure all queries that use this logic match.
>
> Well, you could produce 2,5,3, in a subquery, and in the outer query, say:
> SELECT RaceList = CASE RIGHT(List,1)
> WHEN ',' THEN LEFT(List, LEN(List)-1)
> ELSE List
> END
> FROM
> (
> SELECT List = '2,5,3,'
> UNION
> SELECT List = '4'
> ) x
> Or, you could do something really novel, and inject the commas where they
> belong, on the presentation tier.
>
> Yes but you should look at alternative designs. This is far from optimal.
>
>|||You can string the CASE expressions together:
CASE WHEN X = 1 THEN 'A' ELSE '' END+
CASE WHEN X = 2 THEN 'B' ELSE '' END+
CASE WHEN X = 3 THEN 'C' ELSE '' END+ ...
but I share Aaron's views about the weakness of the design you are
implying. In particular, it's a mistake to design tables to mirror a UI
- columns are not check boxes!
David Portas
SQL Server MVP
--|||or:
CASE WHEN col1 = 1 THEN 'A' ELSE '' END+
CASE WHEN col2 = 1 THEN 'B' ELSE '' END+
CASE WHEN col3 = 1 THEN 'C' ELSE '' END+ ...
David Portas
SQL Server MVP
--

Case - Sensitive field

I want to specify the data in columns are both upper & lower case(i.e. one column data having all lowercase data or uppercase data ).

Ex:- if i have one column - col1 and its

1st value is RAKESH JHA

2nd value is rakesh jha

then how i can find that, how much row are in upper case. respectively lower case

Use UPPER and LOWER functions. Like:
select * from MyTable where UPPER(fCOL1)=fCOL1

|||

Have a look at

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Cascading updates question

I have two tables, Stock and Positions. Stock contains a Symbol column and a
Price column, and Symbol is the key. Positions contain the columns as well a
s
several other columns whose data should change with the Price. Positions can
have the same Symbol multiple times (it is keyed by Symbol-Account).
My design is to have a foreign key between the tables, so that when the
Price in the Stock column is updated, the Price in Positions also updates. I
am making some columns in the Positions table computed columns, so that they
recalculate when the Price updates.
My questions/concerns are:
1. Is this design any good? One alternative I was thinkning of was to use no
computed columns, and run a stored procedure frequently to update the
Positions.
2. Will the computed columns updated automatically when Price changes?
3. Will there be the possibility that some rows in Positions with the same
symbols are not updated simultaneously, so that the Price could be different
for the same symbol?
4. Do the rows lock while they are updating? This has implications because I
am querying this table often for other purposes.
5. If one computed column depends on another computed column, is there a way
to specify the order in which they calculate, or is this just a big no-no?
I am grateful for any insight.
Thank you,
CP Developer"CP Developer" <steved@.newsgroup.nospam> wrote in message
news:A40102EA-6AF1-4377-9332-09EFCAA83204@.microsoft.com...
>I have two tables, Stock and Positions. Stock contains a Symbol column and
>a
> Price column, and Symbol is the key. Positions contain the columns as well
> as
> several other columns whose data should change with the Price. Positions
> can
> have the same Symbol multiple times (it is keyed by Symbol-Account).
> My design is to have a foreign key between the tables, so that when the
> Price in the Stock column is updated, the Price in Positions also updates.
> I
> am making some columns in the Positions table computed columns, so that
> they
> recalculate when the Price updates.
> My questions/concerns are:
> 1. Is this design any good? One alternative I was thinkning of was to use
> no
> computed columns, and run a stored procedure frequently to update the
> Positions.
There are a few reasonable ways I can think of to have a comupted column
based on a column in a related table.
Put a phoney foreign key on (StockID, Price) and use cascade updates (your
idea).
Put a trigger on Stock to update the related positions.
Use a view to join the two tables and define the calculations there.

> 2. Will the computed columns updated automatically when Price changes?

> 3. Will there be the possibility that some rows in Positions with the same
> symbols are not updated simultaneously, so that the Price could be
> different
> for the same symbol?
No.

> 4. Do the rows lock while they are updating? This has implications because
> I
> am querying this table often for other purposes.
Yes. Make sure you have an index supporting the foreign key relationship.

> 5. If one computed column depends on another computed column, is there a
> way
> to specify the order in which they calculate, or is this just a big no-no?
>
No you cannot base one computed column on another. However you are free to
cut and paste the calculation for one column into the other.
Here's an example:
drop table position
drop table stock
go
create table stock
(
id int primary key,
price decimal(9,2),
constraint uk_id_price
unique (id,price)
)
create table position
(
account int not null, -- references account
stock int not null references stock,
price decimal(9,2) not null,
other_price as cast(price*.9 as decimal(9,2)),
constraint pk_position
primary key(account,stock),
constraint fk_position_stock_price
foreign key (stock,price)
references stock(id,price)
on update cascade
)
create index ix_position_stock_price
on position(stock,price)
go
insert into stock(id,price) values (1,3.50)
insert into position(account,stock,price) values (23,1,3.50)
go
update stock set price = 5.25 where id = 1
select * from position
David|||The columns will update when the price changes.
Create Table TableA
(
Symbol varchar(10),
Price Money,
Qty Int,
TotalCost As (Price * Qty)
)
Insert Into TableA
Select 'GBP', 14.52, 2
select * From TableA
Update TableA
Set Qty = 151, price = 45.65
select * From TableA
Drop table TableA
HTH
Barry|||I would store the price in one table only, and select it from there. If
you need you selects to be as fast as possible, consider using an
indexed view.

Friday, February 24, 2012

capture showplan in profiler

What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000Binary data column. There is also a pretty cool way of extracting the
information if you save/load the trace into a table here
http://www.umachandar.com/technical/SQL2000Scripts/UtilitySPs/Main9.htm
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uW4ukVqcDHA.652@.tk2msftngp13.phx.gbl...
What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000

Friday, February 10, 2012

can't update column if there are autocomputed columns?

Am I missing something?
Thank you very much for any input!
Michal Kreslik
michal.kreslik(at)kreslik.comThis is how the table looks like when scripted:
-- begin script
---
USE [FXdataDB]
GO
/****** Object: Table [dbo].[Tick] Script Date: 02/08/2007 23:04
:50
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tick](
[TickID] [int] IDENTITY(1,1) NOT NULL,
[SourceFileID] [smallint] NOT NULL,
[FXPairID] [tinyint] NOT NULL,
[Year] [smallint] NOT NULL,
[Month] [tinyint] NOT NULL,
[Day] [tinyint] NOT NULL,
[DayOfWeekID] [tinyint] NOT NULL,
[Hour] [tinyint] NOT NULL,
[Minute] [tinyint] NOT NULL,
[Second] [tinyint] NOT NULL,
[Bid] [real] NOT NULL,
[Ask] [real] NOT NULL,
[UniqueTick] [bit] NULL,
[IsWithinSession] AS
([dbo].& #91;GetIsWithinSessionWithSchemaBinding]
([DayOfWeekID],[
Hour],[Minute],[Second])) PERSISTED,
[MinsFromSStart] AS
([dbo].& #91;GetMinsFromSStartWithSchemaBinding](
[DayOfWeekID],[H
our],[Minute],[Second])) PERSISTED,
[SecsFromSStart] AS
([dbo].& #91;GetSecsFromSStartWithSchemaBinding](
[DayOfWeekID],[H
our],[Minute],[Second])) PERSISTED,
[TickTime] AS
((((([Second]+(100)*[Minute])+(10000)*[Hour])+(1000000)*[Day
])+(100000000)*[Month])+(10000000000.)*[Year]) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'' ,
@.level0type=N'SCHEMA',@.level0name=N'dbo'
,
@.level1type=N'TABLE',@.level1name=N'Tick'
,
@.level2type=N'COLUMN',@.level2name=N'Sour
ceFileID'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Holds the
number of minutes from Forex session start (17:00:00 EST Sunday)' ,
@.level0type=N'SCHEMA',@.level0name=N'dbo'
,
@.level1type=N'TABLE',@.level1name=N'Tick'
,
@.level2type=N'COLUMN',@.level2name=N'Mins
FromSStart'
---
-- end of script
As stated above, I am attempting to UPDATE the UniqueTick column here.
Thanks,
Michal Kreslik|||Do you have any indexes on the table? I expect that a table scan of 255
million rows to take a while, even if only 1 row is updated.
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:55499C83-6ACB-4AE1-8319-A2D5F05473E6@.microsoft.com...
> This is how the table looks like when scripted:
> -- begin script
> ---
> USE [FXdataDB]
> GO
> /****** Object: Table [dbo].[Tick] Script Date: 02/08/2007 23:
04:50
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[Tick](
> [TickID] [int] IDENTITY(1,1) NOT NULL,
> [SourceFileID] [smallint] NOT NULL,
> [FXPairID] [tinyint] NOT NULL,
> [Year] [smallint] NOT NULL,
> [Month] [tinyint] NOT NULL,
> [Day] [tinyint] NOT NULL,
> [DayOfWeekID] [tinyint] NOT NULL,
> [Hour] [tinyint] NOT NULL,
> [Minute] [tinyint] NOT NULL,
> [Second] [tinyint] NOT NULL,
> [Bid] [real] NOT NULL,
> [Ask] [real] NOT NULL,
> [UniqueTick] [bit] NULL,
> [IsWithinSession] AS
> ([dbo].& #91;GetIsWithinSessionWithSchemaBinding]
([DayOfWeekID],
1;Hour],[Minute],[Second]))
> PERSISTED,
> [MinsFromSStart] AS
> ([dbo].& #91;GetMinsFromSStartWithSchemaBinding](
[DayOfWeekID],[
;Hour],[Minute],[Second]))
> PERSISTED,
> [SecsFromSStart] AS
> ([dbo].& #91;GetSecsFromSStartWithSchemaBinding](
[DayOfWeekID],[
;Hour],[Minute],[Second]))
> PERSISTED,
> [TickTime] AS
> ((((([Second]+(100)*[Minute])+(10000)*[Hour])+(1000000)*[D
ay])+(100000000)*[Month])+(10000000000.)*[Year])
> PERSISTED
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> GO
> EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'' ,
> @.level0type=N'SCHEMA',@.level0name=N'dbo'
,
> @.level1type=N'TABLE',@.level1name=N'Tick'
,
> @.level2type=N'COLUMN',@.level2name=N'Sour
ceFileID'
> GO
> EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Holds
> the
> number of minutes from Forex session start (17:00:00 EST Sunday)' ,
> @.level0type=N'SCHEMA',@.level0name=N'dbo'
,
> @.level1type=N'TABLE',@.level1name=N'Tick'
,
> @.level2type=N'COLUMN',@.level2name=N'Mins
FromSStart'
> ---
> -- end of script
>
> As stated above, I am attempting to UPDATE the UniqueTick column here.
> Thanks,
> Michal Kreslik|||"Dan Guzman" wrote:

> Do you have any indexes on the table? I expect that a table scan of 255
> million rows to take a while, even if only 1 row is updated.
Yes, I have 6 indexes on the table. But before adding the extra two
autocomputed columns, UPDATE worked fine even with all those indexes in
place. It took about 5 ms to update.
So there's something fishy about that.
What this behaviour could be caused by?
Thanks,
Michal Kreslik|||> What this behaviour could be caused by?
Have you looked at the execution plan to ensure indexes are used as
expected? These symptoms can be due to blocking - have you checked activity
during the update?
I can try to repro if you provide the script for the user-defined function
and all indexes too. I didn't see anything obvious in the scripts you
posed. I assume your actual UPDATE statement is:
UPDATE Tick
SET UniqueTick = 1
WHERE TickID = 123456789
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:15AECAEA-A24D-496D-8101-80B21DAF63EC@.microsoft.com...
> "Dan Guzman" wrote:
>
> Yes, I have 6 indexes on the table. But before adding the extra two
> autocomputed columns, UPDATE worked fine even with all those indexes in
> place. It took about 5 ms to update.
> So there's something fishy about that.
> What this behaviour could be caused by?
> Thanks,
> Michal Kreslik|||"Dan Guzman" wrote:

> Have you looked at the execution plan to ensure indexes are used as
> expected? These symptoms can be due to blocking - have you checked activi
ty
> during the update?
> I can try to repro if you provide the script for the user-defined function
> and all indexes too. I didn't see anything obvious in the scripts you
> posed. I assume your actual UPDATE statement is:
> UPDATE Tick
> SET UniqueTick = 1
> WHERE TickID = 123456789
>
Thanks very much for your swift reply.
Yes, obviously I execute the statement with the correct syntax, sorry.
I have just checked the execution plan and everything seems to be ok.

>- have you checked activity during the update?
You mean, whether SQL server is using CPU time? No, there's almost no CPU
activity during this strange UPDATE.
I have uploaded the CREATE script for the entire database here:
http://kreslik.com/files/FXdataDB_Scripted_All.zip
Also, ever since I added those two autocomputed columns, the SQL server
seems to be "caching" something to disk all the time (using up to 50% of
CPU). It always starts a couple of minutes after the SQL server starts and
the only way to stop it (although I guess there's no reason why SQL server
should be "recomputing" persisted columns) was to restart the SQL server.
Today it seems it is no longer "caching", but I'm not sure why. Could this b
e
related to my problem? How do I find out what exactly what is SQL server
doing and eventually how do I put and end to such a nonsensical "caching"?
Thanks much for any help.
Michal Kreslik|||> You mean, whether SQL server is using CPU time? No, there's almost no CPU
> activity during this strange UPDATE.
What about blocking and disk activity?
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:A49FAE08-3EDD-42D1-9EA9-6E0A3FD139DE@.microsoft.com...
> "Dan Guzman" wrote:
>
> Thanks very much for your swift reply.
> Yes, obviously I execute the statement with the correct syntax, sorry.
> I have just checked the execution plan and everything seems to be ok.
>
> You mean, whether SQL server is using CPU time? No, there's almost no CPU
> activity during this strange UPDATE.
> I have uploaded the CREATE script for the entire database here:
> http://kreslik.com/files/FXdataDB_Scripted_All.zip
> Also, ever since I added those two autocomputed columns, the SQL server
> seems to be "caching" something to disk all the time (using up to 50% of
> CPU). It always starts a couple of minutes after the SQL server starts and
> the only way to stop it (although I guess there's no reason why SQL server
> should be "recomputing" persisted columns) was to restart the SQL server.
> Today it seems it is no longer "caching", but I'm not sure why. Could this
> be
> related to my problem? How do I find out what exactly what is SQL server
> doing and eventually how do I put and end to such a nonsensical "caching"?
> Thanks much for any help.
> Michal Kreslik|||"Dan Guzman" wrote:

> What about blocking and disk activity?
No, there doesn't seem to be any disk activity.
Michal Kreslik|||Hi, Michal. I haven't been able to repro your problem on this end. I
created the database (without the last 2 computed columns) and loaded the
Tick table with 10M rows of test data. I then added the computed columns
and index and the update statement ran quickly.
Can you post the output of the following query against the hung spid?
Perhaps this and/or the other dm views will provide a clue as to what's
going on.
SELECT *
FROM sys.dm_exec_requests
WHERE session_id = ?
FOR XML PATH ('dm_exec_requests')
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:8564911C-2F36-4938-9B4A-3829152D03C6@.microsoft.com...
> "Dan Guzman" wrote:
>
> No, there doesn't seem to be any disk activity.
> Michal Kreslik|||Hello, Dan,
thanks for this idea. Quite suprisingly, the UPDATE command started working
all by itself out of the blue.
But anyway, this strange behavior should not be left unexplained as it might
happen again.
I ran the SQL command you suggested against the (now running ok) UPDATE
spid. If you can see something that might have been causing the UPDATE
getting stuck, please let me know.
XML result:
<dm_exec_requests>
<session_id>58</session_id>
<request_id>0</request_id>
<start_time>2007-02-13T17:42:35.430</start_time>
<status>suspended</status>
<command>UPDATE</command>
<sql_handle>AgAAANT0Yi4+B5OkY6CYr8CIw0pT0XYp</sql_handle>
<statement_start_offset>30</statement_start_offset>
<statement_end_offset>-1</statement_end_offset>
<plan_handle>BgAHANT0Yi64oVkEAAAAAAAAAAAAAAAA</plan_handle>
<database_id>7</database_id>
<user_id>1</user_id>
<connection_id>0E8F7597-F1AC-44D5-AE5A-48F3081915E7</connection_id>
<blocking_session_id>0</blocking_session_id>
<wait_type>WRITELOG</wait_type>
<wait_time>0</wait_time>
<last_wait_type>WRITELOG</last_wait_type>
<wait_resource></wait_resource>
<open_transaction_count>1</open_transaction_count>
<open_resultset_count>1</open_resultset_count>
<transaction_id>1633798</transaction_id>
<context_info></context_info>
<percent_complete>0.0000000e+000</percent_complete>
<estimated_completion_time>0</estimated_completion_time>
<cpu_time>0</cpu_time>
<total_elapsed_time>2</total_elapsed_time>
<scheduler_id>0</scheduler_id>
<task_address>AGiKeA==</task_address>
<reads>135</reads>
<writes>325</writes>
<logical_reads>413692</logical_reads>
<text_size>2147483647</text_size>
<language>us_english</language>
<date_format>mdy</date_format>
<date_first>7</date_first>
<quoted_identifier>1</quoted_identifier>
<arithabort>0</arithabort>
<ansi_null_dflt_on>1</ansi_null_dflt_on>
<ansi_defaults>0</ansi_defaults>
<ansi_warnings>1</ansi_warnings>
<ansi_padding>1</ansi_padding>
<ansi_nulls>1</ansi_nulls>
<concat_null_yields_null>1</concat_null_yields_null>
<transaction_isolation_level>2</transaction_isolation_level>
<lock_timeout>-1</lock_timeout>
<deadlock_priority>0</deadlock_priority>
<row_count>1</row_count>
<prev_error>0</prev_error>
<nest_level>0</nest_level>
<granted_query_memory>0</granted_query_memory>
<executing_managed_code>0</executing_managed_code>
</dm_exec_requests>
Thank you very much, Dan,
Michal Kreslik

can't update column if there are autocomputed columns?

Hello,
I would greatly appreciate if someone could kindly help me in this matter:
I am running a large OLAP database with the biggest table of 255 million+
records. Recently I've added two autocomputed and persisted columns to this
table. Ever since then, I'm not able to update this table, although I am able
to INSERT into the table.
When I attempt to UPDATE, there's no error, but the update command takes
forever and nothing is happening. In Microsoft SQL Server Management Studio,
this is accompanied by a nice hourglass icon.
The table is properly indexed, so the problem is not there. The data is
accessible, the column that I am updating is not autocomputed nor is there
any restriction on that column whatsoever. It's of type "bit".
It worked before I added the two autocomputed columns. As I am updating this
OLAP database once a week, something else might have happened and this issue
is maybe not related to those autocomputed columns at all. But who knows.
Just for the sake of perfect clarity, let's say I'm going to run this command:
SELECT * FROM Tick WHERE TickID = 123456789
It completes immediately and returns the desired record. But the following
similar command:
UPDATE Tick WHERE TickID = 123456789 SET UniqueTick = 1
is a "permawaiter" :)
Am I missing something?
Thank you very much for any input!
Michal Kreslik
michal.kreslik(at)kreslik.comThis is how the table looks like when scripted:
-- begin script
---
USE [FXdataDB]
GO
/****** Object: Table [dbo].[Tick] Script Date: 02/08/2007 23:04:50
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tick](
[TickID] [int] IDENTITY(1,1) NOT NULL,
[SourceFileID] [smallint] NOT NULL,
[FXPairID] [tinyint] NOT NULL,
[Year] [smallint] NOT NULL,
[Month] [tinyint] NOT NULL,
[Day] [tinyint] NOT NULL,
[DayOfWeekID] [tinyint] NOT NULL,
[Hour] [tinyint] NOT NULL,
[Minute] [tinyint] NOT NULL,
[Second] [tinyint] NOT NULL,
[Bid] [real] NOT NULL,
[Ask] [real] NOT NULL,
[UniqueTick] [bit] NULL,
[IsWithinSession] AS
([dbo].[GetIsWithinSessionWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second])) PERSISTED,
[MinsFromSStart] AS
([dbo].[GetMinsFromSStartWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second])) PERSISTED,
[SecsFromSStart] AS
([dbo].[GetSecsFromSStartWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second])) PERSISTED,
[TickTime] AS
((((([Second]+(100)*[Minute])+(10000)*[Hour])+(1000000)*[Day])+(100000000)*[Month])+(10000000000.)*[Year]) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'' ,
@.level0type=N'SCHEMA',@.level0name=N'dbo',
@.level1type=N'TABLE',@.level1name=N'Tick',
@.level2type=N'COLUMN',@.level2name=N'SourceFileID'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Holds the
number of minutes from Forex session start (17:00:00 EST Sunday)' ,
@.level0type=N'SCHEMA',@.level0name=N'dbo',
@.level1type=N'TABLE',@.level1name=N'Tick',
@.level2type=N'COLUMN',@.level2name=N'MinsFromSStart'
---
-- end of script
As stated above, I am attempting to UPDATE the UniqueTick column here.
Thanks,
Michal Kreslik|||Do you have any indexes on the table? I expect that a table scan of 255
million rows to take a while, even if only 1 row is updated.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:55499C83-6ACB-4AE1-8319-A2D5F05473E6@.microsoft.com...
> This is how the table looks like when scripted:
> -- begin script
> ---
> USE [FXdataDB]
> GO
> /****** Object: Table [dbo].[Tick] Script Date: 02/08/2007 23:04:50
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[Tick](
> [TickID] [int] IDENTITY(1,1) NOT NULL,
> [SourceFileID] [smallint] NOT NULL,
> [FXPairID] [tinyint] NOT NULL,
> [Year] [smallint] NOT NULL,
> [Month] [tinyint] NOT NULL,
> [Day] [tinyint] NOT NULL,
> [DayOfWeekID] [tinyint] NOT NULL,
> [Hour] [tinyint] NOT NULL,
> [Minute] [tinyint] NOT NULL,
> [Second] [tinyint] NOT NULL,
> [Bid] [real] NOT NULL,
> [Ask] [real] NOT NULL,
> [UniqueTick] [bit] NULL,
> [IsWithinSession] AS
> ([dbo].[GetIsWithinSessionWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second]))
> PERSISTED,
> [MinsFromSStart] AS
> ([dbo].[GetMinsFromSStartWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second]))
> PERSISTED,
> [SecsFromSStart] AS
> ([dbo].[GetSecsFromSStartWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second]))
> PERSISTED,
> [TickTime] AS
> ((((([Second]+(100)*[Minute])+(10000)*[Hour])+(1000000)*[Day])+(100000000)*[Month])+(10000000000.)*[Year])
> PERSISTED
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> GO
> EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'' ,
> @.level0type=N'SCHEMA',@.level0name=N'dbo',
> @.level1type=N'TABLE',@.level1name=N'Tick',
> @.level2type=N'COLUMN',@.level2name=N'SourceFileID'
> GO
> EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Holds
> the
> number of minutes from Forex session start (17:00:00 EST Sunday)' ,
> @.level0type=N'SCHEMA',@.level0name=N'dbo',
> @.level1type=N'TABLE',@.level1name=N'Tick',
> @.level2type=N'COLUMN',@.level2name=N'MinsFromSStart'
> ---
> -- end of script
>
> As stated above, I am attempting to UPDATE the UniqueTick column here.
> Thanks,
> Michal Kreslik|||"Dan Guzman" wrote:
> Do you have any indexes on the table? I expect that a table scan of 255
> million rows to take a while, even if only 1 row is updated.
Yes, I have 6 indexes on the table. But before adding the extra two
autocomputed columns, UPDATE worked fine even with all those indexes in
place. It took about 5 ms to update.
So there's something fishy about that.
What this behaviour could be caused by?
Thanks,
Michal Kreslik|||> What this behaviour could be caused by?
Have you looked at the execution plan to ensure indexes are used as
expected? These symptoms can be due to blocking - have you checked activity
during the update?
I can try to repro if you provide the script for the user-defined function
and all indexes too. I didn't see anything obvious in the scripts you
posed. I assume your actual UPDATE statement is:
UPDATE Tick
SET UniqueTick = 1
WHERE TickID = 123456789
--
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:15AECAEA-A24D-496D-8101-80B21DAF63EC@.microsoft.com...
> "Dan Guzman" wrote:
>> Do you have any indexes on the table? I expect that a table scan of 255
>> million rows to take a while, even if only 1 row is updated.
> Yes, I have 6 indexes on the table. But before adding the extra two
> autocomputed columns, UPDATE worked fine even with all those indexes in
> place. It took about 5 ms to update.
> So there's something fishy about that.
> What this behaviour could be caused by?
> Thanks,
> Michal Kreslik|||"Dan Guzman" wrote:
> > What this behaviour could be caused by?
> Have you looked at the execution plan to ensure indexes are used as
> expected? These symptoms can be due to blocking - have you checked activity
> during the update?
> I can try to repro if you provide the script for the user-defined function
> and all indexes too. I didn't see anything obvious in the scripts you
> posed. I assume your actual UPDATE statement is:
> UPDATE Tick
> SET UniqueTick = 1
> WHERE TickID = 123456789
>
Thanks very much for your swift reply.
Yes, obviously I execute the statement with the correct syntax, sorry.
I have just checked the execution plan and everything seems to be ok.
>- have you checked activity during the update?
You mean, whether SQL server is using CPU time? No, there's almost no CPU
activity during this strange UPDATE.
I have uploaded the CREATE script for the entire database here:
http://kreslik.com/files/FXdataDB_Scripted_All.zip
Also, ever since I added those two autocomputed columns, the SQL server
seems to be "caching" something to disk all the time (using up to 50% of
CPU). It always starts a couple of minutes after the SQL server starts and
the only way to stop it (although I guess there's no reason why SQL server
should be "recomputing" persisted columns) was to restart the SQL server.
Today it seems it is no longer "caching", but I'm not sure why. Could this be
related to my problem? How do I find out what exactly what is SQL server
doing and eventually how do I put and end to such a nonsensical "caching"?
Thanks much for any help.
Michal Kreslik|||> You mean, whether SQL server is using CPU time? No, there's almost no CPU
> activity during this strange UPDATE.
What about blocking and disk activity?
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:A49FAE08-3EDD-42D1-9EA9-6E0A3FD139DE@.microsoft.com...
> "Dan Guzman" wrote:
>> > What this behaviour could be caused by?
>> Have you looked at the execution plan to ensure indexes are used as
>> expected? These symptoms can be due to blocking - have you checked
>> activity
>> during the update?
>> I can try to repro if you provide the script for the user-defined
>> function
>> and all indexes too. I didn't see anything obvious in the scripts you
>> posed. I assume your actual UPDATE statement is:
>> UPDATE Tick
>> SET UniqueTick = 1
>> WHERE TickID = 123456789
> Thanks very much for your swift reply.
> Yes, obviously I execute the statement with the correct syntax, sorry.
> I have just checked the execution plan and everything seems to be ok.
>>- have you checked activity during the update?
> You mean, whether SQL server is using CPU time? No, there's almost no CPU
> activity during this strange UPDATE.
> I have uploaded the CREATE script for the entire database here:
> http://kreslik.com/files/FXdataDB_Scripted_All.zip
> Also, ever since I added those two autocomputed columns, the SQL server
> seems to be "caching" something to disk all the time (using up to 50% of
> CPU). It always starts a couple of minutes after the SQL server starts and
> the only way to stop it (although I guess there's no reason why SQL server
> should be "recomputing" persisted columns) was to restart the SQL server.
> Today it seems it is no longer "caching", but I'm not sure why. Could this
> be
> related to my problem? How do I find out what exactly what is SQL server
> doing and eventually how do I put and end to such a nonsensical "caching"?
> Thanks much for any help.
> Michal Kreslik|||"Dan Guzman" wrote:
> > You mean, whether SQL server is using CPU time? No, there's almost no CPU
> > activity during this strange UPDATE.
> What about blocking and disk activity?
No, there doesn't seem to be any disk activity.
Michal Kreslik|||Hi, Michal. I haven't been able to repro your problem on this end. I
created the database (without the last 2 computed columns) and loaded the
Tick table with 10M rows of test data. I then added the computed columns
and index and the update statement ran quickly.
Can you post the output of the following query against the hung spid?
Perhaps this and/or the other dm views will provide a clue as to what's
going on.
SELECT *
FROM sys.dm_exec_requests
WHERE session_id = ?
FOR XML PATH ('dm_exec_requests')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:8564911C-2F36-4938-9B4A-3829152D03C6@.microsoft.com...
> "Dan Guzman" wrote:
>> > You mean, whether SQL server is using CPU time? No, there's almost no
>> > CPU
>> > activity during this strange UPDATE.
>> What about blocking and disk activity?
> No, there doesn't seem to be any disk activity.
> Michal Kreslik|||Hello, Dan,
thanks for this idea. Quite suprisingly, the UPDATE command started working
all by itself out of the blue.
But anyway, this strange behavior should not be left unexplained as it might
happen again.
I ran the SQL command you suggested against the (now running ok) UPDATE
spid. If you can see something that might have been causing the UPDATE
getting stuck, please let me know.
XML result:
<dm_exec_requests>
<session_id>58</session_id>
<request_id>0</request_id>
<start_time>2007-02-13T17:42:35.430</start_time>
<status>suspended</status>
<command>UPDATE</command>
<sql_handle>AgAAANT0Yi4+B5OkY6CYr8CIw0pT0XYp</sql_handle>
<statement_start_offset>30</statement_start_offset>
<statement_end_offset>-1</statement_end_offset>
<plan_handle>BgAHANT0Yi64oVkEAAAAAAAAAAAAAAAA</plan_handle>
<database_id>7</database_id>
<user_id>1</user_id>
<connection_id>0E8F7597-F1AC-44D5-AE5A-48F3081915E7</connection_id>
<blocking_session_id>0</blocking_session_id>
<wait_type>WRITELOG</wait_type>
<wait_time>0</wait_time>
<last_wait_type>WRITELOG</last_wait_type>
<wait_resource></wait_resource>
<open_transaction_count>1</open_transaction_count>
<open_resultset_count>1</open_resultset_count>
<transaction_id>1633798</transaction_id>
<context_info></context_info>
<percent_complete>0.0000000e+000</percent_complete>
<estimated_completion_time>0</estimated_completion_time>
<cpu_time>0</cpu_time>
<total_elapsed_time>2</total_elapsed_time>
<scheduler_id>0</scheduler_id>
<task_address>AGiKeA==</task_address>
<reads>135</reads>
<writes>325</writes>
<logical_reads>413692</logical_reads>
<text_size>2147483647</text_size>
<language>us_english</language>
<date_format>mdy</date_format>
<date_first>7</date_first>
<quoted_identifier>1</quoted_identifier>
<arithabort>0</arithabort>
<ansi_null_dflt_on>1</ansi_null_dflt_on>
<ansi_defaults>0</ansi_defaults>
<ansi_warnings>1</ansi_warnings>
<ansi_padding>1</ansi_padding>
<ansi_nulls>1</ansi_nulls>
<concat_null_yields_null>1</concat_null_yields_null>
<transaction_isolation_level>2</transaction_isolation_level>
<lock_timeout>-1</lock_timeout>
<deadlock_priority>0</deadlock_priority>
<row_count>1</row_count>
<prev_error>0</prev_error>
<nest_level>0</nest_level>
<granted_query_memory>0</granted_query_memory>
<executing_managed_code>0</executing_managed_code>
</dm_exec_requests>
Thank you very much, Dan,
Michal Kreslik|||> thanks for this idea. Quite suprisingly, the UPDATE command started
> working
> all by itself out of the blue.
> But anyway, this strange behavior should not be left unexplained as it
> might
> happen again.
I agree that it's best to identify the underlying cause. Unfortunately,
this is very difficult to do forensically with a problem that is not
repeatable. I assume you checked the SQL Server error log for unusual
messages?
> I ran the SQL command you suggested against the (now running ok) UPDATE
> spid. If you can see something that might have been causing the UPDATE
> getting stuck, please let me know.
As you can see, this healthy request is waiting on WRITELOG, which is normal
and expected. Should the problem happen again, the wait_type should provide
more clues as to what the query is waiting on and you can drill-down
accordingly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:BD88E108-A5AE-4710-9F61-4E1DB23B465A@.microsoft.com...
> Hello, Dan,
> thanks for this idea. Quite suprisingly, the UPDATE command started
> working
> all by itself out of the blue.
> But anyway, this strange behavior should not be left unexplained as it
> might
> happen again.
> I ran the SQL command you suggested against the (now running ok) UPDATE
> spid. If you can see something that might have been causing the UPDATE
> getting stuck, please let me know.
> XML result:
> <dm_exec_requests>
> <session_id>58</session_id>
> <request_id>0</request_id>
> <start_time>2007-02-13T17:42:35.430</start_time>
> <status>suspended</status>
> <command>UPDATE</command>
> <sql_handle>AgAAANT0Yi4+B5OkY6CYr8CIw0pT0XYp</sql_handle>
> <statement_start_offset>30</statement_start_offset>
> <statement_end_offset>-1</statement_end_offset>
> <plan_handle>BgAHANT0Yi64oVkEAAAAAAAAAAAAAAAA</plan_handle>
> <database_id>7</database_id>
> <user_id>1</user_id>
> <connection_id>0E8F7597-F1AC-44D5-AE5A-48F3081915E7</connection_id>
> <blocking_session_id>0</blocking_session_id>
> <wait_type>WRITELOG</wait_type>
> <wait_time>0</wait_time>
> <last_wait_type>WRITELOG</last_wait_type>
> <wait_resource></wait_resource>
> <open_transaction_count>1</open_transaction_count>
> <open_resultset_count>1</open_resultset_count>
> <transaction_id>1633798</transaction_id>
> <context_info></context_info>
> <percent_complete>0.0000000e+000</percent_complete>
> <estimated_completion_time>0</estimated_completion_time>
> <cpu_time>0</cpu_time>
> <total_elapsed_time>2</total_elapsed_time>
> <scheduler_id>0</scheduler_id>
> <task_address>AGiKeA==</task_address>
> <reads>135</reads>
> <writes>325</writes>
> <logical_reads>413692</logical_reads>
> <text_size>2147483647</text_size>
> <language>us_english</language>
> <date_format>mdy</date_format>
> <date_first>7</date_first>
> <quoted_identifier>1</quoted_identifier>
> <arithabort>0</arithabort>
> <ansi_null_dflt_on>1</ansi_null_dflt_on>
> <ansi_defaults>0</ansi_defaults>
> <ansi_warnings>1</ansi_warnings>
> <ansi_padding>1</ansi_padding>
> <ansi_nulls>1</ansi_nulls>
> <concat_null_yields_null>1</concat_null_yields_null>
> <transaction_isolation_level>2</transaction_isolation_level>
> <lock_timeout>-1</lock_timeout>
> <deadlock_priority>0</deadlock_priority>
> <row_count>1</row_count>
> <prev_error>0</prev_error>
> <nest_level>0</nest_level>
> <granted_query_memory>0</granted_query_memory>
> <executing_managed_code>0</executing_managed_code>
> </dm_exec_requests>
> Thank you very much, Dan,
> Michal Kreslik|||Dan, so I have finally resolved the issue today.
Also, I have found out why this puzzling behavior was seemingly random.
Today the UPDATE part of my program stopped working again. Thanks to you and
your good idea about looking into the sys.dm_exec_requests for the particular
session_id, I have found out that the UPDATE is being blocked by a SELECT
statement.
This SELECT statment is used to select and sort rows from the large OLAP
table and prepares the data for further work.
When the result is ready, my algorithm goes thru the rows one after another,
performs some calculations and based on the calculations, it UPDATEs the
rows.
But the problem is that I am using the SqlDataReader object in C#.NET which
is free to return first row to you BEFORE it finished executing the entire
command.
So basically, SqlDataReader might return the first 10000 rows out of 2
million SELECTed to speed up further processing.
But this is also the catch as it seems that you can't tell SqlDataReader to
not behave like this. That means that if your processing code contains
something (UPDATE) that can't be performed before the SqlDataReader command
(SELECT) is finished, you are going to get a lock (which is what I got) as
you can't UPDATE the row in the table where the SELECT on that row is just
being processed.
The solution that I'm now going to implement is to simply use the
SqlDataAdapter object in C#.NET to fill the DataTable object and this
DataTable object will be used as a source for calculations that are doing
UPDATE to the SELECTed data. As the DataTable object is just a memory
representation of the SQL data, it is not going to interfere with UPDATE in
any way.
The reason why this behavior was random is that SqlDataReader sometimes
finishes the entire SELECT before returning the first row and sometimes it
doesn't. I would infer that the bigger is the data chunk that you are
retrieving with SqlDataReader, the higher is the probability that
SqlDataReader won't finish the entire SELECT statement before returning the
firt row for processing. But it seems you can't control this aspect in any
way.
My algorithm is being fed with the data chunk that equals the weekly update
for the OLAP database (new data for that week). This data chunk varies
somewhere between 700k rows and 1.5 mil rows (forex price tick data for 16 FX
pairs for that week).
When the amount of data is small enough for the SqlDataReader to complete
the SELECT before it returns the first row, everything is ok. But when the
amount of data is such that SqlDataReader returns the first row BEFORE it
finishes the SELECT, you get the lock.
Thanks for helping me out. Anyther mystery solved.
Michal Kreslik
michal.kreslik(at)kreslik.com|||Dan, so I have finally resolved the issue today.
Also, I have found out why this puzzling behavior was seemingly random.
Today the UPDATE part of my program stopped working again. Thanks to you and
your good idea about looking into the sys.dm_exec_requests for the particular
session_id, I have found out that the UPDATE is being blocked by a SELECT
statement.
This SELECT statment is used to select and sort rows from the large OLAP
table and prepares the data for further work.
When the result is ready, my algorithm goes thru the rows one after another,
performs some calculations and based on the calculations, it UPDATEs the
rows.
But the problem is that I am using the SqlDataReader object in C#.NET which
is free to return first row to you BEFORE it finished executing the entire
command.
So basically, SqlDataReader might return the first 10000 rows out of 2
million SELECTed to speed up further processing.
But this is also the catch as it seems that you can't tell SqlDataReader to
not behave like this. That means that if your processing code contains
something (UPDATE) that can't be performed before the SqlDataReader command
(SELECT) is finished, you are going to get a lock (which is what I got) as
you can't UPDATE the row in the table where the SELECT on that row is just
being processed.
The solution that I'm now going to implement is to simply use the
SqlDataAdapter object in C#.NET to fill the DataTable object and this
DataTable object will be used as a source for calculations that are doing
UPDATE to the SELECTed data. As the DataTable object is just a memory
representation of the SQL data, it is not going to interfere with UPDATE in
any way.
The reason why this behavior was random is that SqlDataReader sometimes
finishes the entire SELECT before returning the first row and sometimes it
doesn't. I would infer that the bigger is the data chunk that you are
retrieving with SqlDataReader, the higher is the probability that
SqlDataReader won't finish the entire SELECT statement before returning the
firt row for processing. But it seems you can't control this aspect in any
way.
My algorithm is being fed with the data chunk that equals the weekly update
for the OLAP database (new data for that week). This data chunk varies
somewhere between 700k rows and 1.5 mil rows (forex price tick data for 16 FX
pairs for that week).
When the amount of data is small enough for the SqlDataReader to complete
the SELECT before it returns the first row, everything is ok. But when the
amount of data is such that SqlDataReader returns the first row BEFORE it
finishes the SELECT, you get the lock.
Thanks for helping me out. Anyther mystery solved.
Michal Kreslik
michal.kreslik(at)kreslik.com|||Dan, so I have finally resolved the issue today.
Also, I have found out why this puzzling behavior was seemingly random.
Today the UPDATE part of my program stopped working again. Thanks to you and
your good idea about looking into the sys.dm_exec_requests for the particular
session_id, I have found out that the UPDATE is being blocked by a SELECT
statement.
This SELECT statment is used to select and sort rows from the large OLAP
table and prepares the data for further work.
When the result is ready, my algorithm goes thru the rows one after another,
performs some calculations and based on the calculations, it UPDATEs the
rows.
But the problem is that I am using the SqlDataReader object in C#.NET which
is free to return first row to you BEFORE it finished executing the entire
command.
So basically, SqlDataReader might return the first 10000 rows out of 2
million SELECTed to speed up further processing.
But this is also the catch as it seems that you can't tell SqlDataReader to
not behave like this. That means that if your processing code contains
something (UPDATE) that can't be performed before the SqlDataReader command
(SELECT) is finished, you are going to get a lock (which is what I got) as
you can't UPDATE the row in the table where the SELECT on that row is just
being processed.
The solution that I'm now going to implement is to simply use the
SqlDataAdapter object in C#.NET to fill the DataTable object and this
DataTable object will be used as a source for calculations that are doing
UPDATE to the SELECTed data. As the DataTable object is just a memory
representation of the SQL data, it is not going to interfere with UPDATE in
any way.
The reason why this behavior was random is that SqlDataReader sometimes
finishes the entire SELECT before returning the first row and sometimes it
doesn't. I would infer that the bigger is the data chunk that you are
retrieving with SqlDataReader, the higher is the probability that
SqlDataReader won't finish the entire SELECT statement before returning the
firt row for processing. But it seems you can't control this aspect in any
way.
My algorithm is being fed with the data chunk that equals the weekly update
for the OLAP database (new data for that week). This data chunk varies
somewhere between 700k rows and 1.5 mil rows (forex price tick data for 16 FX
pairs for that week).
When the amount of data is small enough for the SqlDataReader to complete
the SELECT before it returns the first row, everything is ok. But when the
amount of data is such that SqlDataReader returns the first row BEFORE it
finishes the SELECT, you get the lock.
Thanks for helping me out. Anyther mystery solved.
Michal Kreslik
michal.kreslik(at)kreslik.com|||So I've implemented the solution as described above and everything works
correctly.
The interesting twist to this is that the overall processing time with
DataTable is now half of what it was with SqlDataReader.
Michal Kreslik
michal.kreslik(at)kreslik.com

Tuesday, March 27, 2012

Case Statement help

Sunday, March 25, 2012

Case Sensitive Columns

Case Sensitive Columns

Case Sensitive Columns

Thursday, March 22, 2012

Case order by

Tuesday, March 20, 2012

case expression plus outer join in ole db source

case expression in where clause and null's

Monday, March 19, 2012

CASE and Inserting Commas

Case - Sensitive field

Cascading updates question

Friday, February 24, 2012

capture showplan in profiler

Friday, February 10, 2012

can't update column if there are autocomputed columns?

can't update column if there are autocomputed columns?

uninstall SQL

Blog Archive

About Me