|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
A Reuseable Window | |
I have this sample code from the MS Docs:SELECT ROW_NUMBER () OVER win AS "Row Number" , SUM (s.TerritoryID) OVER win AS TerritoryCount , p.LastName , s.SalesYTD , a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0 WINDOW win AS(PARTITION BY PostalCode ORDER BY SalesYTD DESC) ORDER BY PostalCode; GOWhen I run this on a SQL Server 2022 database, what happens? | |
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) |
Backing Up a Restoring Database I have a database involved in log shipping. I have this database restored with the NORECOVERY option as we continue to add log restores to this. Can I back up this database with a BACKUP DATABASE command while it's in the restoring state? Answer: No Explanation: You cannot back up a database in the restoring state. Ref: BACKUP - https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16#general-remarks |
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 2017 - Administration |
cost threshold for parallelism value to be set - Hi Everyone, Why and when should be set 'cost threshold for parallelism'? We are on SQL 2017 Enterprise Edition. What should be the value set for cost threshold for parallelism instead of default value 5 and why ? what benefit do we get if we set a high value and what should be the starting […] |
Identifying the process that is backing the databases to virtual device - We have about 20 SQL Servers. We use Net backup or Native backup for SQL Server database backups. When I query the msdb to find the backups, 2 servers have backup method different. In addition to the native backup some other process is backing up the databases to virtual device. I am struggling to identify […] |
SQL Migration - Identifying Connections - Best approach - I have one SQL Database server which is old version and planning to migrate to SQL2017. We have about 10 databases on this server. I am seeing connections to only one database from sp_who2. Looks the connections to other databases are closing immediately. Question 1: Other than running SQL Profiler, what are the options available […] |
SQL Server 2016 - Administration |
Does multi-subnet with standardeditionofsqlserver supports DB Mirror Or AG group - Does multi subnet failover with standard edition of sqlserver supports DB Mirror or AG groups 1. can db mirror configured in different multi subnet with standard edition 2. can db always on configured in different multi subnet with standard edition |
SQL Server 2016 - Development and T-SQL |
get week number of month - I want to query a table that has a datetime column ([CreateDateTime]) and get the week number for that specific month. 2023-03-01 09:30:00 2023-03-12 11:15:22 2023-03-22 14:22:18 2023-04-02 15:12:30 I want to get these results from the above dates: 1 3 4 1 |
CXConsumer wait type issue - hello, my query is select * into #TmpA from tblA (nolock). tblA contains 116000000 records. This query sometime takes 6 minutes and some time takes less 2 minutes. I observed CXConsumer wait types mostly and sometimes CXPacket. On observing the execution plan, parallel execution is taking place. kindly guide what can be done in this […] |
SQL Server 2019 - Administration |
High tempdb usage alternatives - Hi We have an stored procedure which runs frequently and it contain logic in which data are first dumped in temproray table and then moved to physical table .due to large data and frequent run of sp .tempdb database get filled up quickly .We cannot take server restart everytime so whats alternative to temp variables […] |
high latency in sql server transactional replication - Hi I am new to replication and we have database of size around 2TB and its has been set up for replication .there are 4 subscriber to it. Tables(articles) of database has been divided into 14 publishers. Issue sometime we face high latency on server with lakhs of undistributed command so what should i check. […] |
upgrade evalution editon reporting services to Stanadard - Hi All, I installed reporting services evaluation edition for testing purpose, but now 180 day trial period is expired and I am unable to generate reports. Is it possible to upgrade it with SQL server 2019 standard edition, without disturbing the existing configuration. I Installed SQL server 2019 database engine on same machine, but while […] |
Delegated rights to Enable/Disable TSQL Jobs (Specific Jobs) - All, In attempts thinking that I am smarter than your average bear, I attempted to do the right thing and write a wrapper for users to be able to enable/disable certain jobs. Trying to avoid the pitfalls of allowing users access to modify everything in a job and avoid code elevation, I wrote this test […] |
SQL Server 2019 - Development |
Convert columns result into rows with comma and single quotes - Hi All, Hi All, I want to convert columns result into rows with comma. Ex: Result should be - 'SQL1','ABCD100','XYZ123' #code: create table #tbl_db (name varchar(100)) insert into #tbl_db values ('SQL1') insert into #tbl_db values ('ABCD100') insert into #tbl_db values ('XYZ123') select * from #tbl_db Result needed: 'SQL1','ABCD100','XYZ123' |
SQL Azure - Administration |
post steps for Table PARTIONING - Any one did Table partitioning in Prod, please list steps here. we have a transaction DB with 4 Existing Large 60 M row Tables, WITHOUT partition storing Transaction Data from 2019 to 2023. They asked me to come up with solution to partition this data , on azure MI ( assume only one FG is […] |
Strategies and Ideas |
SSIS vs TSQL sp - Hey All, I'm working on a new DW design and I'm learning DW on the fly right now, so I don't have much experience here. Anyway, we are currently in the planning stages. We are considering not using SSIS, and just writing our own SP to handle all DML logic for the EDW. I'm just […] |
Employers and Employees |
Master Data - Relational or Dimensional modeling? - Dears, In my company, we have certain master data domains like customer; Vendor; Product; Materials; Bill of Materials. So far we have been doing our modeling using relational modeling in the cloud. It consists of entire tables that we push from SAP ECC and SAP MDG like MARA; MVKT, etc... then we have views on […] |
SQL Server 2022 - Development |
Hello i am new to Sql and idk why i get this error and also not getting results. - CREATE DATABASE dbsubjects; CREATE TABLE tblstudent( ID INTEGER PRIMARY KEY, TheName Varchar (100), TheAddress VarChar (100), Marks Real, Comments Varchar (100)); insert into tblstudent values (1001, 'Imon', 'FRI', 90,'Great'); Msg 213, Level 16, State 1, Line 9 Column name or number of supplied values does not match table definition. |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |