--- schema_version: "1" id: 0f8c357a19eed4b87408cd2b32f07b0fdd335daac1a990ae761e91050a7cb494 title: SQL Reference collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/mktg/264-0-0/mc_as_sql_reference.md canonical_url: https://help.salesforce.com/s/articleView?id=mktg.mc_as_sql_reference.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: e8170e094976cc187e2b5902c60596e60057767e38aac8f5ddf3fbfa72b09d61 description: Get SQL query information for Automation Studio’s SQL Query activity at a glance. products: - Marketing - Marketing Cloud Engagement release: "264" --- # SQL Reference Get SQL query information for Automation Studio’s SQL Query activity at a glance. ## Considerations SQL support for the SQL Query Activity is based on, but doesn’t precisely correspond to, SQL Server 2016 capabilities. - To ensure that queries are executed without blocking from other SQL updates, query activities use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is equal to WITH (NOLOCK). Using the NOLOCK query hint is unnecessary. - Only SELECT statements to data extension or data views in an account or in the parent account - Nested Queries - UNION - JOIN - GROUP BY - Conditional Statements - IF Constructs - CASE Statements - Functions - Most functions, including MIN, MAX, and more are supported - CAST and CONVERT - Unsupported elements - Variables - Cursors - User-Defined Functions - Transaction and Locking - GOTO - PRINT - Any `sp_*` stored procedure - EXEC - Temporary Tables and Common Table Expressions - TEXT and IMAGE Functions - Open-ended comment designations such as '--' to comment out a line Split data within a column into two rows using the Char function and its ASCII value. Use an ASCII approved character as a delimiter. To query unicode, it isn’t necessary to append an N to the search string. ## Performance Considerations Queries have a 30-minute timeout. If a query is timing out, it’s possible to make the query perform faster with indexes applied. Queries that include both a join and a `Select *` statement aren’t permitted. Instead, write a statement that specifies each column name, even when there are multiple columns. ## Known Issues - Data Types - Decimal: If a data extension contains a decimal field and that field is used in a query, the output isn’t a decimal output - Data Views and Enterprise 2.0 Business Units - Only data extensions are accessible for queries written at the Enterprise 2.0 business unit - Data Extensions - Don’t set your data extension primary key field for more than 1700 bytes. ## Querying Data Extensions - In an enterprise environment, add the ENT."" prefix to get access to data extension in the enterprise parent account. - Access a shared data extension in an Enterprise 2.0 account using the ENT. prefix. - Use the data extension Name in SQL queries, not the External Key. ## Querying Subscriber Data ## Using the \_Subscriber Data View ``` SELECT SubscriberID, EmailAddress, Status FROM _subscribers ``` ## Using the \_EnterpriseAttribute Data View ``` SELECT _SubsriberID FROM _EnterpriseAttribute ``` ![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-mktg-mc_journeys_and_automations-264-0-0-production-enus/e08cdd71-c1c1-4ae5-a5e9-8a6dad583ad4/images/icon_note.png) Note This query can be used in Enterprise 2.0 edition accounts only. Core, Advanced, or Enterprise don’t support querying subscriber attributes. ## Combining the \_EnterpriseAttribute Data View and the \_Subscriber Data View ``` SELECT b.[first name], b.[last name], b.[age], a.[Status] as 'Subscription Status', a.[EmailAddress] as 'Email' FROM _subscribers a INNER JOIN _EnterpriseAttribute b ON a.subscriberID = b._subscriberID ``` This example assumes the \[first name\],\[last name\], and \[age\] attributes exist in the account. ## Sample Queries ## GetaSend List Based on Aggregate Row Count Use Case: Generating a Sendable list from a detailed data set ``` SELECT visitorid FROM [cart abandonment segment] GROUP BY visitorid HAVING count(visitorid) > 1 ``` ## Random Sample of 33 Percent Exclusion Use Case: A/B split testing and general segmentation ``` SELECT TOP 33 PERCENT offerid,score,name,description,url FROM Offers inner join Offer_Ext1 a on a.offerid <> offers.offerid order by newid() ``` ## Random Sample of 33 Percent Use Case: A/B split testing and general segmentation ``` SELECT TOP 33 PERCENT offerid,score,name,description,url FROM Offers ORDER BY newid() ``` ## Random Sample of 100 Use Case: A/B split testing and general segmentation ``` SELECT TOP 100 offerid,score,name,description,url FROM Offers ORDER BY newid() ``` ## Unique Values for a Data Set Use Case: Testing ``` SELECT DISTINCT '109406145' as linkage_id, '109406145' as ticket_id, '28480048' as family_id, '73334' as merchant_id, '25.32' as Original_Transaction_Amt, transaction_type_cd, Max(TRANSACTION_DATE) as Transaction_Date, TRANSACTION_STATUS, MERCHANT_TYPE, '0' as Group_Points, '.00' as Group_Comp, '.00' as Group_Credit, '.00' as Group_Dues, '0' as Group_Miles, '.00' as Group_BonusComp, '102' as Group_BonusMiles, '0' as Group_BonusPoints, '.00' as Group_BonusCrdt, '.00' as Total_Comp_Earned, '.00' as Total_Credit_Earned, '10' as Total_Miles_Earned, '103' as Total_Points_Earned, MERCHANT_CATEGORY, RA_SEQUENCE, 'FFAK' as fam_campaign_cd FROM Subscriber_Activity GROUP BY transaction_type_cd, TRANSACTION_STATUS, MERCHANT_TYPE, RA_SEQUENCE, MERCHANT_CATEGORY ``` ## Geo-Targeting Prerequisites: Zip Code Data Extension. Search for current ZIP code data on the web. Use Case: Find all cities or zips, and therefore subscribers, within 15 kilometers of a ZIP code. -- 6378.137 earth circumference ``` SELECT city, zip, ROUND(6378.137 * ACOS( CASE WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) > 1 THEN 1 WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) < -1 THEN -1 ELSE (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) END),0) AS Distance FROM [ZipCode] AS geo WHERE ROUND(6378.137 * ACOS( CASE WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) > 1 THEN 1 WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) < -1 THEN -1 ELSE (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) END),0) <= 15 ``` ## Pulling Tracking Information for a Triggered Send Use Case: Viewing available tracking information regarding a triggered send ``` SELECT SubscriberID, EventDate FROM _Open WHERE TriggeredSendCustomerKey = 'External Key of Triggered Send' ``` ## Retrieve Deactivated Mobile Numbers Use Case: To ensure that the data pull matches the [Carrier Deactivation SMS Summary](https://help.salesforce.com/s/articleView?id=mktg.mc_re_carrier_deactivation_sms_summary_report.htm&type=5&language=en_US), retrieve the deactivated (opted out) mobile numbers. `select LogDate, SubscriberKey, MobileNumber, OptOutStatusID, OptOutMethodID, OptOutDate, OptInStatusID, OptInMethodID, OptInDate, CreatedDate, ModifiedDate FROM _SMSSubscriptionLog WHERE OptOutMethodID = '20' AND OptOutStatusID = '1' AND CreatedDate >= getdate()-30` #### See Also - [ASCII Characters](https://help.salesforce.com/s/articleView?id=mktg.mc_es_ascii_characters.htm&type=5&language=en_US) - [Data View: SMSSubscriptionLog](https://help.salesforce.com/s/articleView?id=mktg.mc_as_data_view_sms_subscription_log.htm&language=en_US)