|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Quickly Dropping Columns | |
I have a table that contains 1 billion rows. I want to drop a column that our application is not using. Under which circumstances can I do this as a metadata operation? | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Carlo Romagnano) |
Create temporary table on fly I run the following code: select col1 = 1/0.000 into #a WHERE 0=0 What happens?
Answer: Temp table #a is created, Divide by zero error encountered., Column "col1" is numeric(10,6), Temp table #a is empty beacuse of error Explanation: According to the Logical Processing Order of the SELECT statement, the order is:
This means that the table will be created and the WHERE clause evaluated before the column list is computed. |
Featured Script |
habib from SQLServerCentral Functions to list the number of work days in period , determine if a given date is a work day, add work days to a given date. |
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 |
Stored Procedure - I have a table: Named HighScores "INSERT HighScores VALUES ('Bob', 2500, '2 Jan 2013 13:13'), ('Jon', 1500, '2 Jan 2013 13:15'), ('Amy', 3500, '2 Jan 2013 13:18')" I want to create a stored procedure that would: The score must show the TOP 5 or so people. Show your position on the high score board Show […] |
After 2014 to 2017 Upgrade, table unused space growing massively. - I have a database that we recently upgraded from SQL 2014 to SQL 2017. The application against the data are working fine, but we started to notice database file growth. After investigating, many tables are exhibiting a behavior where the unused_space is growing massively compared to the time before the upgrade, and I cannot figure […] |
SQL 2017 Automatic Tuning (issue?) - Hello, This past summer we migrated to OS 2019 and SQL 2017. I have the automatic tuning feature enabled for the DB in question and all has been fantastic until this morning. Each morning index maintenance (Ola Hallengren) runs with rebuilding statistics option. Once load began, (~ 5,000 users) massive blocking began (LCK_M_X) slowing down […] |
SQL Server 2017 - Development |
Using a CTE in a subquery - I have been tasked with identifying a set of users in one of our databases. The following code should be reasonably self-explanatory: --There are 1,360 Users SELECT COUNT(*) AS NumUsers FROM Users; -- Use a CTE to identify all Users who are linked to Actions - there are 82 of these ;WITH ActionCentreUsers AS ( […] |
Getting a result set from temp tables - So I have 5 queries . Each query fills a different temp table. For each table the first two columns will be named (GovState, FirmID) . A third column will also exist. It will be named differently depending on the temp table. ("CountOfPolicies", "CountOfQuotes", etc) This column will normally show a count of something. I […] |
SQL Server 2016 - Administration |
AlwaysOn 2 Nodes Next to Zero Downtime with SQL Edition Upgrade - I'll attempt to be as specific as I can with this question. I've been tasked with setting up AG's on our SQL Servers. We have a customer who provides translation services for medical facilities so downtime is hard to come by. I need to upgrade our SQL Server version from Standard Edition to Enterprise so […] |
question on locking ? - Hi All, Heard about readers can also block writers. Can anyone provide a demo example to prove this? I was under the impression that only writers can block readers. Secondly, will INSERT block a SELECT ? Thanks, Sam |
SSIS catalog deployment from source server to target - powershell - I am looking for a good solution for automating the deployment of SSIS packages through environments. Right now the deployment is very manual but I am hoping to find a powershell script that will copy packages from source to target servers (including all configurations and environment variables). Does anyone have any suggestions? I have found […] |
SQL Server 2016 - Development and T-SQL |
how to call same logic using different parameter in if then else stored procedur - ALTER PROCEDURE [dbo].[employee] @isemployee int AS IF (@isemployee = 0) BEGIN DECLARE @Email Varchar (100) DECLARE @Name Varchar (100) SELECT @Email = text from dbo.emailtable WHERE email = 'XXX' SELECT @Name = name from dbo.nametag where refer = 'HJJ' SELECT @Name as NAME, @Email as EMAIL, '' as Test1, '' as Test2 END ELSE BEGIN […] |
SYMMETRIC KEY Incorrect syntax near 'TRIPLE_DES' - Hi All I'm using SQL2016 I'm having an issue with a piece of code generated in SQL2008 Sample code below This returns Incorrect syntax near 'TRIPLE_DES'. So, looking around, it appears as though TRIPLE_DES has been deprecated Is there an obvious replacement for TRIPLE_DES here? CREATE SYMMETRIC KEY [My_SYMMETRIC_Key] AUTHORIZATION [dbo] WITH ALGORITHM = […] |
SQL 2012 - General |
DB digram, Standard reports to use. - Hi, Please let me know.. DB Diagram: SSMS is not helping with so many tables and huge DB. Please advise if there are other ways which can be helpful to generate or create DB diagram. The goal is to understanding the tb lvl relationship and what is the MASTER table and get better understanding. 2. […] |
XML Data singleton value not fixed???? - Hi Hope to explain this correctly I have some XML data that can vary on singleton value So in the example below I want to list the "Company" along with the "Service" but [1] may be any value('Mental Health', Substance Abuse etc..)If the User entered Mental Health first it would be [1] if they enetered […] |
Execution Plans - Hi, How do you share execution plans. Save the plan and share the .sqlformat file as take a screen shot what is the best practice. The only reason to do this to optimize the query. Thanks! |
Security (SS2K8) |
Remote Server calls failing since recent Windows update - Has anyone else had issues with linked servers, remote calls, double-hops, etc, since the July, August, or September 2019 Windows updates? We know the cause is in there somewhere, as we have re-imaged a PC to pre-July updates and everything works. As soon as the updates are applied, ka-blooey. We can't control the updates (and […] |
Anything that is NOT about SQL! |
SQL Licencing and a move to Opensource RDBMS' - Good day Quite a number of our larger clients are contracting us to assist them with migrating from SQL Server to other opensource RDBMS'. Their motivation is simple: Microsoft licencing is becoming way too expensive and is corroding their bottom line. This is a very similar to what happened a few years ago when companies […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |