|
|
|
|
|
|
|
Question of the Day |
Today's question (by Kendra Little): | |
Fashion | |
What kind of shirts does Steve Jones like to wear? | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Kendra Little) |
SELECT Queries and Type of Locking SELECT queries running under the default “pessimistic” implementation of the read committed isolation level like to use… Answer: Shared locks Explanation: SELECT queries under the default implementation of read committed like to use “shared” locks. “Shared” locks enable multiple read queries to … well, share! If my query takes out a shared lock for a read and your query wants to read it as well, our “shared” locks are compatible with one another and no blocking need occur. However, if my query has a shared lock on a row and Kathi’s runs a query to modify data, that will require an exclusive lock with is incompatible with my shared lock – so her query will need to wait until I am done. Please note that this behavior changes if you or an administrator changes the implementation of read committed in the database by enabling Read Committed Snapshot Isolation (RCSI), which enables more “optimistic” row-versioning based isolation levels. A good resource to learn more about this is Microsoft’s “Transaction Locking and Row Versioning Guide.” |
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 - Development |
Recursive CTE vs UNION ALL in a VIEW - Hi, I'm trying to improve a VIEW which is made of several UNION ALLs, something like this: SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL, (nivdop1.ANT) / sumniv1.SUMANT as ANT, 0 as ANTS, nivdop1.SVIND, nivrv1.nr as RVNR, nivrv1.NAVN as RVNAVN, nivrv1.TYPE, 1 as NIVEAU, 0 as PARENT, nivdop1.LINE, 0 as PLINE from DBDOPSK […] |
How to preserve leading zeros with insert into - Below is the create table script and the insert into statement. Both work fine in SSMS. However, the insert into drops my leading zeros. These are important because I have a query that I want to test out that clearly specifics certain character counts and values. I am hoping to […] |
FUN only, solving a Puzzle. - /* For FUN. I like puzzles, I like T-SQL coding, I like to show off :-), so why not combine these. This project was for FUN only, but I did learn new things. Goal: Build a Sudoku solver with the minimum amount of script. (Minimum number of lines or characters). It does have to solve […] |
SQL Server 2016 - Administration |
Update Statistics is causing DB file to grow in always on envrionment - Hello! When I check what is causing DB files (.mdf) to grow, it is showing as Update Statistics. DB files are auto-growing by 1 GB. In about a month, it has grown by more than 10 GB. Could you please let me know the reason and how to prevent it from this? Thanks in […] |
Looking for recommendation on "cost threshold value" - H All, What is the recommended value for setting sp_configure setting "Cost Threshold For Parallelism" for an OLTP workload instead of default value 5? Regards, Sam |
SQL Server 2016 - Development and T-SQL |
Workaround for executing dynamic sql in function - I'm trying to create a view which dynamically generates text from input parameters. But executing dynamic sql or stored procedure is not allowed in functions. Is there a work around for this? Below is sample script for: 1. Create tables, 2.Create function, 3.Create view, 4. Expected output ----------------TABLES------------------------- IF OBJECT_ID('dbo.emp_info', 'U') IS NOT NULL DROP […] |
Administration - SQL Server 2014 |
Web Synchronisation error:The server returned an invalid or unrecognized respon - Hello everybody we use Web synchronisation in my business. It's very important for us. the error that I cannot resolve is as follows : 2020-02-16 23: 26: 20.057 OLE DB Subscriber 'SRV-PREV': {call sys.sp_MSadd_merge_history90 (?,?, 2020-02-16 23: 26: 20.057 Percent Complete: 0 2020-02-16 23: 26: 20.057 The server returned an invalid or unrecognized response 2020-02-16 23: […] |
SQL 2012 - General |
tempdb shrinking - Hi, Need some quick help here. I am unable to shrink tempdb files. There is lot of available free space. DBCC loginfo output We have an alert and incident opened for this and we have to close it. Other thing is that systems/storage team is not going to give us more space. There are […] |
Convert column values into single row comma separator - Team My requirement is to convert column values into single row comma separator along with single quotes. Pls help ServerName > Column Name Values in a row like below Server1 server2 server3 server44 I need a result like 'Server1,server2,server3,server44' |
SQL Server 2019 - Administration |
New user wants to create test DB, which menu option? - I'm new to SQL Server and using V18.4. I want to create a new database on my local machine so that I can do feasibility testing and development before moving it later. When I go to Connect / Database engine there's no option for creating a new one. What should I do? Thank you for […] |
SQL Server 2019 - Development |
Query to fetch quarter & year from date - Hi, I am having the following query to fetch day & year of a particular date. Need to fetch Quarter & Year also. I tried in a similar fashion. But its not working. Please help on this. DECLARE @minDate_Str NVARCHAR(30) = '01/01/2020' , @maxDate_Str NVARCHAR(30) = '31/12/2020' DECLARE @minDate DATETIME, @maxDate DATETIME SET @minDate = […] |
Running VBS Script through SQL Agent Job - Hello All, Glad to have found this board. I have a VB Script that is exporting a query to an excel file. It works perfectly when I run it from the command line. When I create a SQL Server Agent Job to execute the same script, it fails. The type is 'Operating System (CmdExec)' and […] |
T-SQL (SS2K8) |
How to extract the right country info from the field? - This is very painful to me, I have a list of location and need to determine as much/close as possible(no need to be 100% accurate) for its country info, sample as below and two files attached here: one has 100 records, the other is the full list. San Diego, CA, United States Chicago, IL, United […] |
Reporting Services |
SSRS report rows not summing correctly - Hi, I have produced a report in report builder. During the month the report runs ok, however when the report crosses into a new month e.g. January to February, it seems to split on the number of referrals whereas it should be a total number. Within my SQL code it shows the split with the […] |
General |
SELECT different rows on multiple threads - I have an application which will be reading from a single table, in order to speed up the processing of each row it will run multiple threads, as soon as each thread finishes it will read again from the same table. My first approach is to select the rows assigned to an index of each […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |