May 19, 2017 at 8:37 am
I stumbled upon this function in SSMS version 2016, but i haven't been able to find any documentation for it, nor was i able to get anything meaningful out of it.
Anyone knows what it does?
select GETDEFAULT (1,1)
--returns: Cannot find object ID 1 in database ID 1.
May 19, 2017 at 8:57 am
siggemannen - Friday, May 19, 2017 8:37 AMI stumbled upon this function in SSMS version 2016, but i haven't been able to find any documentation for it, nor was i able to get anything meaningful out of it.Anyone knows what it does?
select GETDEFAULT (1,1)
--returns: Cannot find object ID 1 in database ID 1.
Documentation is lacking for that!
It seems that parameter 1 is database Id and parameter 2 is the object Id for a particular constraint.
Try executing the following to get these:SELECT DB_ID('your db name here');
SELECT TOP 10
*
FROM sys.default_constraints dc;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 19, 2017 at 9:30 am
It gives you the value of a default constraint as if it were evaluated right now. Best example to see this in action would be to create a date default constraint and see it return the current date time.
I would hazard a guess that it is an internal function, and perhaps that's why it is documented. The engine itself perhaps calls this function when inserting or updating a table to get the "default value" on execution to insert into the table;
USE master;
GO
DROP DATABASE IF EXISTS [testDB];
GO
CREATE DATABASE testDB;
GO
USE testDB;
GO
DROP TABLE IF EXISTS [dbo.testTable];
GO
CREATE TABLE dbo.TestTable
( RowId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_TestTable_RowId PRIMARY KEY
, DateTimeStamp DATETIME NOT NULL CONSTRAINT DF_TestTable_DateTimeStamp_GETDATE DEFAULT(GETDATE())
, One SMALLINT NOT NULL CONSTRAINT DF_TestTable_MinsOne_1 DEFAULT(1)
, SomeCheck DATETIME NOT NULL CONSTRAINT Chk_TestTable_SomeCheck_LT_NOW CHECK(SomeCheck < GETDATE())
)
GO
DROP TABLE IF EXISTS dbo.#DefaultConstraintId;
CREATE TABLE dbo.#DefaultConstraintId
( RowId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
, obid INT NOT NULL
);
GO
DECLARE @DbId INT = (SELECT database_id FROM sys.databases WHERE [name] = N'testDB');
DECLARE @Count INT;
DECLARE @i INT = 1;
DECLARE @obid INT;
INSERT dbo.#DefaultConstraintId
(obid)
SELECT [object_Id]
FROM sys.default_constraints;
SET @Count = @@ROWCOUNT;
WHILE (@i <= @Count)
BEGIN
SET @obid = (SELECT obid FROM dbo.#DefaultConstraintId WHERE RowId = @i);
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT GETDEFAULT('+CAST(@DbId as VARCHAR(100))+', '+CAST(@obid as VARCHAR(100))+');';
EXEC (@Sql);
SET @i += 1;
END;
GO
May 21, 2017 at 2:19 am
Thanks guys, very interesting, this function seem to be performed during compilation time and evaluation is inserted inline, or something like that.
Because it's not possible to call with variables.
It can be used to be able to call "RAND" or other side-effect functions inside of other scalar functions if one is perverse enough:
use master;
GO
CREATE TABLE dbo.t_rand(i float not null default RAND())
GO
declare @sql AS NVARCHAR(MAX) = N'
create function dbo.FN_RAND(
)
RETURNS FLOAT
BEGIN
RETURN GETDEFAULT(1, ' + CAST((select OBJECT_ID from sys.default_constraints where parent_object_id = OBJECT_ID('t_rand')) AS NVARCHAR(MAX)) + ')
END
'
EXEC (@SQL)
GO
select dbo.FN_RAND()
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply