Tag Info

Hot answers tagged

11

The whole set of lines that satisfy the problem are known as a Pareto efficient front (or frontier or set) or the convex hull problem. (See also my answer in the similar question at StackOverflow: How to write a query selecting reasonable trade-offs?). So, one way to solve would be to identify first the points of the front, with a query like below. I assume ...


6

The idea that an application should be written with just standard SQL comes from application developers who think that changing a DBMS is a worthy design goal. I -- and many other DBAs with me -- don't agree. While it holds true for basic features, your application is going to perform best if it uses the features that your particular DBMS provides. This ...


5

One way of doing it would be to use LEAD. WITH T AS ( SELECT StudentID, GradeIssued AS g1, LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2, LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3, LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4 ...


3

This should largely be a Google question and might get marked, but I'll add an answer just in case it helps others. Please see a blog post by the Brent Ozar crew: http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/ First off: ‘Reorganize’ and ‘Rebuild’ are two different operations that each reduce fragmentation in an ...


3

how to stored the actual IP address - in text or bytes format. Which is going to be better? Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither. Given the following information (from Wikipedia article on IPv6): Address representation The 128 bits of an IPv6 address are represented in 8 groups of 16 ...


3

SELECT column_a ,column_b ,column_c ,column_d FROM mytable WHERE column_a = column_b AND column_a = column_c AND column_a = column_d ETC. SQL Fiddle Link My Notes CREATE TABLE mytable ( column_a int, column_b int, column_c int, column_d int ); INSERT INTO mytable VALUES (1,2,4,7); INSERT INTO mytable VALUES (6,3,5,6); INSERT INTO mytable ...


3

How about something like below : change your output in SSMS to text and once you are happy run the generated sql statements in another query window : use master go select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go' from ...


3

USE msdb; GO CREATE USER shims FROM LOGIN shims; GO ALTER ROLE SqlAgentUserRole ADD MEMBER shims; GO Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


2

Would you like to try this? DECLARE @cmd1 nvarchar(2000) SET @cmd1 = 'IF ''?'' NOT IN(''Name of your database'')' + 'BEGIN ' + 'drop DATABASE [?] ' + 'END' EXEC sp_MSForEachdb @command1 = @cmd1 GO it worked here. PAY ATTENTION TO NOT DROP MASTER AND ETC. Put then on that list.


2

After setting the db to singleuser you have to switch back to master. I added a use master statement but I don't want to test this.... EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') BEGIN ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE **use master;** DROP DATABASE ''?'' END


2

If the database is in full recovery mode and you are doing periodic log backups then you can find the login that deleted the data, and the exact moment the data was deleted. Read How to read and interpret the SQL Server log. Also, with a proper backup strategy in place, it will be trivial to recover the deleted data as well. On the other hand, if you do not ...


2

Since Postgres is always evaluating CTEs, you can use a CTE for table a and then something like this: WITH a AS ( SELECT <a column list> FROM table_a WHERE <conditions> ) TABLE a UNION ALL SELECT <b column list> FROM table_b WHERE NOT EXISTS (TABLE a) AND <maybe other conditions> ; Note: (TABLE a) is just a ...


2

This specific case (the exists predicate) is a bit of an outlier as far as correlated subqueries go. ish... Some correlated subqueries are certainly evaluated once for every row in the outer query, but I don't think you can apply that across the board, and I specifically think that's a statement that cannot be applied with authority to the exists ...


2

You could unpivot the data. I'm going to add an surrogate ID column to your table because this will make reconciliation easier. The unpivoted data will look like this: id SourceCol SourceVal 1 A 3 1 B 3 1 C 3 ... 1 N 3 2 A 5 2 B 5 2 C 5 ... 2 N 5 Then you group by ID and ...


2

What you need to do is use the LEAD windowing function to look at one row down, two rows, three rows, etc for however long your pattern is. Example: LEAD(gradeissued,1) over (partition by studentid order by whenissued) as one_row. Put that in a CTE or subquery and then in the next query do a where criteria on your pattern (one_row = 'D' and two_row = 'C' and ...


1

I noticed in the where clause right away some Right Outer Joins, the first one for example do you really want all records from the second table? Also do the tables have good index's?


1

SELECT IF(A01 = 99999, NULL, A01) AS A01, IF(A02 = 99999, NULL, A02) AS A02, ... FROM ... (For empty string, use '' instead of NULL.)


1

if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail? If logshipping paths are same on the primary and secondary servers then adding an ndf will be seamless. If the paths are different on primary and secondary servers then Stop the backup, copy and restore jobs ...


1

Ugh, 2000. Assuming all objects are owned by dbo, you can generate a script, e.g.: SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type IN (N'U', N'V'); SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type = N'P'; Now you can copy and paste the ...


1

The subquery in your example becomes correlated because it refers to a table (stores) in the outer query. This is the definition of a correlated subquery - a subquery which depends on rows in an outer query. What you ask about is an independent subquery not correlated to the main query. You can do that like: SELECT DISTINCT store_type FROM stores WHERE ...


1

Corrective If instead of generating one random value we generate S random values where S is the sample size, then order all of the random generated values, it will be sampling with replacement. (I don't know if I am right.) You are wrong in two points. Your original query does not generate "one random value". random() is a volatile function that ...


1

In Postgres 9.3+, you can use the folowing: select t.* from generate_series(1, 10) as x(i) cross join lateral ( select *, x.i from Table_Name -- where random() < 0.01 order by random() limit 1 ) ; which basically chooses 1 random row, 10 times. In older versions, you can use a simple cross join (no lateral): select t.* ...


1

I think you're trying to avoid writing: UPDATE mytable SET door_open = door_open + INTERVAL '1' HOUR, door_close = door_close + INTERVAL '1' HOUR, car_open = car_open + INTERVAL '1' HOUR, car_close = car_close + INTERVAL '1' HOUR, ... blah blah ... WHERE ... and instead want a way to match column names by wildcard. If that is the ...


1

Instead of SSIS I think you might want to look into SQL Server Service Broker. This lets you send messages asynchronously. See here or here for a start. MSMQ or RabbitMQ may also be options you should consider.


1

Use: A foreign data wrapper like odbc_fdw or tds_fdw (see foreign data wrappers list, foreign data wrapper documentation); or dbi-link - see github repo


1

If you want to change the current\default database to a different one try: alter login <loginname> with default_database = <dbname>; Now, create a user for above login created use <dbname>; create user <username> from login <loginname>; And now you can assign roles to the above create user for the login as below: use ...



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