Last Function

SQL Server has no Last() and Max() pulls the Max not the last. Is there a way to pull the Last payment amount with the date of that payment, in SQL Server??

tblClients - ClientID (PK)
tblPayments - PaymentID (PK)

Thanks for any help,You can add a timestamp column to the table and then use Max(TimestampCol) to return the last inserted record (which i assume you want to return), e.g.

SELECT
 *
FROM
 myTable
WHERE
 myTable.TimestampCol = (SELECT Max(TimestampCol) FROM myTable)

|||You're going to have to date stamp your transactions.

Largest character data type?

I thought "text" was the largest, as the documentation says the max length
is (2^31)-1
But I can only fit 8000 characters in before it truncates, but even then it
is not exactly 8000 characters, but more like 1000.TEXT is the largest data type. Where do you see the truncation happening'
If it is in Query Analyzer, you need to expand the number of display
character (max is 8192). Your application will never truncate the content of
the TEXT data type.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Wayne M J" <not@.home.nor.bigpuddle.com> wrote in message
news:OSh09QhvDHA.2076@.TK2MSFTNGP09.phx.gbl...
> I thought "text" was the largest, as the documentation says the max length
> is (2^31)-1
> But I can only fit 8000 characters in before it truncates, but even then
it
> is not exactly 8000 characters, but more like 1000.
>|||"SriSamp" <ssampath@.sct.co.in> wrote in message
news:uTxYyohvDHA.2072@.TK2MSFTNGP10.phx.gbl...
> TEXT is the largest data type. Where do you see the truncation happening'
> If it is in Query Analyzer, you need to expand the number of display
> character (max is 8192). Your application will never truncate the content
of
> the TEXT data type.
I see this from the table view, when I 'return all rows' I can scroll
through the field, but it only seems to have stored to a certain point.
The result is visible here:
http://www.wmjackson.cable.nu/website/goto.aspx?id=0|||That must be a limitation with Enterprise Manager. The data will not be
truncated.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Wayne M J" <not@.home.nor.bigpuddle.com> wrote in message
news:eOaaYPjvDHA.1512@.TK2MSFTNGP10.phx.gbl...
> "SriSamp" <ssampath@.sct.co.in> wrote in message
> news:uTxYyohvDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > TEXT is the largest data type. Where do you see the truncation
happening'
> > If it is in Query Analyzer, you need to expand the number of display
> > character (max is 8192). Your application will never truncate the
content
> of
> > the TEXT data type.
> I see this from the table view, when I 'return all rows' I can scroll
> through the field, but it only seems to have stored to a certain point.
> The result is visible here:
> http://www.wmjackson.cable.nu/website/goto.aspx?id=0
>|||"SriSamp" <ssampath@.sct.co.in> wrote in message
news:OEnSpbjvDHA.3256@.TK2MSFTNGP11.phx.gbl...
> That must be a limitation with Enterprise Manager. The data will not be
> truncated.
Actually, that makes sense.

large value data type (nvarchar(max))

hi,

I have inserted a new column in my db table (type: nvarchar(max))
now I can't process the cube anymore ... is there a possibility to store large text files in the cube?

thanks,

rhapsy

Hi Rhapsy

What are you trying to achieve with this data? Would it not be easier to try and have an action to get that text if it is needed?

Regards

Mike

|||hey mike,

probably it would be easier, but I'd like to get all my information from the same source ...
and I'm a little bit curious if thias is possible ...

thanks,
rhapsy

Large text column

I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.

Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?

After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.

Is this a Microsoft "feature" that is scheduled to be fixed in 2008?

|||

Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.

We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.

Large text column

I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.

Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?

After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.

Is this a Microsoft "feature" that is scheduled to be fixed in 2008?

|||

Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.

We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.

Large text column

I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.

Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?

After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.

Is this a Microsoft "feature" that is scheduled to be fixed in 2008?

|||

Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.

We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.

Large table hogging cache

Using SQL Server 2000, SP1 with 4Gb max memory allocated to the instance. The problem is that one large table is hogging cache and it's dragging down overall query performance. I realise it's in cache because it's getting queried regulary. However, I need to know what options exist to get around this problem - to free up some cache for other tables and indexes? Of course, there is the option of archiving off some the data in the table to reduce its size and we will look at doing this although it will not be as easy as it sounds.

I can imagine that there must be many databases that have at least one large table that is getting hit regularly and is left in cache more-or-less permanently. Therefore, I can't believe I have an usual problem.

Thanks in advance,
ZartyHi Zarty

Are you sure it is this the caching of this table causing performance problems and nothing else? Have you read up on the lazy writer and understand how it decides what to keep in cache and what to release (in particular how it calculates cost)?

My point is - if this table is queried regularly and SQL Server determines that it is best left in the cache - why would you want to force SQL Server to retrieve the data from the disk each and every time the table is queried? Wouldn't you expect this to cause the performance to dramatically decrease?

Anyway - I don't know how to clear a single table from the cache or if it is possible. Best I have is a decent article about the cache:
http://www.sql-server-performance.com/rd_data_cache.asp|||I'm not sure about a lot of things! I just find it very difficult to get definite info on what SQL Server is doing/reporting in some areas. For example, in Perfmon, the Page Life Expectancy counter... I would assume that seeing this counter dip to zero regularly and struggle to get above 200 seconds would be a bad thing? If so, I have a problem. However, at the same time, I see Buffer Cache Hit Ratio look healthy at 99% +. I can't reconcile these two pieces of information.

Another example... In the article you suggested (http://www.sql-server-performance.com/rd_data_cache.asp), it mentions syscacheobjects and BOL confirms that 'user tables' are a possible object type. Therefore, I am confused when I query syscachetables and do not see hardly any references to user tables at all on the system in question (on other sql instances I do) and certainly no reference to the large and regularly quiried tables in question. However, if I analyse the output from DBCC MEMUSAGE, I can definitely see regularly queried user tables reported as being in cache as one would expect. Can you see why I find it so difficult to get a full picture of what is going on?

What I can say is that I've focused mainly on the info reported by DBCC MEMUSAGE and, from that, we've identified some large user tables that are hogging cache and we have evidence through Page Life Expectancy and other sources that many other important tables don't get much cache time. I get the impression that SQL Server is busy thrashing pages in and out of cache and it would be better if it could leave a bigger proportion of them there. I've also seen that better indexing on a table can improve the problem a lot. SQL Server seems to cache a lot less of a user table if the indexing is sorted out. So, in some ways, things are improving here. However, as soon as we improve the situation for one table, the cache space made available seems to be quickly taken up and hogged by another large table. We're chipping away at the indexing but it's not always possible/practical to create much of a cache space saving through improved indexing.

What I've tried to explain here is that if it were just a problem of one table staying in cache, you're right, it wouldn't be a problem... However, that is not the problem. The problem is that a few tables appear to fill most of the available cache and there is a lot of other important table data that doesn't seem to get much cache time because the cache appears relatively small and doesn't grow despite the fact that a bigger memory pool has been assigned to SQL Server via the Max Memory setting. I'm going for a service restart this weekend by the way.

Thanks,
Zarty

Large FullText tables - slow queries

Hi,

I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.

To query the complete index is fast, for example:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

JOIN Pages ITP ON ITP.PageID = CT.[Key]

JOIN Feeds ITF ON ITP.IPID = ITF.IPID

JOIN Buyers ITB ON ITB.IBID = ITF.IBID

WHERE ITB.ID IN (1342,246)

These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).

One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:

'{ID:1342}' + [Body]

That sounds like a very good idea. I could then change the 2nd query above to be:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT

That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.

If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!

Many thanks for any advice.

Simon

I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||

Hi I am not sure if this can help:

35 Mio. I am looking forward to encounter more soon.

try:

Make sure to use the noiseword concept of FT, get rid of the noice first.

Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.

Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)

Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only

Try to Query also against other fields (not fulltext indexed)

Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!

.

*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!

Of course database needs field AutoId as Clusterd index.

8GB Memory / 64-bit version of SQL

Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)

How many keywords per record do you have, how long are they in average?

How big is the Textfile?

What is the CPU?

Thomas

Large FullText tables - slow queries

Hi,

I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.

To query the complete index is fast, for example:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

JOIN Pages ITP ON ITP.PageID = CT.[Key]

JOIN Feeds ITF ON ITP.IPID = ITF.IPID

JOIN Buyers ITB ON ITB.IBID = ITF.IBID

WHERE ITB.ID IN (1342,246)

These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).

One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:

'{ID:1342}' + [Body]

That sounds like a very good idea. I could then change the 2nd query above to be:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT

That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.

If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!

Many thanks for any advice.

Simon

I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||

Hi I am not sure if this can help:

35 Mio. I am looking forward to encounter more soon.

try:

Make sure to use the noiseword concept of FT, get rid of the noice first.

Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.

Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)

Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only

Try to Query also against other fields (not fulltext indexed)

Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!

.

*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!

Of course database needs field AutoId as Clusterd index.

8GB Memory / 64-bit version of SQL

Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)

How many keywords per record do you have, how long are they in average?

How big is the Textfile?

What is the CPU?

Thomas