SOME | ANY (Transact-SQL)

SQL Server 2008 R2

 

                                    

Compares a scalar value with a single-column set of values. SOME and ANY are equivalent.
 

Syntax

---

 

scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 
     { SOME | ANY } ( subquery ) 

Arguments

---

scalar_expression

Is any valid expression.

{ = | <> | != | > | >= | !> | < | <= | !< }

Is any valid comparison operator.

SOME | ANY

Specifies that a comparison should be made.

subquery

Is a subquery that has a result set of one column. The data type of the column returned must be the same data type as scalar_expression.

Result Types

---

Boolean

Result Value

---

SOME or ANY returns TRUE when the comparison specified is TRUE for any pair (scalar_expression, x) where x is a value in the single-column set; otherwise, returns FALSE.

Remarks

---

SOME requires the scalar_expression to compare positively to at least one value returned by the subquery. For statements that require the scalar_expression to compare positively to every value that is returned by the subquery, see ALL (Transact-SQL). For instance, if the subquery returns values of 2 and 3, scalar_expression = SOME (subquery) would evaluate as TRUE for a scalar_express of 2. If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) would not meet the criteria of the expression.

Examples

---

A. Running a simple example

The following statements create a simple table and add the values of 1, 2, 3, and 4 to the ID column.

Copy

CREATE TABLE T1
(ID int) ;
GO
INSERT T1 VALUES (1) ;
INSERT T1 VALUES (2) ;
INSERT T1 VALUES (3) ;
INSERT T1 VALUES (4) ;

The following query returns TRUE because 3 is less than some of the values in the table.

Copy

IF 3 < SOME (SELECT ID FROM T1)
PRINT 'TRUE' 
ELSE
PRINT 'FALSE' ;

The following query returns FALSE because 3 is not less than all of the values in the table.

Copy

IF 3 < ALL (SELECT ID FROM T1)
PRINT 'TRUE' 
ELSE
PRINT 'FALSE' ;

B. Running a practical example

The following example creates a stored procedure that determines whether all the components of a specified SalesOrderID in the AdventureWorks2008R2 database can be manufactured in the specified number of days. The example uses a subquery to create a list of the number of DaysToManufacture value for all the components of the specific SalesOrderID, and then tests whether any of the values that are returned by the subquery are greater than the number of days specified. If every value of DaysToManufacture that is returned is less than the number provided, the condition is TRUE and the first message is printed.

Copy

USE AdventureWorks2008R2 ;
GO

CREATE PROCEDURE ManyDaysToComplete @OrderID int, @NumberOfDays int
AS
IF 
@NumberOfDays < SOME
   (
    SELECT DaysToManufacture
    FROM Sales.SalesOrderDetail
    JOIN Production.Product 
    ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID 
    WHERE SalesOrderID = @OrderID
   )
PRINT 'At least one item for this order cannot be manufactured in specified number of days.'
ELSE 
PRINT 'All items for this order can be manufactured in the specified number of days or less.' ;

To test the procedure, execute the procedure by using the SalesOrderID 49080, which has one component that requires 2 days and two components that require 0 days. The first statement meets the criteria. The second query does not.

Copy

EXECUTE ManyDaysToComplete 49080, 2 ;

Here is the result set.
All items for this order can be manufactured in the specified number of days or less.

Copy

EXECUTE ManyDaysToComplete 49080, 1 ;

Here is the result set.
At least one item for this order cannot be manufactured in specified number of days.

sys.objects (Transact-SQL)

           

                   

Contains a row for each user-defined, schema-scoped object that is created within a database.

Note
sys.objects does not show DDL triggers, because they are not schema-scoped. All triggers, both DML and DDL, are found in sys.triggers. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.

Column name	Data type	Description
name	sysname	Object name.
object_id	int	Object identification number. Is unique within a database.
principal_id	int	ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. Is NULL if there is no alternate individual owner. Is NULL if the object type is one of the following: C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) TA = Assembly (CLR-integration) trigger TR = SQL trigger UQ = UNIQUE constraint
schema_id	int	ID of the schema that the object is contained in. Schema-scoped system objects are always contained in the sys or INFORMATION_SCHEMA schemas.
parent_object_id	int	ID of the object to which this object belongs. 0 = Not a child object.
type	char(2)	Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint FN = SQL scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF = SQL inline table-valued function IT = Internal table P = SQL Stored Procedure PC = Assembly (CLR) stored-procedure PG = Plan guide PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) RF = Replication-filter-procedure S = System base table SN = Synonym SO = Sequence object SQ = Service queue TA = Assembly (CLR) DML trigger TF = SQL table-valued-function TR = SQL DML trigger TT = Table type U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure
type_desc	nvarchar(60)	Description of the object type: AGGREGATE_FUNCTION CHECK_CONSTRAINT CLR_SCALAR_FUNCTION CLR_STORED_PROCEDURE CLR_TABLE_VALUED_FUNCTION CLR_TRIGGER DEFAULT_CONSTRAINT EXTENDED_STORED_PROCEDURE FOREIGN_KEY_CONSTRAINT INTERNAL_TABLE PLAN_GUIDE PRIMARY_KEY_CONSTRAINT REPLICATION_FILTER_PROCEDURE RULE SEQUENCE_OBJECT SERVICE_QUEUE SQL_INLINE_TABLE_VALUED_FUNCTION SQL_SCALAR_FUNCTION SQL_STORED_PROCEDURE SQL_TABLE_VALUED_FUNCTION SQL_TRIGGER SYNONYM SYSTEM_TABLE TABLE_TYPE UNIQUE_CONSTRAINT USER_TABLE VIEW
create_date	datetime	Date the object was created.
modify_date	datetime	Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.
is_ms_shipped	bit	Object is created by an internal SQL Server component.
is_published	bit	Object is published.
is_schema_published	bit	Only the schema of the object is published.

Remarks

---

You can apply the OBJECT_ID, OBJECT_NAME, and OBJECTPROPERTY() built-in functions to the objects shown in sys.objects.
There is a version of this view with the same schema, called sys.system_objects, that shows system objects. There is another view called sys.all_objects that shows both system and user objects. All three catalog views have the same structure.
In this version of SQL Server, an extended index, such as an XML index or spatial index, is considered an internal table in sys.objects (type = IT and type_desc = INTERNAL_TABLE). For an extended index:

• name is the internal name of the index table.
• parent_object_id is the object_id of the base table.
• is_ms_shipped, is_published and is_schema_published columns are set to 0.

Permissions

---

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

Examples

---

A. Returning all the objects that have been modified in the last N days

Before you run the following query, replace <database_name> and <n_days> with valid values.

Transact-SQL

Copy

USE <database_name>;
GO
SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - <n_days>
ORDER BY modify_date;
GO

B. Returning the parameters for a specified stored procedure or function

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

Transact-SQL

Copy

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,o.name AS object_name
    ,o.type_desc
    ,p.parameter_id
    ,p.name AS parameter_name
    ,TYPE_NAME(p.user_type_id) AS parameter_type
    ,p.max_length
    ,p.precision
    ,p.scale
    ,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name, o.object_name, p.parameter_id;
GO

C. Returning all the user-defined functions in a database

Before you run the following query, replace <database_name> with a valid database name.

Transact-SQL

Copy

USE <database_name>;
GO
SELECT name AS function_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

D. Returning the owner of each object in a schema.

Before you run the following query, replace all occurrences of <database_name> and <schema_name> with valid names.

Transact-SQL

Copy

USE <database_name>;
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION 
SELECT 'TYPE' AS entity_type
    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
    ,name 
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>' 
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type 
    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
    ,xsc.name 
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
    ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
GO

REPLICATE

Repeats a string value a specified number of times.

Syntax

REPLICATE ( string_expression ,integer_expression )
Using REPLICATE

The following example replicates a 0 character four times in front of a production line code in the AdventureWorks2012 database.

 

USE AdventureWorks2012;
GO
SELECT [Name]
, REPLICATE('0', 4) + [ProductLine] AS 'Line Code'
FROM [Production].[Product]
WHERE [ProductLine] = 'T'
ORDER BY [Name];
GO

Here is the result set.


 



Name                                               Line Code
-------------------------------------------------- ---------
HL Touring Frame - Blue, 46                        0000T 
HL Touring Frame - Blue, 50                        0000T 
HL Touring Frame - Blue, 54                        0000T 
HL Touring Frame - Blue, 60                        0000T 
HL Touring Frame - Yellow, 46                      0000T 
HL Touring Frame - Yellow, 50                      0000T
...

 

Using REPLICATE and DATALENGTH

The following example left pads numbers to a specified length as they are converted from a numeric data type to character or Unicode.


 



USE AdventureWorks2012;
GO
IF EXISTS(SELECT name FROM sys.tables
      WHERE name = 't1')
   DROP TABLE t1;
GO
CREATE TABLE t1 
(
 c1 varchar(3),
 c2 char(3)
);
GO
INSERT INTO t1 VALUES ('2', '2'), ('37', '37'),('597', '597');
GO
SELECT REPLICATE('0', 3 - DATALENGTH(c1)) + c1 AS 'Varchar Column',
       REPLICATE('0', 3 - DATALENGTH(c2)) + c2 AS 'Char Column'
FROM t1;
GO

 

The differences between LEN and DATALENGTH in SQL Server

 

The differences between LEN and DATALENGTH in SQL Server!


LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
DATALENGTH
Returns the number of bytes used to represent any expression.

So what does that mean? It means that the LEN function will first right trim the value and then give you a count of the charaters, the DATALENGTH function on the other hand does not right trim the value and gives you the storage space required for the characters.

Take a look at this example

 

 

declare @v nchar(5)

select @v ='ABC  '

 

 

select len(@v),datalength(@v)

The output for len is 3 while the output for datalength =10. The reason that datalength returns the value 10 is because nvarchar uses 2 bytes to store 1 character by using unicode while varchar is using ascii which requires 1 byte per charaters