Problem with checkpoints in SS2005 - Help is appreciated

Hello All,
I am having problems with the contention caused by checkpoints as I
migrated from SQL Server 2000 to SQL Server 2005. The same code, the
same database, tha same machine, the same load, but much worse
performance.
Do you know of any changes in the way checkpoints are performed in SQL
Server 2005?
In particular, any change in the locking behavior?
Your help is appreciated.
Kind regards
CDCD
How do you know that is CHECKPOINT ? Have you run Profiler? Did you update
statistics after upgrading?
"CD" <crbd98@.yahoo.com> wrote in message
news:1179976388.257189.15410@.b40g2000prd.googlegroups.com...
> Hello All,
> I am having problems with the contention caused by checkpoints as I
> migrated from SQL Server 2000 to SQL Server 2005. The same code, the
> same database, tha same machine, the same load, but much worse
> performance.
> Do you know of any changes in the way checkpoints are performed in SQL
> Server 2005?
> In particular, any change in the locking behavior?
> Your help is appreciated.
> Kind regards
> CD
>|||Did you guys adjust the recovery interval?
Run a recompile on all stored procedures
But indeed monitor with profiler!

Problem with checkpoints in SS2005 - Help is appreciated

Hello All,
I am having problems with the contention caused by checkpoints as I
migrated from SQL Server 2000 to SQL Server 2005. The same code, the
same database, tha same machine, the same load, but much worse
performance.
Do you know of any changes in the way checkpoints are performed in SQL
Server 2005?
In particular, any change in the locking behavior?
Your help is appreciated.
Kind regards
CDCD
How do you know that is CHECKPOINT ? Have you run Profiler? Did you update
statistics after upgrading?
"CD" <crbd98@.yahoo.com> wrote in message
news:1179976388.257189.15410@.b40g2000prd.googlegroups.com...
> Hello All,
> I am having problems with the contention caused by checkpoints as I
> migrated from SQL Server 2000 to SQL Server 2005. The same code, the
> same database, tha same machine, the same load, but much worse
> performance.
> Do you know of any changes in the way checkpoints are performed in SQL
> Server 2005?
> In particular, any change in the locking behavior?
> Your help is appreciated.
> Kind regards
> CD
>|||Did you guys adjust the recovery interval?
Run a recompile on all stored procedures
But indeed monitor with profiler!

Problem with Check Constraints

I am working with an evaluation copy of SQL Server 2000 for the first
time; my DB experience lies with MS Access.

I have a simple table in SQL Server (tblCompany) that has a field
called "Ticker." When new company stock tickers (i.e., MSFT for
Microsoft) are entered into the field, I'd like them in all
caps--whether the user types msft, Msft, MsFt, etc. In Access, this
was easy--simply set the Format to ">" in table design view.

In SQL Server Design Table view, I've clicked on "Manage Constraints"
and put the following code in that I found elsewhere:

([Ticker] = upper([Ticker]))

I then checked all three boxes below: "Check existing data on
creation," "Enforce constraint for replication," and "Enforce
constraint for INSERTs and UPDATEs." The first one, "Check existing
data..." is checked as I've already entered in some data in the field
in lowercase to see if the check constraint would go back and change
it to Upper Case--this because I'm wanting to ultimately migrate a
table from Access to SQL Server and ensure that all Tickers are in
Upper Case.

I'm able to do this and then save the table design with changes; but
every time, I then go and look at the table data to see if the check
constraint was applied, and each time it is not; then, I go back to
"Manage Constraints" and find that the "Check existing data..." box is
unchecked. I've gone through this SEVERAL times.

Hoping this is something simple. Apologize for my "newbieness." I've
got a "For Dummies" book in front of me as well as numerous Internet
windows open, trying to figure this out. Have checked books online on
the MSFT site as well to no avail.

Thanks in advance--

RADA constraint enforces data integrity rules but does not change existing or
newly inserted data. You need to cleanup your data and then add the
constraint to only permit uppercase values going forward.

If you want to automatically change values to upper case as they are entered
on the server side, you'll need to do this in a trigger. IMHO, this task is
better done in application code and let the database just enforce the data
integrity rule.

I don't know the details of the constraint you are adding but be aware that
case sensitivity is determined by collations in SQL 2000. The default
collation is case insensitive so you'll need to override the default
case-insensitive compare in your constraint. The example script below will
correct existing data and add a check constraint to ensure only upper case
values are allowed.

UPDATE Company
SET Ticker = UPPER('Ticker')
GO

ALTER TABLE Company
ADD CONSTRAINT CK_Ticker
CHECK (Ticker COLLATE SQL_Latin1_General_Cp1_CS_AS = UPPER(Ticker))
GO

On a side note, be aware that Hungarian notation (e.g. 'tbl' prefixes) are
frowned upon in client-server database design. The underlying database
implementation (table or view) should be transparent to database users.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"RAD" <rdavenport@.nyc.rr.com> wrote in message
news:d4119d8.0401251017.28f0b46f@.posting.google.co m...
> I am working with an evaluation copy of SQL Server 2000 for the first
> time; my DB experience lies with MS Access.
> I have a simple table in SQL Server (tblCompany) that has a field
> called "Ticker." When new company stock tickers (i.e., MSFT for
> Microsoft) are entered into the field, I'd like them in all
> caps--whether the user types msft, Msft, MsFt, etc. In Access, this
> was easy--simply set the Format to ">" in table design view.
> In SQL Server Design Table view, I've clicked on "Manage Constraints"
> and put the following code in that I found elsewhere:
> ([Ticker] = upper([Ticker]))
> I then checked all three boxes below: "Check existing data on
> creation," "Enforce constraint for replication," and "Enforce
> constraint for INSERTs and UPDATEs." The first one, "Check existing
> data..." is checked as I've already entered in some data in the field
> in lowercase to see if the check constraint would go back and change
> it to Upper Case--this because I'm wanting to ultimately migrate a
> table from Access to SQL Server and ensure that all Tickers are in
> Upper Case.
> I'm able to do this and then save the table design with changes; but
> every time, I then go and look at the table data to see if the check
> constraint was applied, and each time it is not; then, I go back to
> "Manage Constraints" and find that the "Check existing data..." box is
> unchecked. I've gone through this SEVERAL times.
> Hoping this is something simple. Apologize for my "newbieness." I've
> got a "For Dummies" book in front of me as well as numerous Internet
> windows open, trying to figure this out. Have checked books online on
> the MSFT site as well to no avail.
> Thanks in advance--
> RAD|||On 25 Jan 2004 10:17:18 -0800, rdavenport@.nyc.rr.com (RAD) wrote:

>I am working with an evaluation copy of SQL Server 2000 for the first
>time; my DB experience lies with MS Access.
>I have a simple table in SQL Server (tblCompany) that has a field
>called "Ticker." When new company stock tickers (i.e., MSFT for
>Microsoft) are entered into the field, I'd like them in all
>caps--whether the user types msft, Msft, MsFt, etc. In Access, this
>was easy--simply set the Format to ">" in table design view.
>In SQL Server Design Table view, I've clicked on "Manage Constraints"
>and put the following code in that I found elsewhere:
>([Ticker] = upper([Ticker]))
>I then checked all three boxes below: "Check existing data on
>creation," "Enforce constraint for replication," and "Enforce
>constraint for INSERTs and UPDATEs." The first one, "Check existing
>data..." is checked as I've already entered in some data in the field
>in lowercase to see if the check constraint would go back and change
>it to Upper Case--this because I'm wanting to ultimately migrate a
>table from Access to SQL Server and ensure that all Tickers are in
>Upper Case.
>I'm able to do this and then save the table design with changes; but
>every time, I then go and look at the table data to see if the check
>constraint was applied, and each time it is not; then, I go back to
>"Manage Constraints" and find that the "Check existing data..." box is
>unchecked. I've gone through this SEVERAL times.
>Hoping this is something simple. Apologize for my "newbieness." I've
>got a "For Dummies" book in front of me as well as numerous Internet
>windows open, trying to figure this out. Have checked books online on
>the MSFT site as well to no avail.
>Thanks in advance--
>RAD
That doesn't work, as can be shown with

select ticker from tblCompany where ticker = 'MSFT'

Your row will be returned regardless of the case of the data.

this may be something that can be set at the database level,
alternatively use a trigger to uppercase the data.

Something like;

create trigger instblCompany
on tblCompany
instead of insert
as
insert into tblCompany
select upper(ticker), all other columns
from inserted|||Thanks both Dan and Lyndon--I'll give it a whirl.

RAD

Problem with Check Constraint Validation in MS SQL Server Management Studio Express

Hello,

it seems like the check constraint validation of MS SQL Server Management Studio express is horribly, horribly broken. Either that or I'm using it wrong. I hope it's the latter.

I'm trying to add a check constraint to an empty table. The problem is that just about no form of expression syntax seems to get accepted by MSSQLSMS. On my a table "Projects" I've created a ntext column called "status". The following check constraint expressions have all yielded "Error validating constraint" from MSSQLSMS:
status = "Test"status in ( "One", "Two", "Three" )
I've tried converting the column to regular "text", but that didn't work.
I've tried disabling "check existing data", but that didn't work.
Just about the only thing that got accepted by MSSQLSMS was:
status like 'Test'
... but then MSSQLSMS failed to save the table. What am I doing wrong?sigh, i can't believe this is such an unsolvable problem.

problem with chart and grouping

hy,

i have a problem with placing Percentages on a stacked bar chart. It displays the mount of days worked per employee.i group by year for catagory group and on a productive field for series grouping (vacation is a ligit booking but not productive)

to calculate the percent i have to do:
value grouped by productive and year/grouped by year

when i change the productive group to also group on year it displayed the bar in productive and not productive but the productive part had color and a line across the bar to point it out.

so i tried making 2 series groups, with the year groups output property set to no,and got the same result.

any help would be great.
Jensif my question isn't clear please say sosql

Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
Bill
You will have to use TEXTPTR

>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/