SET TEXTSIZE Not Working in SQL Server? Check This.

Posted on by Ian

Have you used SET TEXTSIZE to limit the data returned by a query, but found that it just doesn’t seem to work? You’re finding that your SELECT queries continue to return the full amount of text, regardless of the TEXTSIZE value?

Read on. You could be making this one obvious mistake.

When I first encountered the TEXTSIZE option, I decided to run a quick test. But it didn’t work. I tried several times with various TEXTSIZE values, but nothing seemed to work.

“Why isn’t it working?” I wondered.

But then it clicked. I re-read the documentation and realised that I wasn’t using the correct string size for my data types.

Continue reading →

Use SET TEXTSIZE to Limit the Data Returned for Each Row in SQL Server

Posted on by Ian

In SQL Server, the SET TEXTSIZE statement specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement.

Microsoft advises that text, ntext, and image will be removed in a future version of SQL Server, so you should avoid using these data types in new development work, and plan to modify applications that currently use them to use varchar(max), nvarchar(max), or varbinary(max) instead.

Continue reading →

What is @@TEXTSIZE in SQL Server?

Posted on by Ian

In SQL Server, the @@TEXTSIZE configuration function returns the current value of the TEXTSIZE option.

No argument is required. You can simply use it in a SELECT statement to return the current TEXTSIZE value.

The TEXTSIZE value specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement. This value can be set using SET TEXTSIZE.

Continue reading →

Use SCOPE_IDENTITY() to Return the Last-Inserted Identity Value in the Same Scope (SQL Server)

Posted on by Ian

In SQL Server, you can use the T-SQL SCOPE_IDENTITY() function to return the last identity value inserted into an identity column in the same scope.

A scope is a module (stored procedure, trigger, function, or batch). If two statements are in the same stored procedure, function, or batch, they are in the same scope.

Note that it returns the last identity value generated in any table in the current session. This is in contrast to the IDENT_CURRENT() function, which returns the last-inserted identity value for a given table, regardless of which session it’s in.

SCOPE_IDENTITY() is very similar to @@IDENTITY in that they both return the last-inserted identity value in the current session. The difference is that SCOPE_IDENTITY() is limited to the current scope, whereas @@IDENTITY is not limited to a specific scope.

Continue reading →

Use @@IDENTITY to Return the Last-Inserted Identity Value in SQL Server

Posted on by Ian

In SQL Server, you can use the T-SQL @@IDENTITY system function to return the last-inserted identity value in the current session.

Note that it returns the last identity value generated in any table in the current session. This is in contrast to the IDENT_CURRENT() function, which returns the last-inserted identity value for a given table.

The SCOPE_IDENTITY() function is very similar to @@IDENTITY in that it also returns the last-inserted identity value in the current session. The difference is that SCOPE_IDENTITY() is limited to the current scope.

Continue reading →

IDENTITY() vs IDENTITY() in SQL Server: What’s the Difference?

Posted on by Ian

It might seem strange, but T-SQL has an IDENTITY() property and an IDENTITY() function, each of which serve a (similar, but) different purpose.

  • The IDENTITY() property creates an identity column in a table. An identity column contains an automatically incrementing identification number. This property is used with the CREATE TABLE and ALTER TABLE statements.
  • The IDENTITY() function however, is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

There’s also the SQL-DMO Identity property that exposes the row identity property of a column, but I won’t cover that here. Microsoft advises that this property will be removed in a future version of SQL Server, and that you should avoid using it in new development work.

Continue reading →