I'm totally new to SQL Server, I use version 2012.  I have a table with the following structure:  Attendance -------------- TagID     (FK) SessionID (FK)        ScanningTime   I need to create a unique constraint based on two columns (TagID and SessionID)  I'm not sure how to do this. I have created a query and tried this code:  ALTER TABLE Attendance ADD CONSTRAINT UNIQUE NONCLUSTERED (     TagID,     SessionID )   But when I try to execute it, I receive this error:     Msg 156, Level 15, State 1, Line 2   Incorrect syntax near the keyword 'UNIQUE'.   What is the proper way to alter the table using SSMS? Should I create a query each time I want to do so?
30784
How do I create a unique constraint on an existing table in SQL Server 2005?  I am looking for both the TSQL and how to do it in the Database Diagram.
68032
I have a table item having just one column name:-  name ----- toys shirt mobile Shirt speaker Toys ....  ....   I am trying to delete duplicate rows except one duplicated record with the help of below query  DELETE FROM ( SELECT `name`,`rownumber` FROM (SELECT GREATEST(0,@num := IF(`name` = @NAME,  @num + 1, 1),LEAST(1, LENGTH(@NAME := `name`))) AS rownumber,`name` FROM  item ORDER BY `name`) AS result1 ) AS result  WHERE rownumber &gt;1   The Inner query (Starts from SELECT name and ends with result1) returns individual auto incremented number for each repeating group but when i run entire query then it does not work.  I would like to know what changes i need to make in the inner query (contained in round braces) to delete duplicate rows.
77926
It seems to be disallowed to add a Foreign Key constraint to a foreign table.  Is there any other way to do this?  My two tables have these constrains on the remote server.    More specific details:  I only really need it as an annotation because some tools look that up for JOINs.  I have two tables on a remote server that I've added to my local using postgresql_fdw's CREATE FOREIGN TABLE.  These I'm using in PostgREST, an automatic API server uses the REFERENCES information in order to make joins.  Actually, I'm further making two additional CREATE MATERIALIZED VIEW to make the lookups fast - but sadly, they too don't have any way I know of to add REFERENCES information?  I'm open to other ways of making (read-only) lookups fast for these tables on the foreign API server and where Foreign Key constraints will work.
36386
create table employee_tbl ( EmpID int primary key, FirstName varchar(50), LastName varchar(50), Salary float, MobileNo varchar(15) )  create table employeeaudit_tbl ( EmpID int primary key, AuditData varchar(50) )  select * from employee_tbl select * from employeeaudit_tbl  create trigger tr_employee_tbl_forinsert on employee_tbl for insert as begin         declare @EmpID int         select @EmpID = EmpID from inserted          insert into employeeaudit_tbl         values('New employee with EmpID = ' + CAST(@EmpID as nvarchar(5)) +'is added at ' + CAST(GETDATE() as nvarchar(50))) end
105534
I have a table with a column for ID, Date and Frequency of occurrence for that particular date and ID.  ID    date          Frequency 1     2014-05-18      5 1     2014-05-19      4 1     2014-05-20      25 2     2014-05-20      7 3     2014-05-18      4 3     2014-05-20      1 4     2014-05-18      6   I'm looking to insert the dates for each ID that aren't currently there (assuming that the date ranges from 2014-05-18 to 2014-05-20) and add a value of 0 in Frequency for those.  For example, you can see that  ID 1 has an observation for all three dates;  ID 2 is missing 2014-05-18 and 2014-05-19;   ID 3 is missing 2014-05-19;   ID 4 is missing 2014-05-19 and 2014-05-20.  Therefore, the output should look like this  ID    date          Frequency 1     2014-05-18      5 1     2014-05-19      4 1     2014-05-20      25 2     2014-05-18      0 2     2014-05-19      0 2     2014-05-20      7 3     2014-05-18      4 3     2014-05-19      0 3     2014-05-20      1 4     2014-05-18      6 4     2014-05-19      0 4     2014-05-20      0   I've attempted the following from . However, that will only add dates with 0 Frequency that aren't present throughout the entire table instead of on an ID level.   DECLARE @s DATE = '20140518', @e DATE = '20140520';  SELECT c.date, Frequency = COALESCE(a.Frequency,0) FROM dbo.Calendar AS c LEFT OUTER JOIN dbo.table AS a ON c.date = s.[date] WHERE c.d &gt;= @s AND c.d &lt; DATEADD(DAY, 1, @e);
44610
I need to create a background job that processes a table looking for rows matching on a particular id with different statuses. It will store the row data in a string to compare the data against a row with a matching id. I know the syntax to get the row data, but I have never tried comparing 2 rows from the same table before. How is it done? Would I need to use variables to store the data from each? Or some other way? (Using SQL Server 2008)
122382
ALTER TABLE [dbo].[REMINDER_EMAIL]  WITH CHECK ADD  CONSTRAINT    [FK__REMINDER_EMAIL__FACILITY_ID] FOREIGN KEY ( [FACILITY_ID] )   REFERENCES [dbo].[FACILITY] ( [FACILITY_ID] )  ALTER TABLE [dbo].[REMINDER_EMAIL] CHECK CONSTRAINT [FK__REMINDER_EMAIL__FACILITY_ID]
41986
In SQL Server, you can set the increment of an identity column other than 1.  In what situation would you do that?
7766
This is a very basic query I can't figure out....  Let's say I have a two column table like this:  userid  |  roleid --------|--------    1    |    1    1    |    2    1    |    3    2    |    1   I want to get all distinct userids that have roleids 1, 2 AND 3.  Using the above example, the only result I want returned is userid 1.  How do I do this?
99550
I want to programmatically create a new column in an MS Access table.  I've tried many permutations of ALTER TABLE MyTable Add  MyField DECIMAL (9,4) NULL; and got:      Syntax Error in Field Definition   I can easily create a number field that goes to a Double type, but I want decimal.  I would very strongly prefer to do this in a single ALTER TABLE statement and not have to create a field and then alter it.   I am using Access 2003.
55085
When executing the following (complete) SQL query on Microsoft SQL Server 2000:  SELECT B.ARTIFACTTNS, B.ARTIFACTNAME, B.ARTIFACTTYPE, B.INITIALBYTES, B.TIMESTAMP1, B.FILENAME, B.BACKINGCLASS,         B.CHARENCODING, B.APPNAME, B.COMPONENTTNS, B.COMPONENTNAME, B.SCAMODULENAME, B.SCACOMPONENTNAME  FROM (SELECT DISTINCT A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME        FROM (SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME              FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemName' AND PVALUE = 'MyRuleGroup'                    UNION SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME                            FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemDisplayName' AND PVALUE = 'MyRuleGroup') A,             (SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME              FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemTargetNameSpace' AND PVALUE = 'http://MyModule') B  WHERE A.ARTIFACTTYPE = B.ARTIFACTTYPE AND A.ARTIFACTTNS = B.ARTIFACTTNS AND A.ARTIFACTNAME = B.ARTIFACTNAME) A, BYTESTORE B      WHERE (A.ARTIFACTTYPE = 'BRG') AND A.ARTIFACTTYPE = B.ARTIFACTTYPE AND A.ARTIFACTTNS = B.ARTIFACTTNS AND A.ARTIFACTNAME = B.ARTIFACTNAME      ORDER BY ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME   I get the following exception:  java.sql.SQLException: [Acme][SQLServer JDBC Driver][SQLServer]     Ambiguous column name 'ARTIFACTTYPE'.   What am I doing wrong here and how can I correct it?
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?
115476
I have a database that containes user data and need to dig up only the entries where two attributes are duplicate between users (surname and social security number). I need all of the attributes for these entries, such as email and firstname.   I first used group by to get every user where those two values are duplicates with this query:  SELECT *  FROM jasenet  GROUP BY hetu, sukunimi  HAVING count(*) &gt;= 2  ORDER BY sukunimi;   This returns all duplicate entries. After some research I tried to use IN operator in order to find out each individual duplicate person with this query:  SELECT *  FROM jasenet  WHERE sukunimi  IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) ORDER BY sukunimi;   This works somewhat because I get each individual with duplicate surname, but in addition to this I need to be able to have the query use the social security number as well (hetu).  I tried this query, but it didn't work:  SELECT *  FROM jasenet  WHERE sukunimi  IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) AND hetu IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) ORDER BY sukunimi;   How can I use two columns returned from a query in another query?
64634
I have a table that has a schema like this:  create_table "questions_tags", :id =&gt; false, :force =&gt; true do |t|         t.integer "question_id"         t.integer "tag_id"       end        add_index "questions_tags", ["question_id"], :name =&gt; "index_questions_tags_on_question_id"       add_index "questions_tags", ["tag_id"], :name =&gt; "index_questions_tags_on_tag_id"   I would like to remove records that are duplicates, i.e. they have both the same tag_id and question_id as another record.  What does the SQL look like for that?
41856
I have a business requirement to have a FK field on TableA referencing TableB.  If there is a value in TableBID, it should be unique, but it's not mandatory, so there could be multiple records with null values.  Is it possible somehow to enforce this type of uniqueness on the DB level, using either an index or a constraint?
84174
I would like to create computed index on a table if SQL Server is 2008 or newer and a simple index if SQL Server is 2005 or older:  -- check for sql server version if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) &gt;= 10          CREATE unique nonclustered index ix1_table             ON table (column1, column2)             WHERE column1 is not null and column2 is not null         ELSE             CREATE nonclustered index ix1_table                 ON table (column1, column2)   The problem is that the whole statement is evaluated and on SQL Server 2005 this throws an error:     Incorrect syntax near the keyword 'WHERE'.   Is it possible to somehow create different index based on SQL Server version?
121357
We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables.  For the BeginDate, we want it to be dependent on transaction date and not the current getdate time. We are reprocessing a history of transactions. Example below, customer has gym or bank status, and goes from inactive, to active, or pending, dependent on transaction date.  We currently have this.  CREATE TABLE dbo.Department (     CustomerId int primary key,     MembershipStatus int,     TransactionDate datetime  );   We we would like to create a table like this.  CREATE TABLE dbo.DepartmentHistory (     CustomerId int primary key,     MembershipStatus int,     TransactionDate datetime,     BeginDatetime datetime,     EndDatettime datetime  );   Example usage would be:   first Customer Transaction on 3/5/2018 as Pending P        +------------+--------+-----------+---------+     | CustomerId | Status | BeginDate | EndDate |     +------------+--------+-----------+---------+     | 1          | P      | 3/5/2018  | NULL    |     +------------+--------+-----------+---------+    second transaction is 4/21/2018 is Active A        +------------+--------+-----------+-----------+     | CustomerId | Status | BeginDate | EndDate   |     +------------+--------+-----------+-----------+     | 1          | P      | 3/5/2018  | 4/21/2018 |     | 1          | A      | 4/21/2018 | NULL      |     +------------+--------+-----------+-----------+