Tag: sql

How to force Microsoft Database Project generate ALTER statement for primary key constraint instead of creating temp table?

I have following script for LoginLogo table: CREATE TABLE [LoginLogo] ( [LoginLogoId] INT IDENTITY (1, 1) NOT NULL, [LoginId] INT NOT NULL, [LogoNm] NVARCHAR(255) NULL, CONSTRAINT [PK_LoginLogo_LoginLogoId] PRIMARY KEY CLUSTERED ([LoginId] ASC), CONSTRAINT [FK_LoginLogo_LoginId] FOREIGN KEY ([LoginId]) REFERENCES [Login] ([LoginId]) ); GO CREATE NONCLUSTERED INDEX [IF_LoginLogo_LoginId] ON [LoginLogo]([LoginId] ASC) ON [INDX]; I need to change […]

Is MERGE statement is a good option to use when we are just updating a table?

I know MERGE is used if we have to combine multiple operations(INSERT/UPDATE/DELETE). But in my case, I have a procedure which is using MERGE statement to update few columns in a table. But sometimes it takes a lot of time to run which is not expected because data is not that big. So is it […]

sql select a default row if the result is not found

Suppose that I have table that holds some data for individuals and companies, and I want to retrieve default value if no data found for individuals or companies, for example, suppose that I have a table CustomerAccountId CustomerAccountType DueDays IsAdjustable isDefaultForIndividual isDefaultForCompany 1 Individual 10 true false false 2 Company 20 false false false null […]

Jetbrains Datagrip 2017.1.3, force columns exported when dumping data to sql inserts file

I have an SQL server database with a lot of tables and data. I need to reproduce it locally in a docker container. I have successfully exported the schema and reproduced it. When I dump data to an SQL file, it does not export automatically generated fields (Like ids or uuids for example) Here is […]

How do I escape a single quote in dynamic SQL

I have the following query on SQL Server, some of the values are stored in variables computed earlier: SET @Command = N’INSERT INTO Products (Id ,Region ,Name ,Category ,CreatedBy ,CreatedOn ,) SELECT ‘ + @Id + ‘, Region, ”’ + @ProductName + ”’, Category, CreatedBy, CreatedOn FROM ‘ + @ProductTable + ‘ WITH (NOLOCK) WHERE […]

SQL Charindex vs LIKE operator

Can someone please explain me this weird behavior of the charindex function in SQL? We are searching the values where the second letter is an ‘o’. I don’t understand why query 1 and 3 don’t return ‘OO software ontwerp’, while using the like operator in query 4 it does show up. Even when I’m searching […]

SQL Creating a function to get the sum based on column name

Im trying to write a simple function that will allow me to get the sum based off the value of a column. CREATE FUNCTION [GetSumOfColumnByCase](@column varchar(50), @case int) RETURNS INT AS BEGIN declare @return int set @return = SUM(CASE WHEN @column = @case THEN 1 ELSE 0 END) — Return the result of the function […]

start and end dates of week in SQL Server for the month

we need to get week start and end dates for the month output shown as below: Week# StartDate EndDate Week 1 2017-03-01 2017-03-04 Week 2 2017-03-05 2017-03-11 Week 3 2017-03-12 2017-03-18 Week 4 2017-03-19 2017-03-25 Week 5 2017-03-26 2017-03-31

Design SQL Server database correctly

I have simple registration form, but there are 3 checkboxes for example: Name: [ ] Email: [ ] Pass: [ ] What do you like at free time? Reading: [ ] Travelling: [ ] Sport: [ ] As in example above you see “What do you like at free time?” There are 3 checkboxes. There […]

SQL – Why Does This Happen?

These are the tables that I’m working with. With that in mind, I want to showcase the Employees that are both a supervisor and a manager. But when I used this select e1.fname,e1.lname from employee e1,employee e2,department where e1.ssn=e2.super_ssn and e1.ssn = Mgr_ssn This was the output I know I can solve the problem with […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.