|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
ALTER Transactions | |
I want to ensure that if a user runs an ALTER TABLE statement, that a new BEGIN TRANSACTION is always started. What can I change in the settings for all users to ensure this is the case. In other words, I want to require each admin to execute a COMMIT after running their ALTER TABLE. Note: We don't have any ODBC or OLEDB connection libraries in use. | |
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) |
The Ranking Function I have a table with various products and associated metadata. I run this query: SELECT r.ItemName , r.ItemType , RANK () OVER (ORDER BY r.Price desc) AS Ranking FROM dbo.Ranking AS r; How many products and which types are ranked with a "2" for the Ranking column in the result set? The DDL and DML are below: CREATE TABLE dbo.Ranking ( ItemID INT NOT NULL , ItemName VARCHAR(10) , ItemType VARCHAR(20) , Qty INT , Price int ) GO INSERT dbo.Ranking (ItemID, ItemName, ItemType, Qty, Price) VALUES (1, 'Dell', 'Laptop', 2, 500) , (2, 'HP', 'Laptop', 3, 800) , (3, 'Lenovo', 'Laptop', 4, 900) , (4, 'Samsung', 'Mobile', 5, 900) , (5, 'Apple', 'Mobile', 6, 1200) , (6, 'Apple', 'Watch', 2, 400) , (7, 'Samsung', 'Watch', 3, 500) , (8, 'Garmin', 'Watch', 4, 200) , (9, 'Rolex', 'Watch', 5, 1200) GO Answer: Zero Explanation: No items are ranked 2. There are 2 number 1s and 2 number 3s. The ranking function outputs based on the value used in the aggregate, but if there are ties, then the ties are ranked at the higher level and the number of ties determines how many places are skipped. Ref: RANK() - https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver1 |
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 |
Inaccessible instances - 1 default and 5 named instances on a server with 6 cpu's and 120 gb memory SQL Server audit made the named instances inaccessible.CPU was dumping memory continously.And only after disabling the audit specification and audits were the instances and databases available.It was causing timeout errors prior to disabling.Everything got back to normal only after […] |
SQL Server 2016 Standard - Hey, I have Windows 10 running SQL Server Express 64-bit and I purchased SQL Server 2016 Standard (genuine) key. I want to upgrade and I'm having difficulty figuring out how to do so. Could you direct me to the best how to or explain the steps? Thank you. |
SQL Server 2016 - Development and T-SQL |
Error converting data type varchar to bigint. - Hello Community, I'm getting the following SQL error in Azure Synapse Error converting data type varchar to bigint. ;WITH CTE1 AS ( SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[account] ),CTE2 AS ( SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata] ) SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode ,CASE WHEN C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking ,CASE WHEN C1.ts_ukrow<>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum The problem is, I don't know why Azure Synapse is trying to convert the Data Type to BIGINT. Also, I don't know how to […] |
Error converting data type varchar to bigint. - Hello Community, I'm getting the following SQL error in Azure Synapse Error converting data type varchar to bigint. ;WITH CTE1 AS ( SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[account] ),CTE2 AS ( SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata] ) SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode ,CASE WHEN C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking ,CASE WHEN C1.ts_ukrow<>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum The problem is, I don't know why Azure Synapse is trying to convert the Data Type to BIGINT. Also, I don't know how to […] |
SQL replace with escape characters - I want to do UPDATE x SET y = (REPLACE(y,'a=[123]', a=999) FROM x So I'm replacing a=(any 3 integers) with a=999. I'm so stuck on this. Any help, gratefully received. |
Administration - SQL Server 2014 |
Incorrect syntax near the keyword end - Create PROC [dbo].[SP_Planning] AS BEGIN Truncate TABLE Planning Select * into Planning from (select T0.ID,T1.SID,[dbo].[Planning](T1.Id,T0.SId) as Status from BDetails T0 cross join SDetails T1) END |
SQL 2012 - General |
Question about Certification Exam 461 Querying MS SQL Server 2012 - I'm just curious but I'm in a SQL course at the moment and my head is swimming in all the different functions and operators. It's a lot to take in and since I'm in this course to get certified I'm worried about the dizzying amount of knowledge being presented to me. I'm just curious and […] |
SQL Server 2019 - Administration |
Distributed Availability Groups Connectivity post failover - Hi all, So we are looking at our DR solution and DAG. The question is coming how the applications connect post DAG failover and the timings around the return to operation. So my question is...Post failover what the best way to get application to go to the correct (now DR) Availability group? A few options […] |
Linked Tables Excel - Can anyone assist in resolving this error (Attached Error) I did have the Linked Server working on my test Excel Spreadsheet moving to the live spreadsheet it cannot connect and after deleting the current WORKING linked server i cannot recreate the Link. Its not the 64bit version issue as i have had this working the […] |
SQL Server 2019 - Development |
Populate Column Based on Distinct Values - Hello! I am trying to populate a column based on the number of distinct values in a different column. If there is only one distinct department, Export Department should be populated with *, if there is more than one distinct department, Export Department should be populated with the same value in Department. Any help would […] |
IF ELSEIF Else issue with mysql - In the below select which populates a table my boss wants to add case or if to the select for field delay. I was doing it in three steps. Now i have to update based on if a field in one table is greater than 0 it is yes or if field in another table […] |
Azure Data Factory |
In Azure Data Factory, how does the Event trigger really work? - I'm planning a solution where I have a upload to Container triggered a pipeline which copies these files to SQL tables, and then deletes the files. I would prefer to have this pipeline run from a event trigger, since in case the data is not in place for whatever reason I can still accomodate late […] |
General |
Clause in SQL Natural Join - SELECT * FROM TAB1 NATURAL JOIN TAB2 ON(CONDITION); This query gives an error. SELECT * FROM TAB1 NATURAL JOIN TAB2 WHERE(CONDITION); This question was executed effectively. So Why the WHERE clause is used instead of ON clause in SQL Natural Join? |
IQ Vs Stratascratch - Passed the initial screening and now have a SQL interview. Dabbled with SQL for a few months now but there are a lot of resources that I can use to prepare. Should I choose Stratascratch or Interview Query? I have done a couple of questions from both and it seems like the questions from IQ […] |
Integration Services |
How to Replace LF to CRLF in file source connection - hi, I have a issue where ,the flat text file (fixed width) used to come with CRLF and now it is coming as LF and it is failing. How can i fix it, Is it possible? Please suggest. Thanks in Advance, Komal. |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |