Tag: what is

How AUTOINCREMENT Works in SQLite

Posted on by Ian

In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that’s inserted into the table.

There are a couple of ways you can create an AUTOINCREMENT column:

  • You can create it implicitly when you define the column as INTEGER PRIMARY KEY.
  • You can create it explicitly with the AUTOINCREMENT keyword. One downside of this method is that it uses extra CPU, memory, disk space, and disk I/O overhead.

Both methods cause the column to use an incrementing value each time a new row is inserted with NULL in that column.

However, there are some subtle differences between how each method works.

Continue reading →

What is @@SERVICENAME in SQL Server?

Posted on by Ian

In SQL Server, the @@SERVICENAME configuration function returns the name of the registry key under which SQL Server is running.

No argument is required. You can simply use it in a SELECT statement to return the registry key’s name.

Note that SQL Server runs as a service named MSSQLServer. The @@SERVICENAME function returns MSSQLSERVER if the current instance is the default instance. It returns the instance name if the current instance is a named instance.

Continue reading →

Introduction to User-Defined Functions in SQL Server

Posted on by Ian

SQL Server ships with a collection of built-in functions that enable you to perform a variety of operations. Each built-in function serves a specific purpose, and can’t be modified. If a function serves your purpose, you can go ahead and use it.

But what if you can’t find a function that serves your purpose?

Then it’s time to write your own user-defined function.

Continue reading →

What is a Scalar UDF in SQL Server?

Posted on by Ian

In SQL Server, a scalar UDF, or scalar user-defined function, is a user-defined function that returns a single value.

This is in contrast to a table-valued function, which returns a result set in the form of a table.

User-defined functions can be written as either T-SQL UDFs or CLR (Common Language Runtime) UDFs. CLR enables you to create UDFs in managed code using any .NET Framework programming language. These functions are then accessible to T-SQL or other managed code.

Continue reading →

What is a Table-Valued Function in SQL Server?

Posted on by Ian

In SQL Server, a table-valued function (TVF) is a user-defined function that returns a table. This is in contrast to a scalar function, which returns a single value.

You can invoke a table-valued function in the same way that you can query a table. For example, you can use it in a SELECT statement. In some cases, table-valued functions can also be used to update, delete, and insert data.

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 →

What is “rowversion” in SQL Server?

Posted on by Ian

In SQL Server, rowversion is a data type that exposes automatically generated, unique binary numbers within a database. It allows you to version-stamp table rows with a unique value. This helps maintain the integrity of the database when multiple users are updating rows at the same time.

Each SQL Server database has a a counter that is incremented for each insert or update operation that is performed on a table that contains a column with the rowversion data type (or its timestamp synonym, which is flagged for deprecation).

If a table contains a rowversion (or timestamp) column, any time a row is inserted or updated, the value of the rowversion column is set to the current rowversion value. This is true, even when an UPDATE statement doesn’t result in any changes to the data.

Continue reading →