Function Between in SQL Server 2000

J

Jose Perdigao

Good afternoon,
1. In-Line-Funtion on the criteria column I type BETWEEN dbo.iStdt() AND
dbo.iDate() doesn´t work
2. If I type BETWEEN '01/01/2005' AND '12/12/2005' it works. This option I
can't use because the date is not variable.
3. if I type in one crteria column >=dbo.iStdt() and another column
<=dbo.iDate() it works.

My question is. Is it possible use the first criteria (BETWEEN dbo.iStdt()
AND dbo.iDate() ) in SQL server 2000?
I prefer this option because is short and I type in one column.

NOTE: iStdt() and iDate, are scalar UDF type datetime
Thanks
José Perdigão
 
G

giorgio rancati

Hi Jose,

the *BETWEEN dbo.iStdt() AND dbo.iDate() * it works
Open Query Analyzer and try this
----
USE TEMPDB
GO
SET NOCOUNT ON

CREATE TABLE Tab1
(MyDate DATETIME)
GO

Insert Tab1
SELECT '20060101' UNION
SELECT '20060102' UNION
SELECT '20060103' UNION
SELECT '20060104' UNION
SELECT '20060105' UNION
SELECT '20060106' UNION
SELECT '20060107' UNION
SELECT '20060108' UNION
SELECT '20060109' UNION
SELECT '20060110' UNION
SELECT '20060111' UNION
SELECT '20060112' UNION
SELECT '20060113' UNION
SELECT '20060114' UNION
SELECT '20060115'
GO

CREATE FUNCTION dbo.iStdt()
RETURNS DATETIME AS
BEGIN
RETURN '20060105'
END
GO

CREATE FUNCTION dbo.iDate()
RETURNS DATETIME AS
BEGIN
RETURN '20060110'
END
GO

SELECT *
FROM Tab1
WHERE Mydate BETWEEN dbo.iStdt() AND dbo.iDate()
GO

DROP FUNCTION dbo.iDate ,dbo.iStdt
DROP TABLE Tab1
----

Result
MyDate
----------
2006-01-05
2006-01-06
2006-01-07
2006-01-08
2006-01-09
2006-01-10

bye
 
J

Jose Perdigao

Thanks Giorgio.

It's not working. I'll try to explain what I have.



Step 1

I create Setusers table with the following fields and data.

Login iStdt iData Area
....

perdijc 1/1/2005 12/12/2005 SFA .

jeff 1/3/2005 10/10/2005 CFA .



Step 2

I created a view, called iUserCrt to filter the current user from the table
Setusers

SELECT dbo.Setusers.*

FROM dbo. Setusers WHERE (Login = dbo.iLogin())



Note: iLogin() return the data of current user ( I developed this function
with your suggestion)



If the current user is perdijc it returns:

Login iStdt iData Area ..

perdijc 1/1/2005 12/12/2005 SFA





Step 3

I created the functions iDate and iStdt from the view iUserCrt



********

ALTER FUNCTION dbo.iDate ()

RETURNS datetime

AS

BEGIN

RETURN (SELECT iDate FROM dbo.iUserCrt)

END

/for the current data, this function return 12/12/2005 */

*********

ALTER FUNCTION dbo.iStdt ()

RETURNS datetime

AS

BEGIN

RETURN (SELECT iStdt FROM dbo.iUserCrt)

END

/for the current data, this function return 1/1/2005 */

***********



For all sp, views or in-line-functions if I need filter by date, I apply in
criteria iDate() or iStdt() or >= idate() it works fine if apply between
doesn't work.



Could you help me? What is wrong?



Note, I developed these sequences to pass the parameters to setusers table
and then always I have the date and start date passed to the functions. I
use these procedures for pass all parameters and is working fine.



Thanks,

Jose





giorgio rancati said:
Hi Jose,

the *BETWEEN dbo.iStdt() AND dbo.iDate() * it works
Open Query Analyzer and try this
----
USE TEMPDB
GO
SET NOCOUNT ON

CREATE TABLE Tab1
(MyDate DATETIME)
GO

Insert Tab1
SELECT '20060101' UNION
SELECT '20060102' UNION
SELECT '20060103' UNION
SELECT '20060104' UNION
SELECT '20060105' UNION
SELECT '20060106' UNION
SELECT '20060107' UNION
SELECT '20060108' UNION
SELECT '20060109' UNION
SELECT '20060110' UNION
SELECT '20060111' UNION
SELECT '20060112' UNION
SELECT '20060113' UNION
SELECT '20060114' UNION
SELECT '20060115'
GO

CREATE FUNCTION dbo.iStdt()
RETURNS DATETIME AS
BEGIN
RETURN '20060105'
END
GO

CREATE FUNCTION dbo.iDate()
RETURNS DATETIME AS
BEGIN
RETURN '20060110'
END
GO

SELECT *
FROM Tab1
WHERE Mydate BETWEEN dbo.iStdt() AND dbo.iDate()
GO

DROP FUNCTION dbo.iDate ,dbo.iStdt
DROP TABLE Tab1
----

Result
MyDate
----------
2006-01-05
2006-01-06
2006-01-07
2006-01-08
2006-01-09
2006-01-10

bye
--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
Good afternoon,
1. In-Line-Funtion on the criteria column I type BETWEEN dbo.iStdt() AND
dbo.iDate() doesn´t work
2. If I type BETWEEN '01/01/2005' AND '12/12/2005' it works. This option
I
can't use because the date is not variable.
3. if I type in one crteria column >=dbo.iStdt() and another column
<=dbo.iDate() it works.

My question is. Is it possible use the first criteria (BETWEEN
dbo.iStdt()
AND dbo.iDate() ) in SQL server 2000?
I prefer this option because is short and I type in one column.

NOTE: iStdt() and iDate, are scalar UDF type datetime
Thanks
José Perdigão
Click to expand...
Click to expand...
 
G

giorgio rancati

Hi Jose,

I'm sorry but i don't succeed to reproduce the problem :(
This is the T-SQL statement where I try your issues but it works :(
----
use tempdb
go

Create Table Setusers
([Login] varchar(30),
iStdt datetime,
iDate datetime,
Area CHAR(3))

/* insert values in Setusers
My Login name is Rancati */
INSERT Setusers
VALUES('perdijc','20060101','20060105','SFA')
INSERT Setusers
VALUES('jeff','20060106','20060110','CFA')
INSERT Setusers
VALUES('Rancati','20060111','20060115','HFA')
GO

/* FUNCTION iLogin */
CREATE FUNCTION dbo.iLogin (@Type bit)
RETURNS varchar(30)
AS
-- @Type 0 returns Domain
-- 1 returns user
BEGIN

/* Sql Server autentication mode */
IF CharIndex('\',system_user)=0
RETURN system_user

/* Windows Autentication mode */

--Domain
IF @Type=0
RETURN LEFT(system_user,CharIndex('\',system_user)-1)

--User
RETURN SUBSTRING(system_user,CharIndex('\',system_user)+1,30)

END
GO

/* VIEW iUserCrt*/
Create view iUserCrt
AS
SELECT dbo.Setusers.*
FROM dbo. Setusers
WHERE (Login = dbo.iLogin(1))
GO

/* FUNCTION dbo.iDate */
CREATE FUNCTION dbo.iDate ()
RETURNS datetime
AS
BEGIN

RETURN (SELECT iDate FROM dbo.iUserCrt )

END
GO

/* FUNCTION dbo.iStdt */
CREATE FUNCTION dbo.iStdt ()
RETURNS datetime
AS

BEGIN

RETURN (SELECT iStdt FROM dbo.iUserCrt )

END
GO


/* Test Table */
CREATE TABLE Tab1
(MyDate DATETIME)
GO

Insert Tab1
SELECT '20060101' UNION
SELECT '20060102' UNION
SELECT '20060103' UNION
SELECT '20060104' UNION
SELECT '20060105' UNION
SELECT '20060106' UNION
SELECT '20060107' UNION
SELECT '20060108' UNION
SELECT '20060109' UNION
SELECT '20060110' UNION
SELECT '20060111' UNION
SELECT '20060112' UNION
SELECT '20060113' UNION
SELECT '20060114' UNION
SELECT '20060115' UNION
SELECT '20060116' UNION
SELECT '20060117' UNION
SELECT '20060118' UNION
SELECT '20060119' UNION
SELECT '20060120'
GO

/* functions result */
SELECT dbo.iStdt() iStd ,
dbo.iDate() iDate ,
dbo.iLogin(1) iLogin

/******************************
The Select
******************************/
SELECT *
FROM Tab1
WHERE Mydate BETWEEN dbo.iStdt() AND dbo.iDate()

/* Clean database */
Drop View dbo.iUserCrt
Drop Function dbo.iDate ,dbo.iStdt,dbo.iLogin
Drop Table SetUsers,Tab1
----

Result

iStd iDate iLogin
------------- ------------ ---------
2006-01-11 2006-01-15 Rancati

MyDate
---------------
2006-01-11
2006-01-12
2006-01-13
2006-01-14
2006-01-15


SELECT @@Version
 
J

Jose Perdigao

Hi Giorgio.
I'm devloping an ADP and need create views, sp or in-line-functions to
generate reports, open forms, open queries, send data to Excel using OLE
(CopyFromRecordset).
So, I need to know what is faster and more consistent.

1. I create a view (J1_Allocz) based in two tables, it works fine and
open quicly.
SELECT dbo.A2_Wells.*, dbo.B1_Allocation.*
FROM dbo.A2_Wells INNER JOIN dbo.B1_Allocation ON
dbo.A2_Wells.WellID = dbo.B1_Allocation.Wellz

2. I create a view based in view (J1_Allocz) using between iStdt() and
iDate() it opens but is slower than 1 and the number of rows is less than 1.
Is it normal?
SELECT dbo.J1_AllocFctrs.*
FROM dbo.J1_AllocFctrs
WHERE (dDate BETWEEN dbo.iFDM() AND dbo.iDate())

3. I create a in-line-function based in view (J1_Allocz) using Between
iStdt() and iDate() it doesn't open, timeout expired.
SELECT dbo.J1_Allocz.*
FROM dbo.J1_Allocz
WHERE (dDatez BETWEEN dbo.iStdt() AND dbo.iDate())

4. I create a in-line-function based in view (J1_Allocz) using >=iStdt()
in one criteria column and in next column <=iDate() open faster than 2.
ALTER FUNCTION dbo.J1_AlloczYL ()
RETURNS TABLE
AS
RETURN ( SELECT dbo.J1_Allocz.*
FROM dbo.J1_Allocz
WHERE (dDatez >= dbo.iFDY()) OR (dDatez <= dbo.iDate()) )

Can I conclude?
in-line-function I can not use between
in views I can use between but >=funct1() or <=funt2 is faster than between
Probably, something is wrong but I don't know.
Could you help me?

Thanks
José Perdigão





giorgio rancati said:
Hi Jose,

I'm sorry but i don't succeed to reproduce the problem :(
This is the T-SQL statement where I try your issues but it works :(
----
use tempdb
go

Create Table Setusers
([Login] varchar(30),
iStdt datetime,
iDate datetime,
Area CHAR(3))

/* insert values in Setusers
My Login name is Rancati */
INSERT Setusers
VALUES('perdijc','20060101','20060105','SFA')
INSERT Setusers
VALUES('jeff','20060106','20060110','CFA')
INSERT Setusers
VALUES('Rancati','20060111','20060115','HFA')
GO

/* FUNCTION iLogin */
CREATE FUNCTION dbo.iLogin (@Type bit)
RETURNS varchar(30)
AS
-- @Type 0 returns Domain
-- 1 returns user
BEGIN

/* Sql Server autentication mode */
IF CharIndex('\',system_user)=0
RETURN system_user

/* Windows Autentication mode */

--Domain
IF @Type=0
RETURN LEFT(system_user,CharIndex('\',system_user)-1)

--User
RETURN SUBSTRING(system_user,CharIndex('\',system_user)+1,30)

END
GO

/* VIEW iUserCrt*/
Create view iUserCrt
AS
SELECT dbo.Setusers.*
FROM dbo. Setusers
WHERE (Login = dbo.iLogin(1))
GO

/* FUNCTION dbo.iDate */
CREATE FUNCTION dbo.iDate ()
RETURNS datetime
AS
BEGIN

RETURN (SELECT iDate FROM dbo.iUserCrt )

END
GO

/* FUNCTION dbo.iStdt */
CREATE FUNCTION dbo.iStdt ()
RETURNS datetime
AS

BEGIN

RETURN (SELECT iStdt FROM dbo.iUserCrt )

END
GO


/* Test Table */
CREATE TABLE Tab1
(MyDate DATETIME)
GO

Insert Tab1
SELECT '20060101' UNION
SELECT '20060102' UNION
SELECT '20060103' UNION
SELECT '20060104' UNION
SELECT '20060105' UNION
SELECT '20060106' UNION
SELECT '20060107' UNION
SELECT '20060108' UNION
SELECT '20060109' UNION
SELECT '20060110' UNION
SELECT '20060111' UNION
SELECT '20060112' UNION
SELECT '20060113' UNION
SELECT '20060114' UNION
SELECT '20060115' UNION
SELECT '20060116' UNION
SELECT '20060117' UNION
SELECT '20060118' UNION
SELECT '20060119' UNION
SELECT '20060120'
GO

/* functions result */
SELECT dbo.iStdt() iStd ,
dbo.iDate() iDate ,
dbo.iLogin(1) iLogin

/******************************
The Select
******************************/
SELECT *
FROM Tab1
WHERE Mydate BETWEEN dbo.iStdt() AND dbo.iDate()

/* Clean database */
Drop View dbo.iUserCrt
Drop Function dbo.iDate ,dbo.iStdt,dbo.iLogin
Drop Table SetUsers,Tab1
----

Result

iStd iDate iLogin
------------- ------------ ---------
2006-01-11 2006-01-15 Rancati

MyDate
---------------
2006-01-11
2006-01-12
2006-01-13
2006-01-14
2006-01-15


SELECT @@Version
-----
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Click to expand...
 
J

Jose Perdigao

Hi Giorgio,
I tested the views in query analyzer and the function between dDate BETWEEN
dbo.iStdt() AND dbo.iDate() works but is too slow, I don't know why.
If you have an ideia about this problem, i'll apreciate a lot.
Thanks.
Jose


giorgio rancati said:
Hi Jose,

I'm sorry but i don't succeed to reproduce the problem :(
This is the T-SQL statement where I try your issues but it works :(
----
use tempdb
go

Create Table Setusers
([Login] varchar(30),
iStdt datetime,
iDate datetime,
Area CHAR(3))

/* insert values in Setusers
My Login name is Rancati */
INSERT Setusers
VALUES('perdijc','20060101','20060105','SFA')
INSERT Setusers
VALUES('jeff','20060106','20060110','CFA')
INSERT Setusers
VALUES('Rancati','20060111','20060115','HFA')
GO

/* FUNCTION iLogin */
CREATE FUNCTION dbo.iLogin (@Type bit)
RETURNS varchar(30)
AS
-- @Type 0 returns Domain
-- 1 returns user
BEGIN

/* Sql Server autentication mode */
IF CharIndex('\',system_user)=0
RETURN system_user

/* Windows Autentication mode */

--Domain
IF @Type=0
RETURN LEFT(system_user,CharIndex('\',system_user)-1)

--User
RETURN SUBSTRING(system_user,CharIndex('\',system_user)+1,30)

END
GO

/* VIEW iUserCrt*/
Create view iUserCrt
AS
SELECT dbo.Setusers.*
FROM dbo. Setusers
WHERE (Login = dbo.iLogin(1))
GO

/* FUNCTION dbo.iDate */
CREATE FUNCTION dbo.iDate ()
RETURNS datetime
AS
BEGIN

RETURN (SELECT iDate FROM dbo.iUserCrt )

END
GO

/* FUNCTION dbo.iStdt */
CREATE FUNCTION dbo.iStdt ()
RETURNS datetime
AS

BEGIN

RETURN (SELECT iStdt FROM dbo.iUserCrt )

END
GO


/* Test Table */
CREATE TABLE Tab1
(MyDate DATETIME)
GO

Insert Tab1
SELECT '20060101' UNION
SELECT '20060102' UNION
SELECT '20060103' UNION
SELECT '20060104' UNION
SELECT '20060105' UNION
SELECT '20060106' UNION
SELECT '20060107' UNION
SELECT '20060108' UNION
SELECT '20060109' UNION
SELECT '20060110' UNION
SELECT '20060111' UNION
SELECT '20060112' UNION
SELECT '20060113' UNION
SELECT '20060114' UNION
SELECT '20060115' UNION
SELECT '20060116' UNION
SELECT '20060117' UNION
SELECT '20060118' UNION
SELECT '20060119' UNION
SELECT '20060120'
GO

/* functions result */
SELECT dbo.iStdt() iStd ,
dbo.iDate() iDate ,
dbo.iLogin(1) iLogin

/******************************
The Select
******************************/
SELECT *
FROM Tab1
WHERE Mydate BETWEEN dbo.iStdt() AND dbo.iDate()

/* Clean database */
Drop View dbo.iUserCrt
Drop Function dbo.iDate ,dbo.iStdt,dbo.iLogin
Drop Table SetUsers,Tab1
----

Result

iStd iDate iLogin
------------- ------------ ---------
2006-01-11 2006-01-15 Rancati

MyDate
---------------
2006-01-11
2006-01-12
2006-01-13
2006-01-14
2006-01-15


SELECT @@Version
-----
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Click to expand...
 
J

Jose Perdigao

Hi Giorgio,
I'm still working about creting function in sql server.

I i run the following statement in query analyser and I was surprised with
teh perfomance:
J1_Allocation is a view without filters, based in tables
A4_Users is a table

View:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iFDY
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

I have many and many views with this conditions. So, I would like replace
the statment by a function, it's much easier to bulding views or sp. I
created functions, but the perfomance decrease. Do you have any ideas?
Thanks


giorgio rancati said:
Hi Jose,

I'm sorry but i don't succeed to reproduce the problem :(
This is the T-SQL statement where I try your issues but it works :(
----
use tempdb
go

Create Table Setusers
([Login] varchar(30),
iStdt datetime,
iDate datetime,
Area CHAR(3))

/* insert values in Setusers
My Login name is Rancati */
INSERT Setusers
VALUES('perdijc','20060101','20060105','SFA')
INSERT Setusers
VALUES('jeff','20060106','20060110','CFA')
INSERT Setusers
VALUES('Rancati','20060111','20060115','HFA')
GO

/* FUNCTION iLogin */
CREATE FUNCTION dbo.iLogin (@Type bit)
RETURNS varchar(30)
AS
-- @Type 0 returns Domain
-- 1 returns user
BEGIN

/* Sql Server autentication mode */
IF CharIndex('\',system_user)=0
RETURN system_user

/* Windows Autentication mode */

--Domain
IF @Type=0
RETURN LEFT(system_user,CharIndex('\',system_user)-1)

--User
RETURN SUBSTRING(system_user,CharIndex('\',system_user)+1,30)

END
GO

/* VIEW iUserCrt*/
Create view iUserCrt
AS
SELECT dbo.Setusers.*
FROM dbo. Setusers
WHERE (Login = dbo.iLogin(1))
GO

/* FUNCTION dbo.iDate */
CREATE FUNCTION dbo.iDate ()
RETURNS datetime
AS
BEGIN

RETURN (SELECT iDate FROM dbo.iUserCrt )

END
GO

/* FUNCTION dbo.iStdt */
CREATE FUNCTION dbo.iStdt ()
RETURNS datetime
AS

BEGIN

RETURN (SELECT iStdt FROM dbo.iUserCrt )

END
GO


/* Test Table */
CREATE TABLE Tab1
(MyDate DATETIME)
GO

Insert Tab1
SELECT '20060101' UNION
SELECT '20060102' UNION
SELECT '20060103' UNION
SELECT '20060104' UNION
SELECT '20060105' UNION
SELECT '20060106' UNION
SELECT '20060107' UNION
SELECT '20060108' UNION
SELECT '20060109' UNION
SELECT '20060110' UNION
SELECT '20060111' UNION
SELECT '20060112' UNION
SELECT '20060113' UNION
SELECT '20060114' UNION
SELECT '20060115' UNION
SELECT '20060116' UNION
SELECT '20060117' UNION
SELECT '20060118' UNION
SELECT '20060119' UNION
SELECT '20060120'
GO

/* functions result */
SELECT dbo.iStdt() iStd ,
dbo.iDate() iDate ,
dbo.iLogin(1) iLogin

/******************************
The Select
******************************/
SELECT *
FROM Tab1
WHERE Mydate BETWEEN dbo.iStdt() AND dbo.iDate()

/* Clean database */
Drop View dbo.iUserCrt
Drop Function dbo.iDate ,dbo.iStdt,dbo.iLogin
Drop Table SetUsers,Tab1
----

Result

iStd iDate iLogin
------------- ------------ ---------
2006-01-11 2006-01-15 Rancati

MyDate
---------------
2006-01-11
2006-01-12
2006-01-13
2006-01-14
2006-01-15


SELECT @@Version
-----
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Click to expand...
 
G

giorgio rancati

Hi Jose,

All the four queries are different!
You can't compare them

bye
--
Giorgio Rancati
[Office Access MVP]


Jose Perdigao said:
Hi Giorgio.
I'm devloping an ADP and need create views, sp or in-line-functions to
generate reports, open forms, open queries, send data to Excel using OLE
(CopyFromRecordset).
So, I need to know what is faster and more consistent.

1. I create a view (J1_Allocz) based in two tables, it works fine and
open quicly.
SELECT dbo.A2_Wells.*, dbo.B1_Allocation.*
FROM dbo.A2_Wells INNER JOIN dbo.B1_Allocation ON
dbo.A2_Wells.WellID = dbo.B1_Allocation.Wellz

2. I create a view based in view (J1_Allocz) using between iStdt() and
iDate() it opens but is slower than 1 and the number of rows is less than 1.
Is it normal?
SELECT dbo.J1_AllocFctrs.*
FROM dbo.J1_AllocFctrs
WHERE (dDate BETWEEN dbo.iFDM() AND dbo.iDate())

3. I create a in-line-function based in view (J1_Allocz) using Between
iStdt() and iDate() it doesn't open, timeout expired.
SELECT dbo.J1_Allocz.*
FROM dbo.J1_Allocz
WHERE (dDatez BETWEEN dbo.iStdt() AND dbo.iDate())

4. I create a in-line-function based in view (J1_Allocz) using
=iStdt()
in one criteria column and in next column <=iDate() open faster than 2.
ALTER FUNCTION dbo.J1_AlloczYL ()
RETURNS TABLE
AS
RETURN ( SELECT dbo.J1_Allocz.*
FROM dbo.J1_Allocz
WHERE (dDatez >= dbo.iFDY()) OR (dDatez <= dbo.iDate()) )

Can I conclude?
in-line-function I can not use between
in views I can use between but >=funct1() or <=funt2 is faster than between
Probably, something is wrong but I don't know.
Could you help me?

Thanks
José Perdigão





giorgio rancati said:
Hi Jose,

I'm sorry but i don't succeed to reproduce the problem :(
This is the T-SQL statement where I try your issues but it works :(
----
use tempdb
go

Create Table Setusers
([Login] varchar(30),
iStdt datetime,
iDate datetime,
Area CHAR(3))

/* insert values in Setusers
My Login name is Rancati */
INSERT Setusers
VALUES('perdijc','20060101','20060105','SFA')
INSERT Setusers
VALUES('jeff','20060106','20060110','CFA')
INSERT Setusers
VALUES('Rancati','20060111','20060115','HFA')
GO

/* FUNCTION iLogin */
CREATE FUNCTION dbo.iLogin (@Type bit)
RETURNS varchar(30)
AS
-- @Type 0 returns Domain
-- 1 returns user
BEGIN

/* Sql Server autentication mode */
IF CharIndex('\',system_user)=0
RETURN system_user

/* Windows Autentication mode */

--Domain
IF @Type=0
RETURN LEFT(system_user,CharIndex('\',system_user)-1)

--User
RETURN SUBSTRING(system_user,CharIndex('\',system_user)+1,30)

END
GO

/* VIEW iUserCrt*/
Create view iUserCrt
AS
SELECT dbo.Setusers.*
FROM dbo. Setusers
WHERE (Login = dbo.iLogin(1))
GO

/* FUNCTION dbo.iDate */
CREATE FUNCTION dbo.iDate ()
RETURNS datetime
AS
BEGIN

RETURN (SELECT iDate FROM dbo.iUserCrt )

END
GO

/* FUNCTION dbo.iStdt */
CREATE FUNCTION dbo.iStdt ()
RETURNS datetime
AS

BEGIN

RETURN (SELECT iStdt FROM dbo.iUserCrt )

END
GO


/* Test Table */
CREATE TABLE Tab1
(MyDate DATETIME)
GO

Insert Tab1
SELECT '20060101' UNION
SELECT '20060102' UNION
SELECT '20060103' UNION
SELECT '20060104' UNION
SELECT '20060105' UNION
SELECT '20060106' UNION
SELECT '20060107' UNION
SELECT '20060108' UNION
SELECT '20060109' UNION
SELECT '20060110' UNION
SELECT '20060111' UNION
SELECT '20060112' UNION
SELECT '20060113' UNION
SELECT '20060114' UNION
SELECT '20060115' UNION
SELECT '20060116' UNION
SELECT '20060117' UNION
SELECT '20060118' UNION
SELECT '20060119' UNION
SELECT '20060120'
GO

/* functions result */
SELECT dbo.iStdt() iStd ,
dbo.iDate() iDate ,
dbo.iLogin(1) iLogin

/******************************
The Select
******************************/
SELECT *
FROM Tab1
WHERE Mydate BETWEEN dbo.iStdt() AND dbo.iDate()

/* Clean database */
Drop View dbo.iUserCrt
Drop Function dbo.iDate ,dbo.iStdt,dbo.iLogin
Drop Table SetUsers,Tab1
----

Result

iStd iDate iLogin
------------- ------------ ---------
2006-01-11 2006-01-15 Rancati

MyDate
---------------
2006-01-11
2006-01-12
2006-01-13
2006-01-14
2006-01-15


SELECT @@Version
-----
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Click to expand...
Click to expand...
 
J

Jose Perdigao

SQL Server version.

in query analyzer, I run SELECT @@Version and the result is:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)


Perfomances in query analyzer:



query 1:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez >= dbo.iStdt() AND dDatez <= dbo.iDate())

time:30sec; 31502 rows; 49 columns



query2:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:30sec; 31502 rows; 49 columns



You are right, the time is the same



query3:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



This performance is good, but it has a disadvantage in condition. It's more
complex to type, this is my problem.



Thanks

jose perdigao

giorgio rancati said:
Hi Jose,

This is very weird because the T-SQL optimizer transforms the
----
Data BETWEEN dbo.iStdt() AND dbo.iDate()
----
in
----
Data>=dbo.iStdt() AND Data <=dbo.iDate()
----
so, there isn't difference.

What's your Sql Server version ?
To see it type
----
SELECT @@Version
----
in Query Analyzer.

--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
Hi Giorgio,
I tested the views in query analyzer and the function between dDate BETWEEN
dbo.iStdt() AND dbo.iDate() works but is too slow, I don't know why.
If you have an ideia about this problem, i'll apreciate a lot.
Thanks.
Jose
Click to expand...
Click to expand...
 
G

giorgio rancati

Hi Jose ,

well, the problem doesn't the Between operator bat the many functions.

try this workaraund
----
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
CROSS JOIN iUserCrt
WHERE dDatez BETWEEN iStdt AND iDate
----

The iUserCrt is your View
----
/* VIEW iUserCrt*/
Create view iUserCrt
AS
SELECT dbo.Setusers.*
FROM dbo. Setusers
WHERE (Login = dbo.iLogin())
GO
---


bye
--
Giorgio Rancati
[Office Access MVP]
Jose Perdigao said:
SQL Server version.

in query analyzer, I run SELECT @@Version and the result is:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)


Perfomances in query analyzer:



query 1:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez >= dbo.iStdt() AND dDatez <= dbo.iDate())

time:30sec; 31502 rows; 49 columns



query2:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:30sec; 31502 rows; 49 columns



You are right, the time is the same



query3:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



This performance is good, but it has a disadvantage in condition. It's more
complex to type, this is my problem.



Thanks

jose perdigao

giorgio rancati said:
Hi Jose,

This is very weird because the T-SQL optimizer transforms the
----
Data BETWEEN dbo.iStdt() AND dbo.iDate()
----
in
----
Data>=dbo.iStdt() AND Data <=dbo.iDate()
----
so, there isn't difference.

What's your Sql Server version ?
To see it type
----
SELECT @@Version
----
in Query Analyzer.

--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
Hi Giorgio,
I tested the views in query analyzer and the function between dDate BETWEEN
dbo.iStdt() AND dbo.iDate() works but is too slow, I don't know why.
If you have an ideia about this problem, i'll apreciate a lot.
Thanks.
Jose
Click to expand...
Click to expand...
Click to expand...
 
J

Jose Perdigao

With your suggestions, the performance is better than using functions and
worst using select... in criteria.



1. Using functions: time:30sec; 31502 rows; 49
columns

2. Using Select...in criteria : time:8sec; 31502 rows; 49 columns

3. using CROSS JOIN iUserCrt: time:15sec; 31502 rows; 49 columns



I must use the solution 2. I didn't like to use this solution, because I
spend much more time to build views.



José Perdigão


giorgio rancati said:
Hi Jose ,

well, the problem doesn't the Between operator bat the many functions.

try this workaraund
----
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
CROSS JOIN iUserCrt
WHERE dDatez BETWEEN iStdt AND iDate
----

The iUserCrt is your View
----
/* VIEW iUserCrt*/
Create view iUserCrt
AS
SELECT dbo.Setusers.*
FROM dbo. Setusers
WHERE (Login = dbo.iLogin())
GO
---


bye
--
Giorgio Rancati
[Office Access MVP]
Jose Perdigao said:
SQL Server version.

in query analyzer, I run SELECT @@Version and the result is:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)


Perfomances in query analyzer:



query 1:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez >= dbo.iStdt() AND dDatez <= dbo.iDate())

time:30sec; 31502 rows; 49 columns



query2:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:30sec; 31502 rows; 49 columns



You are right, the time is the same



query3:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



This performance is good, but it has a disadvantage in condition. It's more
complex to type, this is my problem.



Thanks

jose perdigao

message
Hi Jose,

This is very weird because the T-SQL optimizer transforms the
----
Data BETWEEN dbo.iStdt() AND dbo.iDate()
----
in
----
Data>=dbo.iStdt() AND Data <=dbo.iDate()
----
so, there isn't difference.

What's your Sql Server version ?
To see it type
----
SELECT @@Version
----
in Query Analyzer.

--
Giorgio Rancati
[Office Access MVP]

"Jose Perdigao" <[email protected]> ha scritto nel messaggio
Hi Giorgio,
I tested the views in query analyzer and the function between dDate
BETWEEN
dbo.iStdt() AND dbo.iDate() works but is too slow, I don't know why.
If you have an ideia about this problem, i'll apreciate a lot.
Thanks.
Jose
Click to expand...
Click to expand...
Click to expand...
 
J

Jose Perdigao

Hi Giorgio

I have good news about UDFs



I create cluster in the tables and now the performance are the following:



query 1:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:8sec; 31502 rows; 49 columns

query2:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



From this performance I'll use UDFs in Where condition



What's the impact of the clusters in database and in the UDFs?

Thanks,

José Perdigão

Thanks



giorgio rancati said:
Hi Jose ,

well, the problem doesn't the Between operator bat the many functions.

try this workaraund
----
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
CROSS JOIN iUserCrt
WHERE dDatez BETWEEN iStdt AND iDate
----

The iUserCrt is your View
----
/* VIEW iUserCrt*/
Create view iUserCrt
AS
SELECT dbo.Setusers.*
FROM dbo. Setusers
WHERE (Login = dbo.iLogin())
GO
---


bye
--
Giorgio Rancati
[Office Access MVP]
Jose Perdigao said:
SQL Server version.

in query analyzer, I run SELECT @@Version and the result is:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)


Perfomances in query analyzer:



query 1:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez >= dbo.iStdt() AND dDatez <= dbo.iDate())

time:30sec; 31502 rows; 49 columns



query2:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:30sec; 31502 rows; 49 columns



You are right, the time is the same



query3:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



This performance is good, but it has a disadvantage in condition. It's more
complex to type, this is my problem.



Thanks

jose perdigao

message
Hi Jose,

This is very weird because the T-SQL optimizer transforms the
----
Data BETWEEN dbo.iStdt() AND dbo.iDate()
----
in
----
Data>=dbo.iStdt() AND Data <=dbo.iDate()
----
so, there isn't difference.

What's your Sql Server version ?
To see it type
----
SELECT @@Version
----
in Query Analyzer.

--
Giorgio Rancati
[Office Access MVP]

"Jose Perdigao" <[email protected]> ha scritto nel messaggio
Hi Giorgio,
I tested the views in query analyzer and the function between dDate
BETWEEN
dbo.iStdt() AND dbo.iDate() works but is too slow, I don't know why.
If you have an ideia about this problem, i'll apreciate a lot.
Thanks.
Jose
Click to expand...
Click to expand...
Click to expand...
 
G

giorgio rancati

Hi Jose ,

This is my test's resuls

1. Using Function Time 65sec; 42129 row 70 colunmns
2. Using Select...in criteria : Time:5sec; 42129 row 70 colunmns
3. Using Cross Join: Time 4sec; 42129 row 70 colunmns

Before executed they I executed the statement
----
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
----
use they only for test.

ps. Your Sql Server version is very old
http://support.microsoft.com/kb/321185/en-us#XSLTH3130121121120121120120
perhaps you need to upgrade it as Sp4

bye
 
G

giorgio rancati

Hi Jose,

The Cluster Index on [dDatez] column changes the execution plan, Sql Server
resolves it with a Clustered Index Seek.
But, it's neccessary?
When you will perform the select on another datetime field will have low
performance
 
J

Jose Perdigao

I created clausters in the tables because when I execute the queries using
UDFs in Where condition, the perfomance increased a lot.
In your example, I think if you create clausters in the tables, the time in
query1 (using functions) will reduce a lot. Could you try and tell me the
result?

Thanks a lot
Regards,
José perdigão


giorgio rancati said:
Hi Jose,

The Cluster Index on [dDatez] column changes the execution plan, Sql
Server
resolves it with a Clustered Index Seek.
But, it's neccessary?
When you will perform the select on another datetime field will have low
performance

--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
Hi Giorgio

I have good news about UDFs



I create cluster in the tables and now the performance are the following:



query 1:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:8sec; 31502 rows; 49 columns

query2:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



From this performance I'll use UDFs in Where condition



What's the impact of the clusters in database and in the UDFs?

Thanks,

José Perdigão

Thanks
Click to expand...
Click to expand...
 
G

giorgio rancati

Hi Jose,
my results are:


Without Cluster Index
----
1. Using Function Time 65sec; 42129 row 70 colunmns
2. Using Select...in criteria : Time:5sec; 42129 row 70 colunmns
3. Using Cross Join: Time 4sec; 42129 row 70 colunmns
----

With Cluster Index
----
1. Using Function Time 4sec; 42129 row 70 colunmns
2. Using Select...in criteria : Time:4sec; 42129 row 70 colunmns
3. Using Cross Join: Time 4sec; 42129 row 70 colunmns
----

bye
--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
I created clausters in the tables because when I execute the queries using
UDFs in Where condition, the perfomance increased a lot.
In your example, I think if you create clausters in the tables, the time in
query1 (using functions) will reduce a lot. Could you try and tell me the
result?

Thanks a lot
Regards,
José perdigão


giorgio rancati said:
Hi Jose,

The Cluster Index on [dDatez] column changes the execution plan, Sql
Server
resolves it with a Clustered Index Seek.
But, it's neccessary?
When you will perform the select on another datetime field will have low
performance

--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
Hi Giorgio

I have good news about UDFs



I create cluster in the tables and now the performance are the following:



query 1:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:8sec; 31502 rows; 49 columns

query2:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



From this performance I'll use UDFs in Where condition



What's the impact of the clusters in database and in the UDFs?

Thanks,

José Perdigão

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Jose Perdigao

Hi Giorgio,



So, from my and yours results, I think I can create cluster index and I can
build queries using functions because for me it's easier.



Do you think, there are disadvantages create clusters indexes?



I think this is my last question about this issue.



Silvain give me another solution, store the value from the function in a
local variable. But I think we can use only in store procedures.

This is the example



declare @iStdt2 datetime
declare @iDate2 datetime

set @iStdt2 = dbo.iStdt()
set @iDate2 = dbo.iDate()

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN @iStdt2 AND @iDate2)



time: 8sec; 31502 rows; 49 columns



Now I'm much clearer to use criteria in SQL server



Thanks a lot

jose perdigao




giorgio rancati said:
Hi Jose,
my results are:


Without Cluster Index
----
1. Using Function Time 65sec; 42129 row 70 colunmns
2. Using Select...in criteria : Time:5sec; 42129 row 70 colunmns
3. Using Cross Join: Time 4sec; 42129 row 70 colunmns
----

With Cluster Index
----
1. Using Function Time 4sec; 42129 row 70 colunmns
2. Using Select...in criteria : Time:4sec; 42129 row 70 colunmns
3. Using Cross Join: Time 4sec; 42129 row 70 colunmns
----

bye
--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
I created clausters in the tables because when I execute the queries
using
UDFs in Where condition, the perfomance increased a lot.
In your example, I think if you create clausters in the tables, the time in
query1 (using functions) will reduce a lot. Could you try and tell me the
result?

Thanks a lot
Regards,
José perdigão


message
Hi Jose,

The Cluster Index on [dDatez] column changes the execution plan, Sql
Server
resolves it with a Clustered Index Seek.
But, it's neccessary?
When you will perform the select on another datetime field will have
low
performance

--
Giorgio Rancati
[Office Access MVP]

"Jose Perdigao" <[email protected]> ha scritto nel messaggio
Hi Giorgio

I have good news about UDFs



I create cluster in the tables and now the performance are the following:



query 1:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:8sec; 31502 rows; 49 columns

query2:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



From this performance I'll use UDFs in Where condition



What's the impact of the clusters in database and in the UDFs?

Thanks,

José Perdigão

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

giorgio rancati

Jose Perdigao said:
Hi Giorgio,

So, from my and yours results, I think I can create cluster index and I can
build queries using functions because for me it's easier.

Do you think, there are disadvantages create clusters indexes?
Click to expand...

if your filter work always on clustered index there aren't disadvantages,
but when you apply the filter in another field the clustered indexes does
not bring advantages.
I think this is my last question about this issue.

Silvain give me another solution, store the value from the function in a
local variable. But I think we can use only in store procedures.

This is the example
Click to expand...
[CUT]

yes, we saw that the constants are fast
Now I'm much clearer to use criteria in SQL server
:)

Thanks a lot
jose perdigao
Click to expand...

bye
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converted from LINUX time ok, but having issues with the between date. 4
Access ADP project server filter by Date greater than 3
Sort date by Desc doesn't work in SQL Server 2005 2
User Name 5
Access 2000 ADP and SQL Server 2005 2
calling sql server udf in adp from vba 12
Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7 10
date range criteria for SQL server link table 5

Top