I have an user scalar-valued function which takes NVARCHAR(MAX) and returns NVARCHAR(MAX). To increase perfomance of GROUP BY command utilizing this function I wanted to create an index also based on it. But documentation says what CREATE INDEX command only takes columns not expressions, so this is illegal: CREATE INDEX an_index ON a_ta...
A co-worker just came to me with a puzzling SQL query: (essentially) SELECT LEAST(id) FROM tableA A, tableB B WHERE a.name = b.name(+) The result set returned lists three numbers however: LEAST(id) -------------- 621 644 689 (all being IDs that meet the query as if it lacked the LEAST function all together) Why? =) ...
Hi, I have a table where the results are sorted using an "ORDER" column, eg: Doc_Id Doc_Value Doc_Order 1 aaa 1 12 xxx 5 2 bbb 12 3 ccc 24 My issue is to initially set up this order column as efficiently and reusably as possible. My initial take was to set up a scal...
Hi guys I have the following statement: SELECT CASE WHEN (1 = 1) THEN 10 ELSE dbo.at_Test_Function(5) END AS Result I just want to confirm that in this case the function wont be executed? My reason for asking is that the function is particularly slow and if the critiria is true I want to avoid calling the function... Cheers Antho...
I've got a SQL Server function that returns a scalar BIT value and takes one parameter. The following gives you an idea: CREATE FUNCTION dbo.[fnTest] (@StringToTest VARCHAR(10)) RETURNS BIT AS BEGIN DECLARE @b BIT IF @StringToTest = 'A' SET @b = 0 ELSE SET @b = 1 RETURN (@b) END GO Being very new (days!) to SubSonic -...
I have a job that performs several validation checks on rows in several tables in a database. If the check fails it logs the failure in a table that. The information that is logged includes the table name, a uniqueidentifier value of the row that failed, which check it failed, and which job was being run at the time. Here's the simpli...
SQL Server, trying to get day of week via a deterministic UDF. Im sure this must be possible, but cant figure it out. UPDATE: SAMPLE CODE.. CREATE VIEW V_Stuff WITH SCHEMABINDING AS SELECT MD.ID, MD.[DateTime] ... dbo.FN_DayNumeric_DateTime(MD.DateTime) AS [Day], dbo.FN_TimeNumeric_DateTime(MD.DateTime) AS [Time], ... FROM...
Hello, I need help with this scalar-valued function. What I want to do is to return the value I get in MaxValue on the line max(Value) AS MaxValue. The query works and will only return 1 value if ItemId and ListPropertyId exists, but I am not able to create a function of it. CREATE FUNCTION GetLpivMax ( -- Add the parameters for ...
I have the following scalar function in MS SQL 2005: CREATE FUNCTION [dbo].[Distance] ( @lat1 float, @long1 float,@lat2 float, @long2 float ) RETURNS float AS BEGIN RETURN (3958*3.1415926*sqrt((@lat2-@lat1)*(@lat2-@lat1) + cos(@lat2/57.29578)*cos(@lat1/57.29578)*(@long2-@long1)*(@long2-@long1))/180); END I need to be able to call...
I need to display a UI element (e.g. a star or checkmark) for employees that are 'favorites' of the current user (another employee). The Employee model has the following relationship defined to support this: has_and_belongs_to_many :favorites, :class_name => "Employee", :join_table => "favorites", :association_foreign_key => "fav...
We have a scalar function that returns a DateTime. It performs a couple of quick table selects to get its return value. This function is already in use throughout the database - in default constraints, stored procs, etc. I would like to change the implementation of the function (to remove the table hits and make it more efficient) but ap...
I have a requirement to run a query against a database that will return either a zero or one (Checking for existance of specific criteria). The Tech specs I've been given for review state that I should be creating a stored procedure, that will return a single row, with a single column called "result" that will contain a bit value of 0 or...