Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Filter by
Sorted by
Tagged with
-2
votes
2answers
2k views

SQL Server build

We are building a new server at work and I'm hoping to get ideas of how we should build it out for optimum performance and back-up capability. We have 6 drives and we need to have an OS (Windows ...
-2
votes
2answers
122 views

SQL server migration - Server object listing/scripting tool [closed]

We need to quickly migrate multiple SQL Servers. We need a process to script out all server-level objects, e.g. linked servers, logins, jobs, server roles. We're looking for a tool to script out ...
-2
votes
1answer
181 views

What is the purpose of WITH options in Sql Constraints? [closed]

What is the purpose of WITH options (STATISTICS_NORECOMPUTE,PAD_INDEX, ALLOW_ROW_LOCKS,ALLOW_PAGE_LOCKS)? I have the following constraint being applied to one of my tables, but I don't know what this ...
-2
votes
2answers
195 views

Explicit insert or Default constraint

Is it better to insert a value all the time for a column (create time, update time) or to put a default constraint to now(). How bad is default constraint when compared to explicit insert. I mean is ...
-2
votes
2answers
106 views

Can someone please explain to me what “literal values” are?

I am new to SQL Server, and would like to know what "literal values" means. It was in the context of this sentence: Notice that all you’ve done is declare three variables for the literal values, ...
-2
votes
2answers
1k views

Split URL string into columns

Given the string: URL:'ID=1!&user=xxx&depart=21&companyId=43&workOrder=67' I need to get the result: Sno ID user depart companyId workOrder 1 1! XXX 21 43 67 ...
-2
votes
1answer
394 views

Cannot update nor insert data to datetime field [closed]

I was trying to insert data through one web application I am developing and I could not insert data, while I was troubleshooting what the problem is, I saw that I can not neither edit datetime field ...
-2
votes
1answer
4k views

How can I get my LEFT OUTER JOIN to work in SQL Server 2008?

I trying to create a table with this school data in it? Year Month Days Referrals 2012 1 16 10 2012 2 20 7 2012 3 15 11 Year, Month and Count of Days from one ...
-2
votes
2answers
94 views

Need to encrypt the database files and backup files [closed]

Our requirement is that the data at rest is to be encrypted. MDF/LDF files to be encrypted, backup files to be encrypted. I found 3 options. We use Sql Server 2016 (SP1) Always encrypted AppScan EFS (...
-2
votes
3answers
66 views

admin: prefix before the servername while connecting to DB Engine Query in MS SQL Server

I am using SSMS to connect to DB Engine Query for DAC. I've seen some video tutorials where people use 'admin:' prefix before server name while connecting When trying to connect with that prefix I ...
-2
votes
2answers
956 views

How to query system specs (cpu, memory, data disks, free space) using t-sql [closed]

Is there an ability to query below system specifications using t-sql ? 1. cpu (show number of processors / cores / frequency) 2. memory (show total amount of RAM on a machine; show allocated RAM; ...
-2
votes
2answers
341 views

Problems with having SQL Server Identity Column in the Cloud

Our company is utilizing Identity(1,1) clustered primary index in SQL Server 2016 Enterprise, and will eventually deploy into the Cloud (Amazon AWS, Google Cloud). Is there any issues utilizing ...
-2
votes
2answers
2k views

Return First Non Zero Value

My desired result set is Red Purple 1 5 all on one row. My query below returns 4 rows. What would be the syntax to only return 1 row? DECLARE @BlueBonnet TABLE ( randarfield FLOAT ,...
-2
votes
1answer
8k views

Importing delimited files into SQL server [closed]

I am trying to import a large file into SQL server that is | delimited. I know basically nothing about the data, I just want to get it imported. When I go to Database->tasks->import I use the ...
-2
votes
2answers
5k views

Conversion failed when converting the varchar value to data type int [closed]

declare @targetnumber int declare @numberfield varchar(20) = 'stepnum' declare @sourcetable varchar(20) = 'tblsteps' declare @idfield varchar(20) = 'stepid' declare @recid int = 3 set @...
-2
votes
1answer
411 views

Type clash on update of always encrypted varchar in ssms 2017

I am using SSMS 17.6 and I am trying out the Always Encrypted feature. I was able to update/insert/search a datetime field by declaring a parameter and then using the parameter, e.g.: DECLARE @...
-2
votes
3answers
163 views

Disadvantages of converting column with 2 billion data from varchar to int [closed]

I want to convert a column from varchar(50) to bigint or int. What are the disadvantages when tables has 2 billion data in SQL Server 2012? Length of data is 11 and data has only numbers. They were ...
-2
votes
1answer
113 views

Script out spaceused stored procedure to edit it

Is it possible to script out the sp_spaceused stored procedure so I can edit it to include log space information and sort of create my own custom stored procedure? I would like to put the results in ...
-2
votes
2answers
53 views

because of Date format im not able to get few records [closed]

For example declare @string varchar(30) = 'dec. 24, 2015' select substring(@string, 10, 4) declare @output date select @output = case when substring(@string, 1, 3) = 'Dec' ...
-2
votes
1answer
58 views

Business Date and Route Sequence

+-------------------------------------------------------------------+ |Route |Sequence| Planned Time | Business Date | +------------------------------------------------------------...
-2
votes
1answer
74 views

Backup SQL Server database to the cloud or network folder [closed]

I can take a SQL Server backup to a local disk without any problem, but I now need to backup to a cloud disk or network folder that I can access. What are the best practices to do this?
-2
votes
1answer
232 views

Use second hard disk for database if first one is full

I installed Standard Edition on a server with a 500 GB drive. Now the system is giving me error message of disk space is not available, so I added a new hard disk. How can I tell SQL Server to start ...
-2
votes
2answers
49 views

First query waits for loading the whole table in memory

I'm testing SQL Server 2017 performance. I've create a table like the following one: CREATE TABLE [dbo].[AssessmentResponses]( [ResponseID] [int] IDENTITY(1,1) NOT NULL, [AssessmentID] [int] ...
-2
votes
2answers
43 views

Does each node of SQL traditional cluster have separate master database

In SQL server traditional cluster, does each node of cluster has their our own master database? I am sure physically they don't...but logically might be?? Also as we know that the method of applying ...
-2
votes
1answer
42 views

SQL Server 2012 Enforce Password Policy [closed]

If i would like to change the password of my SQL login, How much time i can't use with the same password?
-2
votes
2answers
55 views

How to convert a multi-row result set into a single-row text separated by commas

I'm using this query select Name, valueType from mytable where ID = 1 and getting this table: But I need to covert the result set into something like: 'idGasto int, noTicket string, fechaFact ...
-2
votes
1answer
1k views

Varchar Overflowed an Int Column [closed]

I am having an error with the following query: SELECT scp.phone, scp.extension, scf.fax, sce.email FROM supplier_contacts AS sc LEFT OUTER JOIN supplier_contact_phone AS scp ON sc....
-2
votes
1answer
90 views

Does statistics matter? [closed]

Colleagues, based on your experience, what is the maximum page count of a table when statistics on this table doesn't matter? We have several relatively small tables - around 5000 8K pages. But! ...
-2
votes
2answers
459 views

Update nvarchar value with GETDATE results

I would like to be able to update an nvarchar column with GETDATE(). e.g. if the value is currently 'ordered', I need to append the date. I did try : update order_line set comments = 'ordered'+ ...
-2
votes
1answer
2k views

Sql Server Optimize left Join [closed]

Hello i've these 2 tables : Table Contac_Group ID int Grp_ID int Grp_Name nvarchar(80) Is_Grp_Hidden Bit Table Contacts ID int Contact_Grp_ID int /* this values is from Contact_Group ...
-2
votes
2answers
2k views

How to make an OUTER JOIN return ZERO instead of NULL

I am trying to accomplish this on SQL Server. The simplest table structure with data is shown below. Table: Blog BlogID | Title -------+-------- 1 | FirstBlog 23 | Pizza Table: User ...
-2
votes
1answer
185 views

Column alias with Table alias [closed]

My question is, what type of column alias it is? SELECT serial_no = ROW_NUMBER() OVER (ORDER BY Number),Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING('Anaemia|Anorexia|Cachexia', Number, ...
-2
votes
1answer
219 views

How Do You Set a SQL Backup to URL Blob Retention Period Greater than 30 Days?

How can a retention period be configured to delete SQL backup blobs older than some custom duration, such as 180 days? The existing command only permits a max of 30 days: use msdb; GO EXEC ...
-2
votes
1answer
2k views

why unable to execute table-valued function?

Here is table-value function I have declared like: CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY) RETURNS INT AS BEGIN RETURN ( Select ( ( ...
-2
votes
2answers
5k views

Select query having count and variable

I have a table like the one below. I need to form a select query. I'm going to pass a RefID like SELECT SNo FROM Register WHERE RefID = 1 And beside the SNo in the Statement, I need to have ...
-2
votes
1answer
27 views

Should I use the LIKE predicate or fetch all rows and process them on the client?

I am optimizing my application, while this task, I find one query which is fetching data from database using like . and database rows are around 10000. So is this a good way to use like in query or I ...
-2
votes
1answer
42 views

Query syntax error [closed]

I tried a lot but can't find what is wrong with this query, It gave me this error: Incorrect syntax near ')' WITH cte AS ( SELECT * FROM ( ...
-2
votes
2answers
80 views

Concatenating Dynamic SQL commands with UNION returns inconsistent results

I'm testing a report that I've written in T-SQL and I'm having some issues with the results that are being returned. Here is the section of the code that I'm having issues with; CREATE TABLE #Tables ...
-2
votes
1answer
55 views

What do I do about blocked processes? [closed]

I am getting alerts stating: The SQL Server Performance counter 'Process Blocked' (Instance N/A) of object 'General Statistics' is now above the above threshold of 40 (the current Value is 41)" ...
-2
votes
1answer
42 views

Style bodytext while sending email using sp_send_dbmail [closed]

I am working on an email alert in trigger where body text has to be styled like this: Counterparty: a Previous Status: (Any status should be red) New Status: TRDNG OK(Green colored and Bold Upper)...
-2
votes
2answers
71 views

Are lost updates completely handled in SQL Server or is manual intervention still required? [closed]

As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first. We ...
-2
votes
1answer
49 views

Looking for help with this shipping report! The Case Syntax is throwing me for a loop

So, the goal of this report is to get all orders that have been shipped in a month (October here). I was initially just using the 'PickedUpOn' field but soon found that there were instances where this ...
-2
votes
1answer
101 views

Is there any Performance Issue joining Row based Table to Clustered Columnstore Index Table [closed]

Is there any Performance Degradation joining Row based Table to Clustered Columnstore Index Table? Does SQL Server have any guidance for or against this? We have a table that is 2 million rows, and ...
-2
votes
2answers
475 views

Performance problems after setting ALLOW_SNAPSHOT_ISOLATION on

I'm running a 15GB database on SQL Server 12.0.5207. Server is a virtualized Windows Server 2012 R2 with 16GB RAM and 4 CPUs. Yesterday, we set ALLOW_SNAPSHOT_ISOLATION to ON, to check if it would ...
-2
votes
1answer
193 views

RID and Key lock on same object by a Select and Delete query

To be specific how did the select query (spid 128, on the left) is able to acquire a S (shared) lock on Index but not on the row? Also, how did Delete query (SPID 121, on the right) is able to get X ...
-2
votes
1answer
111 views

a RIGHT JOIN is not working as expected

Question UPDATED: Yes I know the tables are not normalized and yes I know there are no primary keys. Semester table Semester, Year, City 1|2017|Washington 2|2017|Paris 3|2017|London 1|2018|Paris 2|...
-2
votes
2answers
3k views

Connecting to Database using powershell [closed]

I'm trying to connect to my database using PowerShell but it's not find it even though I can see it using SSMS. I'm trying to follow the example MS provides here
-2
votes
1answer
464 views

Is it possible to disable locks for a specific table?

Is it possible to disable all locks for a specific table in any version of SQL Server? I have a handful number of tables that are populated with data at deployment time. For example, one of the ...
-2
votes
1answer
24k views

Scripting out database user level permissions [duplicate]

I have tried to search this and was able to find few but could not find exactly the way to script out the database level permissions for a database. I am migrating this database whose logins I can ...
-2
votes
1answer
2k views

write stored procedure to delete records

There are two tables job run and account interface, as per requirement to write code to prune associated JobRun records (JobRun.AccountId = AccountInterface.AccountID AND JobRun.InterfaceID = ...