|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Can I create this view? | |
Is this view definition valid, why or why not? CREATE VIEW MyView AS WITH cteData (lowerbound, upperbound, letter, pwd) AS ( SELECT SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) AS lowerbound , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) AS upperbound , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1) , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50) FROM dbo.Day2 AS d ) , cteSolution (occ, valid) AS ( SELECT LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) AS occ , CASE WHEN LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) >= d.lowerbound AND LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) <= d.upperbound THEN 1 ELSE 0 END AS valid FROM cteData AS d) SELECT COUNT(*) totalcount FROM cteSolution WHERE valid = 1; GO | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
Reset Query Plan Stats If I want to only reset the query plan stats for a specific plan, which stored procedure would I use? Answer: sp_query_store_reset_exec_stats Explanation: To reset query stats, sp_query_store_reset_exec_stats is used. Flushing the db and removing the plan do more than just reset stats. Ref: sp_query_store_reset_exec_stats - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-reset-exec-stats-transact-sql?view=sql-server-ver15 |
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2016 - Administration |
Updating SQL Server 2016 - We are currently running SQL Server version: Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) Can we upgrade straight to CU15 for CU12? Or do we have to do each version in between? […] |
Change name of a particular step in an existing SQL Agent Job - What is the easiest way to change name of a particular step in an Agent Job? SSMS does not seem to allow to do that. |
Could not find a login matching... when access granted via AD group - Hello experts, A client just reported the following puzzling error. There is a user ABC\user1 that was added to the AD group ABC\foo_db_readers. The group ABC\foo_db_readers was added to the user-defined database role db_foo_datareader, which has read access to foo_db. The client's goal with this setup is to avoid having to create the login ABC\user1 […] |
always on sql server - Hi Guys, I have a question to configure always on sql server same subnet vs multi subnet. It is three node cluster, two in same DC HA (node 1, node 2) and other in DR (Node 3). It needs a multi subnet, I am thinking to configure node1 & 2 as well into different subnet, […] |
SQL Server 2016 - Development and T-SQL |
Converting MONEY - Hi, running this declare @code VARCHAR( 50 ) = '748.019876'; select [code1] = @code, [code2] = CONVERT( MONEY, @code ), [code3] = CONVERT( VARCHAR( 10 ), CONVERT( MONEY, @code ) ) returns --code1 code2 code3 --748.019876 748.0199 […] |
Extracting SELECT statement from queries. - Hi All, I have a need to replace, in various views, the SELECT statement that produces the results (SELECT --> SELECT TOP 10). These queries could be in any of the following formats: --1) single select SELECT * FROM test --2) CTE with single select WITH test AS (SELECT 1 a), test2 AS (SELECT 2 […] |
Administration - SQL Server 2014 |
Full Text Search and Enablement at DB Level - Day One. - I have 2 DBs - they are 'vendor' DBs - part of an application. They both originated from a single source DB - so were identical to start with. One DB has been upgraded and has new functionality from the vendor that utilises full text search - the other hasn't. The SQL instance has Full […] |
Development - SQL Server 2014 |
While loop - CREATE Table TABLEA(N varchar(50),Q int,R int,Dt1 datetime,Dt2 datetime) DECLARE @I int = 1 DECLARE @R int = 2 DECLARE @Q int = 3 DECLARE @Q1 int SET @Q1 =@Q DECLARE @N varchar(100) SET @N = 'NameA' WHILE (@I <= @R+1) BEGIN WHILE (@I <= @Q) BEGIN SELECT @N as Name,@I,@R,@Q SET @Q = @Q - […] |
SQL 2012 - General |
How to get parts that not have same features count on table settings by code typ - How to get parts that not have same features count on table settings by codetypeid and PLID ? I work on sql server 2012 . if face issue I can't get parts that not have all features on table settings so I will count distinct features from table settings and compare it with count features […] |
How to copy Excel file from Office 365 online to local drive in SSIS? - Can anybody help me with problem how to copy Excel file from Office 365 SharePoint online to local drive in SSIS? I never worked with SharePoint before, what is the best practices? Thanks, Aleks |
Using cast inside sum or outside ? - I work on sql server 2012 I face issue using cast first or sum ? cast (sum(a) as decimal(18,2) or sum(cast (a as decimal(18,2))) which is correct and more speed a is integer need to convert to decimal so using cast inside sum or outside |
SQL Server 2019 - Development |
Error: Msg 105070, The ODBC data source name was no specified or the driver is m - Hello everybody, I am trying to use external table with PolyBase for import data from Oracle to Sql Server 2019. I have an issue about creating an external table. I created external data source OracleExternalData. CREATE EXTERNAL DATA SOURCE OracleExternalData WITH ( LOCATION = 'oracle://145.145.145.145:1521', CONNECTION_OPTION = 'DSN=CData Oracle Sys' CREDENTIAL = OracleProxyAccount ) ; […] |
Azure Data Factory |
How to export from sql server table to multiple csv files in Azure Data Factory - I have a clients table in sql server that containes client name and city . In Azure Data Factory , how can I export this table to multiple csv files that each file will contain only a list of clients from the same city , which will be the name of the file |
Strategies and Ideas |
Filtering Customer Specific Data - Hi all! Your help would be appreciated. We have a data warehouse that is a single tenant design with multiple reports and widgets that query the data The underlying structure of the DW is modelling invoice data - so essentially header records and line items The widgets and reports show pretty much what you would […] |
Article Discussions by Author |
Download SQL Patch Info - Comments posted to this topic are about the item Download SQL Patch Info |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |