Tag Info

Hot answers tagged

7

Since you can reset the IDENTITY by issuing a simple TRUNCATE: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10) FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] INNER JOIN sys.identity_columns AS ic ON t.[object_id] = ...


6

As usual, it depends. Let's first consider what a key is, it is something that uniquely identifies something in your business. Even if you choose to use a surrogate key for some reason you should still identify and implement the natural candidate key's. A key ideally has a number of properties unique stable -- should not change frequently, not ...


6

;WITH x AS ( SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID) FROM dbo.your_table ) SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL FROM x WHERE VAL < Prev_VAL ORDER BY ID; If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


4

Your query, given your data, is equivalent to this one: SELECT emp.name, role.role_name, emp.emp_id FROM emp INNER JOIN role ON emp.role_id = 1; -- because this is the role_id for 'tech' This means that you don't define a condition about how to join the two tables. In turn, this results in a Cartesian product of role (without any restriction about ...


4

Something seems really, really, really bad in this design. Do you really have a large number of tables that have a column called Internet? What does that column even mean? Do you really want to call this stored procedure such that it updates every row in the specified table? Why? You don't have a one-row table for every location / city / room, do you? ...


3

You can use the following approach in SQL Server to pivot the data into the format you are looking for. You may also be able to use the PIVOT operator, but I tend to prefer to write out the CASE statements since I find that syntax more clear (they have always resolved to the same query plan in all cases that I have checked). Updated answer based on Aaron ...


3

Here's one way. Assuming this table and sample data: USE tempdb; GO CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE); INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'), ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'), ...


3

You can write a script to call DBCC CHECKIDENT ([TABLENAME], RESEED, 0) over the tables resulting from this query (which will inform of all the tables with an identity field. SELECT t.TABLE_NAME ,c.COLUMN_NAME ,c.TABLE_CATALOG ,c.TABLE_SCHEMA FROM ...


3

Oh Boy! System Databases on C Drive are recipe for disaster ! Change the default database location away from C:\ drive. You can do it using ALTER DATABASE .. MODIFY FILE Change the db file location ALTER DATABASE db_name MODIFY FILE (NAME = logical_data_file, FILENAME = 'D:\data\physical_file.mdf'); ALTER DATABASE db_name MODIFY FILE (NAME = ...


3

Insert query: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type) SELECT Id, CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))), username, firstname, lastname, email, 'www.some_static_url.com', '1' FROM veeva.user ...


3

MySQL, when executing an update against a single table, does -- wrongly -- treat updates as "ordered," and update the columns in the order they're written in the query. The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This ...


2

Joe Celko thinks very strongly that you should use the "real world" data such as a Name rather than using an artificial value such as an ID. However, many people here on DBA.SE prefer to use an ID value as the Primary Key. But it is a to some degree a philosophical question, or personal opinion. An aside: You did not say which brand of SQL (MS SQL ...


2

I'd use a surrogate product id. This avoids the need to store potentially lengthy strings multiple times across every order they are used in. Additionally if the product name is ever updated you only need to do this in one place. It is also more flexible as it allows you to have multiple different products (perhaps over time) with the same product name. ...


2

I think you're running the restore with the NO RECOVERY option. This will restore your database, but will leave it in the RECOVERY state. You can easily check this by taking a look at it in the SSMS. It should say something like: yourDatabaseName (Recovering...) If you haven't the option to take a look in your SSMS, you can simply querying your master ...


2

I assume the * is only for demonstration purposes. You can use GROUP_CONCAT to aggregate strings from several rows into one string. In the example below I only include carname, you probably want some more info: SELECT c.carname , GROUP_CONCAT(p.partname) FROM carparts as cp JOIN car as c ON cp.carid = c.id JOIN parts as p ON p.partid = ...


2

you structure looks good . except that you could merge the amount to the part table . as every part is strictly associates with price. also, only reason you will have duplicate rows is if any of your dimensions tables do not follow strict primary constraints. Car - id (pk) - carname - image - category - status Parts - partid (pk) - partname - ...


2

I found this: SOURCE There is great reason why you should never call stored procedures from within triggers. Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive ...


2

If (and it's a big if) the format remains constant with a number and a unit, you should be able to create a case statement that will get the total of the smallest unit. In your example, let's use days as the smallest unit: CASE WHEN timeToMaturity LIKE '%Days' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 1 WHEN ...


2

I've made a function where you just put the string and the output will be in Capital Letters: DELIMITER // CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1 DETERMINISTIC BEGIN DECLARE vPOSICION INT DEFAULT 0; DECLARE vTMP VARCHAR(150) DEFAULT ''; DECLARE vRESULTADO ...


2

Hmm... I'm a bit confused. By definition, if two events happen at different places, then they're different events. My advice in this scenario is to tave 3 tables as follows: CREATE TABLE Event ( Event_ID INTEGER, Event_Description INTEGER, -- FK Event_Location INTEGER, -- FK Event_Date DATETIME ); And then two other "parent" tables CREATE ...


2

This is a typical situation in which you have to create three tables: Helpers, Contacts, and Help_Contact, using the third table to represent associations between the elements of the first two, by storing the foreign keys for the first two, without any multivalued field in those. That is, you should have something like: Helpers(h_primary_key, ...


2

You could try an indexed view. I know you said you couldn't add an index to the table but this would be creating a view and then indexing it. CREATE VIEW vw_Message WITH SCHEMABINDING AS SELECT month_date= month(SendTime) ,year_date =year(sendtime) ,MessageType, COUNT_BIG(*) AS MESSAGE_COUNT FROM dbo.Message where ...


1

I did this once as an exercise in dynamic SQL. It's NOT a good idea for any production system. Primarily you add a huge risk of SQL Injection. This is where someone would pass an actual SQL command in as part of one of the parameters and you end up running it without realizing it. You can minimize this but even so there is the risk. Also there is a ...


1

I don't know if is equal on SQL Server, I did this in MySQL using the WEEKDAY function. Table UserRecords: Query: SELECT user, count(case when WEEKDAY(qDate)=6 THEN qDate END) as S, count(case when WEEKDAY(qDate)=0 THEN qDate END) as M, count(case when WEEKDAY(qDate)=1 THEN qDate END) as T, count(case when ...


1

maybe I'm a bit too "old school" but I've used a query and script for about 10 years now since the SQL Server 2005 days: SELECT LEFT(d.name,20) AS database_name, CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'D' THEN b.backup_finish_date END), 120) AS LastFullBackup, CONVERT(VARCHAR(16), MAX(CASE b.[type] WHEN 'I' THEN b.backup_finish_date END), 120) ...


1

The sub-select: SELECT role_id from role WHERE role_name = 'tech' returns exactly one row, so your query can be transformed to: SELECT emp.name, role.role_name, emp.emp_id FROM emp JOIN role ON emp.role_id = 1 This can be transformed to: SELECT emp.name, role.role_name, emp.emp_id FROM emp CROSS JOIN role WHERE emp.role_id = 1 Therefor the ...


1

CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)) You missed one more closed parenthesis in your statement CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1))) 2.The query you wrote for concatenation returns only first letter in capital letters of both fist and last names and it doesn't print space between them. example :First ...


1

It is not very clear from your description a) what DB you are using? b) do you need help in the design of tables? c) or you need help in SELECT query only? But then please show your tables. So far it is clear only that you have 2 entities Contacts and Helpers, linked as M:M, so Renzo is correct suggesting a third link-table. Let you want to start dance ...


1

Not knowing what the full relationships are or sample data, try this FK correction first to see if it fixes the problem: SELECT * FROM carparts INNER JOIN car on carparts.carid = car.id INNER JOIN parts on parts.partid = carparts.carpartid INNER JOIN amount on amount.amountid = carparts.amountid WHERE status = 1



Only top voted, non community-wiki answers of a minimum length are eligible