What is the point of outer in a join? I have made a script which harvests mac addresses from switch ports and puts them in a database. I insert it into an import table to be copied over later to the real table. All fine. Now I am writing the query to find which mac addresses are new. So, I need the rows in the import table which are not in the destination table. A left outer join makes sense:

select *
from SwitchportMac_import i
left outer join SwitchportMac sm
on sm.switch = i.switch and sm.port = i.port and sm.mac = i.mac

This returns also rows in i which match in sm. Sure, I can add a where sm.id is null but I am flabbergasted.

So either:

I am an idiot and did something obviously wrong
I am an idiot and have been explaining and understanding joins wrong for 20 years

If it's the first I will dump the DDL and figure it out but I'm scared it's the second one. Does the outer keyword exist just to confuse me?

LEFT JOIN and LEFT OUTER JOIN are synonymous so apologies but it sounds like option 2 :-)

The "Outer" join (which preserves unmatched rows) is as opposed to "Inner" (which doesn't).

There are three flavours of outer join. Left, Right, Full (dependent on which unmatched rows are to be preserved).

As SQL doesn't have direct join syntax for any other types of join (such as left/right anti semi join) there is no ambiguity in allowing the OUTER keyword be optional in the grammar.

The LEFT ANTI SEMI JOIN sounds like what you expected LEFT OUTER to do. You can implement that in a variety of ways. Usually NOT EXISTS is preferred but you can use LEFT OUTER JOIN in conjunction with IS NULL to keep only the non matched ones.

This is all confirmed in the "Join type" section of the documentation

  LEFT [ OUTER ] Specifies that all rows from the left table not
  meeting the join condition are included in the result set, and output
  columns from the other table are set to NULL in addition to all rows
  returned by the inner join.

The square brackets around the [OUTER] just mean that this is optional but nonetheless the same definition applies.


SQL Server 2014 Database vs. Schema Permissions Two Schemas:
 dbo
 test

I'd like to grant create table to the test schema for a windows group; but not have them be able to create a table in the dbo schema.

I have tried:

GRANT CONTROL, ALTER ON SCHEMA :: test TO [ad\windows_group];

GRANT CREATE TABLE TO [ad\windows_group];

DENY ALTER ON SCHEMA :: DBO TO [ad\windows_group];

GRANT VIEW DEFINITION ON SCHEMA :: DBO TO [ad\windows_group]

The members of the windows_group can still create a table on the dbo schema; but cannot drop it.

I've seen multiple references saying that DENYing the ALTER schema permission should block the creation of the table; but I think I'm missing something.

Thanks

I'd like to grant create table to the test schema for a windows group;
  but not have them be able to create a table in the dbo schema.

The correct grants are:

GRANT ALTER ON SCHEMA::test TO [ad\windows_group];

GRANT CREATE TABLE TO [ad\windows_group];

  but not have them be able to create a table in the dbo schema.

They would not be able to do that without ALTER permissions on dbo.

  I think I'm missing something.

Somehow you've granted that user ALTER on the dbo schema or the database.


data warehouse automation tool: benefit or bane? Today while investigating ETL and data warehouse solutions like Kettle, I came across a new concept (for me) called data warehouse automation tool.

I also looked at the white paper here, which indicates there are few vendors in this space.

One reaction I have to these tools is: whoa, anyone purchasing one of these tools is making an enormous investment. At the same time, it seems many data warehouse projects fail, even though they take a significant amount of developer hours to implement.

So, if anyone out there does have experience with a data warehouse automation tool in a real-world project, would you be able to answer these questions?

Did the DWA tool greatly reduce the time to get a data warehouse up and running, or did the time it took to ramp up on learning the tool eat the time that otherwise would have been gained?
If the DWA tool resulted in the failure of your data warehouse project, what were the reasons?
If you have done data warehousing in the past without a DWA tool, and you have also used a DWA tool recently, would you use a DWA tool again for your next data warehouse project?
When would you see a DWA tool as being overkill?
What did you like best about using a DWA tool? What did you like least?

I'll weigh in from the user perspective.
I have worked extensively (15 or so projects) with one of the automation tools on a SQL Server backend, and results were mixed.

Did the DWA tool greatly reduce the time to get a data warehouse up and running, or did the time it took to ramp up on learning the tool eat the time that otherwise would have been gained?

The answer is a great big "it depends". I have found it to largely depend on the complexity of the datawarehouse you are building, and the skills you have using the native tools.
We could easily use the tool to allow customers to design their own datawarehouse and olap solutions with little training and knowledge of the underlying technology provided the requirements were simple.
For an experienced datawarehouse and OLAP developer in the native underlying tools the gains were much less impressive and I haven't seen automation tools supporting each and every feature of SSIS and SSAS. Most provide hooks where you can plug in SQL or XMLA scripts, but as complexity of a project increases you end up scripting more than you are using the automation tool.
Furthermore, as datawarehouse automation tools support multiple versions of the underlying technology I have seen new features in SQL appear later or not appear at all in the automation tools.

If the DWA tool resulted in the failure of your data warehouse project, what were the reasons?

In big complex projects: performance and the lack of flexibility of the automation tool. If you do everything by hand you can configure parallel processing perfectly the way you like it, you can use tricks in your queries to make them faster, you get to think about which operators are blocking in SSIS, what the lineage of your data flow is etc.

If you have done data warehousing in the past without a DWA tool, and you have also used a DWA tool recently, would you use a DWA tool again for your next data warehouse project?

The same "it depends" applies here. It depends on the project. If I expect a lot of data or a lot of complexity, no I wouldn't but I might look at BIML to generate SSIS Packages to avoid repeating tasks.
If it's a simple project, a small source system or I need to have key users at the customer work on the project and take over support after I left, maybe yes.

When would you see a DWA tool as being overkill?

Overkill would when the price exceeds the benefit obviously. If it's a fit for the project, and especially if a less experienced user or  power user can take over the project instead of paying consultant fees, the cost isn't necessarily prohibitive.

What did you like best about using a DWA tool? What did you like least?

What I like best is also the reason to dislike it sometimes. The rigidness and decisions that are made in your place makes sure that projects are "clean" and follow a certain methodology. Especially working with multiple developers it can be a benefit that everybody is forced to work a certain way, you just open a project and know what you will find.
The fact that you are forced to work a certain way can be troublesome if you run into an issue that hasn't been foreseen by the tool.

Some other points I'd like to make

Support is better for the underlying technology. If you have a question on how you have to do something in SSIS or SSAS you could just google or ask here. As the DWA tools abstract that layer you need to turn to the vendor support (which may not be free)
Every piece of software has bugs. SQL Server's bugs are better documented online. I remember a situation where after a long discussion with our vendor's support I just ended up decompiling the DWA tool to figure out what was going on in their code and why it behaved the way it did.
Every DWA tool lacks the flexibility and the community SQL Server has and sooner or later you may run into the limitations the tool is bound to have. Take for example version control. DWA tools may be rolling their own, and that may work more or less, but if you have a simple visual studio project you can add it in your TFS where the rest of your code lives, use BIDShelper to get readable diff's etc. If you need stored procedures in SSAS you'll likely be out of luck too.


How can I prevent UNPIVOT from being transformed into UNION ALL? I have a somewhat complex Oracle query which is taking about half an hour to complete. If I take the slow part of the query and run it separately it finishes in a few seconds. Here's is a screenshot of the SQL Monitor report for the isolated query:

Here is the same logic when run as part of the full query:

The colors correspond to the same tables in both screenshots. For the slow query, Oracle is doing a MERGE JOIN between two tables that do not have an equality condition in the JOIN. As a result of that, about 150 million intermediate rows are unnecessarily processed.

I am able to work around this problem with query hints or rewrites, but I want to understand as much of the root cause as I can so that I can avoid this problem in the future and possibly submit a bug report to Oracle. Every time I get the bad plan the UNPIVOT in the query text is transformed into a UNION ALL in the plan. To further investigate I would like to prevent that query transformation from happening. I have been unable to find a name for this transformation. I also have not been able to find a query hint or underscore parameter that will prevent it. I'm testing on a development server so anything goes.

Is there anything that I can do to prevent the query transformation of the UNPIVOT to UNION ALL? I am on Oracle 12.1.0.2.

I am not able to share the query, table names, or data for IP reasons. I was not able to come up with a simple reproduction. With that said, it's unclear to me why that information is needed to answer the question. Here is an example of an UNPIVOT query along with the same query implemented as UNION ALL.

Try the following Oracle optimizer hint:

NO_EXPAND

This hint disables OR-expansion. OR-expansion transforms combined OR-conditions or IN-lists in the where clause into a compound query with the union all.

We don't have your SQL so this is a guess but seems to a viable option.

As a second option, since you are in a development environment, you can try the Oracle optimizer hint:

NO_QUERY_TRANSFORMATION

but this hint disables all query transformations that the optimizer can perform other than transformations that the optimizer always can apply.


How to retrieve data from 3 tables joined by a record identifier? I have 3 tables.

I need to check whether a specific identifier is present in those tables using a specific WHERE condition. I need to fetch data from all 3 tables.

I have tried LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN but I couldn't get the values of the middle table.

My query attempt is:

Select t1.col1, t2.col2, t3.col3
from t1
left outer join t2
on t1.id=t2.id
full outer join t3
on t1.id=t3.id
where t.id= '101';

I couldn't get the values which are present only in t2 table.

I am assuming that the ID column is either the PK or otherwise declared as unique in all three tables.

Now, if each of these scenarios is equally possible:

all three tables have a row with the ID of 101,
only two (any two) of the three tables have a row with the ID of 101,
only one (any one) of the three tables has a row with the ID of 101,

and you want the requested data from every table that has a match – then you need to use only full joins.

This is one way how you could implement the request:

SELECT
  t1.Col AS t1Col,
  t2.Col AS t2Col,
  t3.Col AS t3Col
FROM
  t1
  FULL JOIN t2 ON t1.ID = t2.ID
  FULL JOIN t3 ON COALESCE(t1.ID, t2.ID) = t3.ID
WHERE
  COALESCE(t1.ID, t2.ID, t3.ID) = 101
;

As you can see, in order to match the third table's ID to that of either of the other two tables when only one of them has a match, you can use the COALESCE function. COALESCE will pick a non-empty (non-null) ID of the two specified, thus making sure that the third ID will be matched with an existing row in either table. The same condition will work correctly if the match is present in both tables.

The WHERE condition uses a similar technique for filtering the result set. Since each table's ID in every row of the joined set is supposed to be either the same value or a null, the COALESCE function will necessarily pick one that is not null to compare to the specified argument (101). Thus, only the row that has the matching data from all or any of the three tables will be selected.

You could also take a slightly different approach: separately select the row matching the ID = 101 condition from each table, use the results as derived tables and then join them:

SELECT
  t1.Col AS t1Col,
  t2.Col AS t2Col,
  t3.Col AS t3Col
FROM
  (
    SELECT ID, Col FROM t1 WHERE ID = 101
  ) AS t1
  FULL JOIN
  (
    SELECT ID, Col FROM t2 WHERE ID = 101
  ) AS t2 ON t1.ID = t2.ID
  FULL JOIN
  (
    SELECT ID, Col FROM t3 WHERE ID = 101
  ) AS t3 ON COALESCE(t1.ID, t2.ID) = t3.ID
;

Although you now have to repeat the same filtering condition, the resulting query might turn out to be more efficient – that will depend on how smart the query optimiser is in your database system.

Test both queries to determine which works better for you.


How to find the same salaries and department names? Write a query to display last name ,department number,and salary of any
employee whose department number and salary both match the department number and salary of any employee using the following table :

employee_id int,
first_name varchar(20),
last_name varchar(25) not null,
email varchar(25) not null,
phone_number varchar(20),
hire_date date not null,
job_id varchar(10) not null,
salary float,
commission_pct float,
manager_id int,
department_id int,
constraint emp_pk primary key(employee_id)

I have tried this :

SELECT * FROM employees e1
WHERE e1.employee_id = ( SELECT employee_id
                           FROM employees e2
          WHERE e2.department_id = e1.department_id
          AND e2.salary = e1.salary);

What is wrong?

This should work:

select e1.*
  from employees e1 inner join (
    select count(employee_id) as CNT, department_id, salary
    from employees group by departmant_id, salary) t1
  on e1.department_id=t1.department_id and e1.salary=t1.salary
  where t1.CNT>1

The problem with your own query is twofold:

the return value of (select...from ..e2..) is from the very nature of your problem not unique, so the "=" relationship should either throw an error or return whichever is the first result (I'm not familiar enough with MySQL to know which applies), which doesn't have to be the one you're looking for.
filtering via "e1.employee_id =" will only ever be true for the record itself, but NOT for another record that matches your criteria

If you insist on using a filter:

SELECT * FROM employees e1
WHERE (e1.department_id,e1.salary) IN
 (SELECT e2.department_id,e2.salary
  FROM employees e2
  WHERE e2.employee_id <> e1.employee_id)


How do I Configure Per Database Roles for PostgreSQL? The default for PostgreSQL is that roles are created per cluster not per database.  I would like to have per database roles.  I could have sworn I once read about a way to set that up but now I can't find any reference to it.  I think it was an initdb option or a createcluster.conf setting or similar but I've read the initdb documentation thoroughly and can't find anything on this.

Is there a way to set per database roles in PostgreSQL?

There is no direct way. Roles are stored in the cluster-wide system table pg_authid And, quoting the manual:

  Because user identities are cluster-wide, pg_authid is shared across all databases of a cluster: there is only one copy of pg_authid per cluster, not one per database.

Bold emphasis mine.

There are also no per database settings in that table or related system tables. So there are no "per database roles".

There are ways to restrict access for each role to a single database. Most effectively in the pg_hba.conf file. But you still can only use each role name once in the whole DB cluster.

I obviously missed the db_user_namespace setting @jjanes remembered. It's the answer to the question, but I wouldn't use it.


Increase query performance where large number of values appear in primary key I have a query where there are numerous values passed in

This is a heavily simplified version of the query

SELECT Id, Name, <other cols..>
FROM Table
<various joins etc.>
WHERE Id in (1,2,3,4,5,6,7....1000,1001,1002 etc.)

I have noticed that when passing in fewer values the query is quick and uses the required index to return the data, however after a certain amount of values the query uses a constant scan and a hash table to join to the main result set which is killing the performance.

Is there a better way of returning this data?

Put your values into #tmp table and join it with the rest of your query


Can I get top 10 remote clients by average query count per minute? Is there any way for me to see the top clients of my sql server?
I want to figure who is executing so many queries on my server. Ultimately I would love to see the application name(like in sql profiler) and IP/name of the server, but even knowing which web server the queries come from would help alot.

Remote Client | Database         | Executions/min
--------------------------------------------------
    XXXX      | FooDb            | 88.00
--------------------------------------------------
    XXXX      | BarDb            | 43.00
--------------------------------------------------

Instead of XXXX it could be - remote server name, IP, ApplicationName (like in SQL Profiler) - any of these.
I think something like this is possible in SQL Profiler, but I cannot run it because this is production environment, and the performance is already bad

Not sure it is possible to get exactly what you want. It probably depends on the setup of the application calling your database, how its connections are pooled etc. But the following will give you the number of current connections, plus their IP and application names:

SELECT c.client_net_address, s.[program_name], s.login_name
    , CURRENT_TIMESTAMP, COUNT(*) as ConnCount
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
GROUP BY c.client_net_address, s.[program_name], s.login_name

Unfortunately this doesn't indicate the activity on the connections. I don't know if the dmv's will be able to return you a history of all the calls broken down by connection/session, which sounds like what you want. This is exactly the sort of thing extended events are for, so in the absence using extended events, you may be stuck.

Coming from an execution side, you could do it for all queries in the last minute using something like the below:

SELECT  eqs.last_execution_time AS [Date Time], execsql.text AS [Script], [sql_handle]
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.[sql_handle]) AS execsql
WHERE eqs.last_execution_time >= DATEADD(mi,-1,CURRENT_TIMESTAMP)
ORDER BY eqs.last_execution_time DESC

Unfortunately this does not show connection information. What you are really after is a combination of the above two, all connections in the past minute and how many executions they performed. Unfortunately the dmv sys.dm_exec_connections only lists the most recent sql query, otherwise it would be a good candidate.

If you really needed this information, you could store both the above queries in tables, running inserts into the tables every minute using a job that is scheduled to run every minute. You could then analyse both tables and make some assumptions about what is calling the procedures.

There may be a way to provide all historic executions from a session/connection using dmv's which I don't know, hopefully this will put you on the right track.


How do you do two date calculations in one SQL Statement? In a SQL Stored Procedure, I need to calculate a date by subtracting 1 month and set the day to the 17th of the month.

I can subtract 1 month with:

DATEADD(month,-1, loanFirstPmtDate)

But do not know how to also set the day to 17.

How do I do both calculations in one SQL statement?

Example: Loan First Payment Date = ‘03/01/2019’
Subtract 1 month and set day to 17 = ‘02/17/2019’

Given a date:

DECLARE @date datetime = CONVERT(date, GETDATE());

--The beginning of this month:

SELECT DATEADD(DAY, 1-DAY(GETDATE()), @date);

-- The beginning of last month - just subtract a month:

SELECT DATEADD(MONTH, -1,
  DATEADD(DAY, 1-DAY(GETDATE()), @date));

-- Then to get the 17th of last month, just add 16 days:

SELECT DATEADD(DAY, 16, DATEADD(MONTH, -1,
  DATEADD(DAY, 1-DAY(GETDATE()), @date)));

That will work on 2008 and above. On 2012 you can write it a little shorter:

DECLARE @date datetime = DATEADD(MONTH, -1, GETDATE());

SELECT DATEFROMPARTS(YEAR(@date), MONTH(@date), 17);

If you are doing this for searching, though, and not output, I highly recommend adjusting the date range parameter values, not applying DATEADD() operations data in the loanFirstPmtDate column.


Will removing a 2nd log file break log shipping? We have a large database 200GB+ to which we added a second log file to cater for growth during an upgrade. This is now no longer needed so we want to remove it. Normally this would be a simple ALTER DATABASE <blah> REMOVE FILE <filename> statement.

However our database is log shipped to a secondary for reporting, so the question is - will removing this file break log shipping?

Server is SQL Server 2005 SP3 Standard Edition if it has any bearing.

Removing the extra file won't give you any problems.


Crossjoin-like operation except A x {} = A? Is there a way to implement operation (let's annotate it ∘) that behaves just like regular cross join except for empty sets it returns non empty side.

∘ - new operation
x - cross join
{} - empty result set

A ∘ B  = A x B
A ∘ {} = A
{} ∘ B = B
{} ∘ {} = {}

Columns of A and B differ and are non empty.

Will this work for you?

A FULL OUTER JOIN B ON TRUE


Does inserting in order have the same effect as clustering? I have a table that will contain some pre-computed data based off other tables. (Computing on the fly is too computationally expensive given the size of data I have to deal with.) I will be generating incrementally as source data is added. (I won't ever need to UPDATE it in normal usage; portions may be deleted and regenerated.) The table will be fairly large. It's currently about 50 million rows and will grow every year.

Most of the queries against this table will be filtered down by a foreign key ID column. As such, they perform better if all the rows for that ID are grouped into the same pages. I can guarantee this ordering on disk by creating an index and calling CLUSTER periodically, but this is obviously less than ideal as it would require some kind of scheduled task, coordinating against usage and other scheduled tasks, etc.

However, since I'm generating this data in chunks related to the foreign key I want to CLUSTER by, I can easily put an ORDER BY clause on the INSERT command:

INSERT INTO big_table (source_table1_id,a,b,c)
SELECT
   source_table1_id,
   5 /* some formula */,
   /* ... */
FROM source_table1
JOIN source_table2 ON ...
...
WHERE ... /* some condition indicating what needs to be generated */
ORDER BY source_table1_id

Will this affect the on disk storage order, grouping the rows into close to the minimum number of pages? And if it does, are there other processes that could mess up the on disk order later?

I am currently using PostgreSQL 9.3, but I'd like to know about newer versions as well for upgrades.

Rows will be processed in the guaranteed order, but it does not mean that after being inserted they will be located next to each other. This will only be possible if records are never deleted or updated in your table. Once you update or delete some rows, after vacuuming you will likely have free space in the middle of the table, where the next inserted record(s) will go.

Some extra details in this question.


Count records in a related table I have three tables: items, members, subscriptions.
One item can be get by 1 or more members (one member can get more items), so subscriptions represents the many-to-many relationship between items and members.
So we have:

items(id, name)
members(id, name, email)
subscriptions(*id_item*, *id_member*)

And now my problem: I have a quite comlicated query which manage items and outputs items data, I want to add to this output a column that contains the number of members who subscribed that item.
I wrote this query but I don't want to use sub select statement

SELECT
(SELECT count(*) AS current_sub from subscriptions AS s where s.id_item = i.id) AS current_members,
  -- ..... other code....
FROM items AS i
WHERE
// ....other code...

I use MySQL 5.5. Thanks in advance.

This one might work better:

SELECT
*
FROM
items AS i
INNER JOIN (SELECT id_item, COUNT(*) AS count_subs FROM subscriptions GROUP BY id_item) counts
      ON i.id = counts.id_item
WHERE
....

Post the result of the query with EXPLAIN keyword in front of SELECT. Then we can see, if and how indexes are used.


How to implement complex search functionality? Below is my table structure:

CREATE TABLE `post` (
  `postId` bigint(20) NOT NULL AUTO_INCREMENT,
  `userId` mediumint(8) unsigned NOT NULL,
  `areaId` smallint(5) unsigned DEFAULT NULL,
  `postTitle` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `postPrice` int(10) unsigned NOT NULL,
  `availableFrom` date DEFAULT NULL,
  `postCreateDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `postCreateIp` varbinary(16) DEFAULT NULL,
  `postUpdateDate` timestamp NULL DEFAULT NULL,
  `postUpdateIp` varbinary(16) DEFAULT NULL,
  `fullAddress` varchar(127) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `floor` tinyint(3) unsigned DEFAULT NULL,
  `room` tinyint(3) unsigned DEFAULT NULL,
  `bath` tinyint(3) unsigned DEFAULT NULL,
  `size` smallint(5) unsigned DEFAULT NULL,
  `postContactNo` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `des` varchar(511) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `imageUrl` varchar(127) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `postType` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0>for_rent\n1>need_rent',
  PRIMARY KEY (`postId`),
  KEY `post_userId_postCreateDate_index` (`userId`,`postCreateDate`),
  KEY `post_areaId_postType_postId_index` (`areaId`,`postType`,`postId` DESC),
  CONSTRAINT `post_join_area_areaId_fk` FOREIGN KEY (`areaId`) REFERENCES `join_area` (`areaId`),
  CONSTRAINT `post_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=20004 DEFAULT CHARSET=utf8

  I want to add search functionality for this table. There will be below conditions:

  I may search by text which will match with rows postTitle and
  des.
  I may also filter those result by many conditions for rows
      areaId, postType postPrice, availableFrom, floor, room, bath, size.

Below is an example:

select *
from post
WHERE areaId = 29
  AND postType in (0, 1)
  AND (postPrice > 0 AND postPrice < 10000000)
  AND availableFrom = '2019-06-26'
  AND (floor >= 0 and floor <= 6)
  AND (room >= 0 and room <= 20)
  AND (bath >= 0 and bath <= 10)
  AND (size >= 0 and size <= 20000);

Now I'm not sure how can I implement that efficiently. I can think below ways:

First I will query for data which match against postTitle and
des rows, this query will use MySQL full text index which I will
create on postTitle and des rows. Then i will apply filter on
those result for areaId, postType postPrice, availableFrom, floor,
room, bath, size rows but this will not cover any index because
this will use temp table.
Second, I may write query by combining all conditions like below:

select *
from post
WHERE (postTitle like '%post%' or des like '%test%')
  AND areaId = 29
  AND postType in (0, 1)
  AND (postPrice > 0 AND postPrice < 10000000)
  AND availableFrom = '2019-06-26'
  AND (floor >= 0 and floor <= 6)
  AND (room >= 0 and room <= 20)
  AND (bath >= 0 and bath <= 10)
  AND (size >= 0 and size <= 20000);

  But I'm not sure how to make this query efficient. How to create index for this query. Please suggeset me how can I design this search functionality or what do I need to do to make this query efficient?

Use Fulltext:

FULLTEXT(postTitle, des)

and

AND MATCH(postTitle, des) AGAINST("+post +test" IN BOOLEAN MODE)

Construct, and optionally leave out, any tests that are not requested.  That is, don't include this line if there are only 20 rooms:

AND (room >= 0 and room <= 20)

Don't bother will bath >= 0 since it is UNSIGNED.

Gather typial queries.  Either by asking 'users' what they will ask about or by collecting queries on the fly.  Then construct the 'optimal' indexes.

But, first, note that if the MATCH clause is included, it will be the only index used.  That is, for cases where the user is not testing on postTitle and des, then we can look at indexing.

Clarification:  It will use the FT index first, then filter on any other WHERE clause, but without the benefit of INDEXes.  The assumption is that FT filters down to very few rows, and the subsequent filtering is not a problem.

The only two useful columns in the above example are those with =.  So:

INDEX(areaId, availableFrom)

But, after looking a user activity, you may find others.

See also Elasticsearch.


Show the error message, after I accidentally clicked "Yes" on "don't show error again" I was trying to alter an existing Maintenance Plan, but received an error on doing so. Before actually reading it I clicked it away, too late realizing that the text said "Do you wish to hide this error message in the future?"

I tried logging out/logging in, resetting my settings, browsing through Tools > Options but the error won't show again. How can I reset my earlier decision and show the error message?

It's Microsoft SQL Server Management Studio version 11.0.2100.60.

You could try shutting down all instances of SSMS and deleting the file SqlStudio.bin, which works for clearing out things like the MRU list for server connections. Just note that this will also wipe out other preferences, so you may want to export certain settings (like if you've customized fonts and colors, startup options, etc).

Also, why is SSMS RTM? You should at the very least apply Service Pack 2, if not use a newer version altogether.


How different clauses treat Null/Unknow in SQL Server 2012? (relatively new to SQL and SQL Server)
I am reading SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan. Autor states that:

  SQL has different treatments for UNKNOWN in different language
  elements

So, I went through SQL Server documentation, and in particular WHERE clause, but could not find anything specific on how NULL/UNKNOWN is treated.

Could someone point me to correct document or point me to what I am reading wrong in the documentation ? (I highly suspect that answer to my question is buried deep somewhere in the SQL Standard, but it might be way too complex for me, at least at this point.)

There are subtle differences on how UNKNOWN and NULL are treated in the various clauses.

Three clauses where a boolean expression can appear are the WHERE, HAVING and FROM (in the ON sub-clause). In all three, the UNKNOWN is treated the same way.

WHERE <boolean expression>:

a row "passes" the <boolean expression> and is returned if the expression evaluates to TRUE. If the expression evaluates to FALSE or UNKNOWN, the row does not pass and is removed from further evaluation of the query.
HAVING <boolean expression>:

The rows examined in the HAVING clause are those created by the aggregation of the previous GROUP BY clause. Similar to above a row (created by the aggregation) "passes" the <boolean expression> and is returned if the expression evaluates to TRUE. If the expression evaluates to FALSE or UNKNOWN, the row does not pass and is removed from further evaluation of the query.
a JOIN b ON <boolean expression>:

Every row from a is checked against every row from b. If the expression is TRUE, then the check passes and the combination of the two rows is valid for further processing of the query. If FALSE or UNKNOWN, then the combination is rejected.
There is a slight change when the join is an outer join (LEFT, RIGHT or FULL) but not regarding how UNKNOWN is treated. What happens here is that if a row from the left table (in a LEFT join) matches no row from the right table (i.e. all its checks evaluate to FALSE or UNKNOWN), then this row is still forwarded and the missing values for the b columns are filled with nulls.)

The situation is different in the constraints, there is a more "relaxed" treatment for UNKNOWN. I guess this has to do with the decision of allowing nulls in the first place. A null value will result in an UNKNOWN result in most constraints, so it makes sense to allow those rows to be inserted. Otherwise, the effect would be the same as declaring the columns as NOT NULL.

CHECK constraint: CHECK <expression>

a row is allowed to be inserted (or updated) if the expression evaluates to TRUE or to UNKNOWN. It is rejected only if the expression evaluates to FALSE.
FOREIGN KEY constraint, with nullable columns.

similar to the check constraints, if a row has a null in one of the columns that participate in a foreign key constraint, the verification of the FOREIGN KEY constraint is skipped and the row is allowed to be inserted (or updated.) One can think of a foreign key constraint in table a:

FOREIGN KEY (column) REFERENCES b (column)

to be equivalent to:

CHECK (column IN (SELECT column IN b))

So, if a.column is NULL, the expression is evaluated to UNKNOWN and thus the row is allowed.
UNIQUE constraints

Here SQL-Server deviates from the standard which state that if a row with a unique constraint contains NULL, then the unique check is skipped and the row is allowed.
SQL-Server treats nulls as if they were regular values, when it comes to unique constraints and allows only one NULL value, in such columns.

NULL values have also special treatment in some clauses:

GROUP BY:

If a grouping column contains null values, all null values are considered equal, and they are put into a single group (the same thing happens in a SELECT DISTINCT query.)

Aggregate functions (MIN(), MAX(), SUM(), AVG() and others) ignore nulls with the exception of COUNT() which can do a lot of different things:

COUNT(*) and COUNT(constant) will count all rows.
COUNT(column) and COUNT(expression) will ignore nulls as the other aggregate functions and count only rows where the column or expression is not null.
COUNT(DISTINCT column) and COUNT(DISTINCT expression) will also ignore nulls and count only the distinct non-null columns or expressions.

So the following 4 queries may return 4 different results if there are nulls in the column:

   SELECT COUNT(*) FROM t ;
   SELECT COUNT(c) FROM t ;
   SELECT COUNT(DISTINCT c) FROM t ;
   SELECT COUNT(*) FROM (SELECT DISTINCT c FROM t) AS x ;

ORDER BY:

In comparisons between them, NULL values are treated as equal.
When comparing them to non-null values, null values are treated as the lowest possible value, lower than any non-null value. As a result, they appear first when the order is ASC and last when the order is DESC.
(There is a NULLS LAST and NULLS FIRST attribute defined in the latest standard but SQL Server has not implemented it yet.)


What is PostgreSQL database ownership When creating a PostgreSQL database one can assign another role to be an owner of that database. What can the new owner do with this database? How is it compared to grants?

When an object is created, it is assigned an owner. The owner is
normally the role that executed the creation statement. For most kinds
of objects, the initial state is that only the owner (or a superuser)
can do anything with the object. To allow other roles to use it,
privileges must be granted.
The right to modify or destroy an object is always the privilege of the owner only.

http://www.postgresql.org/docs/current/static/ddl-priv.html


Resources on database design and data integrity enforcement best practices? I am a developer at a very small software company who has historically taken a very loose approach to database design and data integrity techniques. I'd like to turn that around and start using more in the way of keys, constraints, transactions, etc to prevent software defects and inconsistencies from causing data problems and also bring those causes to light more quickly. Are there any great/classic resources or books on best practices for database design and these sort of data integrity features, discussion of trade offs, etc?

I'm curerntly going throught the following. I've read Louis Davidson's earlier books and fond them very useful so I'm looking forward to reading his latest one (for SQL Server 2012).

"Pro SQL Server 2005 Database Design and Optimization", Louis Davidson et al.
"Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design", Michael J. Hernandez.

More generally, authors you should look out for include Chris Date, Fabian Pascal, Joe Celko (for advanced and/or theoretical topics).

Also check out this other DBA question - some of the answers there may help.


How to add a new column with auto increment ,increment factor, min-value and max value in psql? I have table t1 and I would like to add a new column app_id with the properties given below

auto increment
increment factor(increment by 4)
min-value/initial value(100)
max-value(2000)

I have tried

alter table t4
add column app_id int not null auto_increment = 100 ;

but which is not worked.is it possible to add such a column with the properties mentioned above?

Here, you can use sequence to increase app_id value.
Please noted if the sequence reach to its maxvalue, you will face this error ERROR:  nextval: reached maximum value of sequence .

For example

create table t1 (a int, b int);

create sequence seq_t1
increment by 4
minvalue 100
maxvalue 2000;

alter table t1 add column app_id int default nextval('seq_t1');
insert into t1(a) select generate_series(1, 100) ;

  a  | b | app_id
-----+---+--------
   1 |   |    100
   2 |   |    104
   3 |   |    108
   4 |   |    112
   5 |   |    116
   6 |   |    120
   7 |   |    124


Can we create indexes on views in SQL Server? Can we create Indexes on a view in SQL Server 2008? I have heard about indexed view, which can give performance improvement, but the problem is data refresh on those views: Can it be auto updated, once data in base tables changes?

Indexed views are always in sync with the data in the underlying tables. You can read more about indexed views here.

Be careful going overly enthusiastic with indexed views: they can be a real performance killer on heavily written tables.


Code not working due to collation? Let me preface my question by saying, that my teacher stared at my code for 2h and could only guess that it might be the collation.

Here's the problem: for an exercise we were asked to create a movie database and then write several procedures. One procedure ist supposed to do the following: - take the id of a movie (as a parameter), the director's last and first name and then test wether the director already is in the table. If the answer is no, the name is added to the table. Either way, the procedure then adds the movieID and the ID of the director to a second table. Here's my code:

delimiter //
create or replace procedure p_director
( par_movieID integer,
 par_firstName varchar(50),
 par_lastName varchar(100))
 BEGIN
 DECLARE var_id  INTEGER;
DECLARE var_firstName  varchar(100);
DECLARE var_lastName   varchar(100);
DECLARE var_control integer;
DECLARE exit handler for SQLEXCEPTION
Begin
rollback;
End;
Start TRANSACTION;

set var_firstName = par_firstName;
set var_lastName =  par_lastName;

SELECT directorID FROM director
where lastName = var_lastName AND firstName = var_firstName
INTO var_control;

select var_control as debug;

IF (var_control IS NULL) THEN

select max(directorID)+1 from director into var_id;

insert into director ( directorid, firstName, lastName)
values (var_id, par_firstName, par_lastName);

insert into moviedirector (movieID, directorID) values
( par_movieID ,var_id) ;

ELSE

insert into moviedirector (movieID, directorID)
values ( par_movieID ,var_control);

END IF;
END//
delimiter ;

As you can see I added the line "select var_control as debug;". I did this to get the value of the variable displayed on the screen. Anyway. This code should run fine. I know this, because I wrote a routine that did the exact same thing for a homework assignment. And for that database it works. However if I run this procedure unter MariaDB, it doe not work. Despite no syntax error, the statements between else and end if are never reached and, even stranger, the "select var_control as debug;" statement genereates no output on the screen, not even null!

As I said, my teacher stared at it for a solid 2 hours, finally he found the only difference between the code in my homework assignement and this exercise: the collation and caracterset of the database: For my homework, I used the collation latin1_swedish_ci, while at school we use utf8_general_ci.

Could this be the reason? Can the collation really have such a profound Impact? Has anyone of you ever run into thsi kind of problem?

Learn about AUTO_INCREMENT instead of using max(directorID)+1.

moviedirector is a many:many mapping table.  See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table for optimal schema for such.  (may get you some brownie points)

"procedure unter MariaDB, it doe not work" elaborate.

(I see 2 clues so far that Deutsch is your native language.  But your English is great.)

SELECT directorID FROM director -- What is director?  It is not declared.  Stored Routines are tricky.  There are parameters (you prefixed them with par_ -- good); there are database/table/column names; there are @variables; there are DECLAREd variables; there are aliases (you don't have any); there are loop names (none).  Using the same 'name' for two different uses can cause mysterious errors.

Collation differences either lead to explicit errors or slow performance due to inability to use indexes.  So I don't think it is a collation problem.

I'm not sure, but I think the entire procedure can be replaced by a single INSERT .. ON DUPLICATE KEY UPDATE .. statement.


PostgreSQL: Insert into JSONB key failing with syntax error at or near "->>" Running the following query auto-generated by Eloquent's upsert function is throwing a syntax error and I'm not sure why? I couldn't find a supporting statement that says PostgreSQL supports the following syntax, looking for some expert advice on whether this would work.
insert into "plugin_positions"
("created_at", "positions"->>"test", "slug", "tag", "updated_at")
values
('2021-04-10 17:30:40', 0, 'contact-for-telegram', 'rrss', '2021-04-10 17:30:40');

Here's the query that works (which uses the simple column name, and a valid json value):
insert into "plugin_positions"
("created_at", "positions", "slug", "tag", "updated_at")
values
('2021-04-10 17:30:40', '{"test":0}', 'contact-for-telegram', 'rrss', '2021-04-10 17:30:40');

Does postgresql allow inserting into a table if we specify the column as "positions"->>"test"?

The reason is that the attributes of a JSON column are not database columns, so you cannot use them in the SET clause of an UPDATE statement.
You can only replace the complete JSON with an UPDATE statement.
It might be a good idea to use regular "scalar" database columns instead of lumping several attributes together into a JSON if you want to modify them individually.


MySQL Script to automate creation of audit (history) tables and triggers? For regulatory and fraud requirements, we need to record every change to most of the tables in the DB.  The way we did this very successfully with a previous project is to have a copy of each table, identical except that:

4 additional columns: DateChanged, UserId, Action (Create, Update, delete), and IP
The Id pk no longer is unique.
all other constraints (FK, unique indexes etc) removed.
the audit tables are in a separate audit schema

At a previous job, the Oracle DBAs wrote a script to automatically generate all this, it did the following:

Created a new schema called audit if not existing
Iterate over each table t in the normal schema:

created a new table in the audit schema with the same table name except prefixed it with a_, e.g. a_t
Added all the same columns as the original table, plus the 4 additional columns (DateChanged, UserId, IP Address and Action)
generated and added triggers to the original table (if not already there) to:
If updating, write a new row to the corresponding a_ table, with action of "Update" containing the pre-updated (old) values in all the columns (the main table will have the new values)
If deleting, Add a row which is a copy of the main tables row, but action = "Delete".
If inserting, add the row to the audit schema

NOTE:

All tables have a ID PK.
A few tables need to be excluded for performance or because not needed (e.g. the balance table which is updated by a trigger)

The beauty of this system is you can query what changed and when, or who made changes to what, and see the record before and after the change.  changes are at the DB row level, not the individual column level.

Has anyone come across something like this for MYSQL 5.6?  We don't have a DBA on our team who could write something like this from scratch, but we know enough to modify something similar.

I just wrote this earlier today.  It's a select statement working off the information_schema database, which produces the schema for the audit tables and the triggers.

SET GLOBAL group_concat_max_len = 1000;

SET @dbName = "[[[your_db_name_here]]]";

SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r",
          "CREATE TABLE `", @dbName, "`.`", table_data.audit_table, "`\r",
          "(\r",
          "  `auditAction` ENUM ('INSERT', 'UPDATE', 'DELETE'),\r",
          "  `auditTimestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,\r",
          "  `auditId` INT(14) AUTO_INCREMENT,",
          column_defs, ",\r"
          "  PRIMARY KEY (`auditId`),\r",
          "  INDEX (`auditTimestamp`)\r",
          ")\r",
          "  ENGINE = InnoDB;\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.insert_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.insert_trigger, "`\r",
          "  AFTER INSERT ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('INSERT',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.update_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.update_trigger, "`\r",
          "  AFTER UPDATE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('UPDATE',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.delete_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.delete_trigger, "`\r",
          "  AFTER DELETE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('DELETE',", table_data.OLDcolumn_names, ");\r\r"
)
FROM (
   # This select builds a derived table of table names with ordered and grouped column information in different
   # formats as needed for audit table definitions and trigger definitions.
   SELECT
     table_order_key,
     table_name                                                                      AS db_table,
     concat("audit_", table_name)                                                    AS audit_table,
     concat(table_name, "_inserts")                                                  AS insert_trigger,
     concat(table_name, "_updates")                                                  AS update_trigger,
     concat(table_name, "_deletes")                                                  AS delete_trigger,
     group_concat("\r  `", column_name, "` ", column_type ORDER BY column_order_key) AS column_defs,
     group_concat("`", column_name, "`" ORDER BY column_order_key)                   AS column_names,
     group_concat("`NEW.", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
     group_concat("`OLD.", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names
   FROM
     (
       # This select builds a derived table of table names, column names and column types for
       # non-audit tables of the specified db, along with ordering keys for later order by.
       # The ordering must be done outside this select, as tables (including derived tables)
       # are by definition unordered.
       # We're only ordering so that the generated audit schema maintains a resemblance to the
       # main schema.
       SELECT
         information_schema.tables.table_name        AS table_name,
         information_schema.columns.column_name      AS column_name,
         information_schema.columns.column_type      AS column_type,
         information_schema.tables.create_time       AS table_order_key,
         information_schema.columns.ordinal_position AS column_order_key
       FROM information_schema.tables
         JOIN information_schema.columns
           ON information_schema.tables.table_name = information_schema.columns.table_name
       WHERE information_schema.tables.table_schema = @dbName
             AND information_schema.columns.table_schema = @dbName
             AND information_schema.tables.table_name NOT LIKE "audit\_%"
     ) table_column_ordering_info
   GROUP BY table_name
 ) table_data
ORDER BY table_order_key
INTO OUTFILE "[[[your_output_file]]]"


Oracle alternative edition for Windows Server 2012 Does Oracle 11.2 support Windows Server 2012? Any alternative for Windows Server 2012 with respect to Oracle editions?

I checked in internet but their forums, and this question says its not currently released for Windows Server editions.

Any alternative Oracle editions for Windows Server 2012 64bit?

You'll find that information the the Oracle reference document 1307195.1: Certification Information for Oracle Database on Microsoft Windows x64 (64-bit).

The latest Windows server version where 11.2 is supported is Windows 2008R2. Oracle 12c (12.1) is supported on Windows 2012 (including Hyper-V guests), but only in single instance mode (no RAC).

You can also find that information in the install guides, e.g.:

11.2 install guide, software requirements - Windows 2012 not listed
12.1 install guide, software requirements - Four editions of Windows 2012 x64 listed (note that the Server Core edition is not supported).


What's the maximum acceptable latency if using a remote MySQL DB server? Aside from single server optimization, I am making evaluation in splitting up an existing web application to separate WEB and DB servers.

I understand network latency is an important factor when using a remote database server.

Ideally I would place a dedicated DB server in the same DC as the WEB server or even with a private network.

However, if I really need to put it on a different DC, what's the maximum acceptable network latency in order to get a responsive connectivity?

Update:

In the end, I want to ask, if I absolutely need to deploy remote database, what's the best practice and things to consider when it comes to choosing the datacenter and infrastructure?

There is no single correct answer to this question.  The MySQL client/server protocol has no limitations on toleration of round trip time, but responsiveness of your application can be dramatically affected.

Fundamentally, though, you can't look at any number and know intuitively that it will work, without intimate familiarity with your application.

I recently had to migrate a legacy application piecemeal from on-premise to cloud -- database first, then the app tiers.  The round-trip time between the two sites was 23ms.  "Cake," I thought... "23 ms is no problem."

Unfortunately, it was a problem, because the original developers had succumbed to one of the fallacies of distributed computing: "The latency is zero."

It was a 3-tiered app, and investigation of multi-second page loads revealed that every method call to the application tier on each page on the site triggered a minimum of 13 round trips to the database.  GetUserAttributes, GetAccountBalance, GetProductListings... you get the idea... so here's a simple page making 39 largely unnecessary database requests and requiring ~750ms just in transit time, instead of just a few.  (Why so many? They historically used two different ORMs and were still setting up both for each method, redundantly calling USE database and SET autocommit, etc.)  This is an extreme example, but on a LAN, this is the kind of thing you get away with, but that causes your performance to be impacted exponentially as RTT increases.

Where you can achieve hundreds of round trips per second on a LAN, that number drops to about 40 with the 23 ms added in.  That could easily be a factor of 10 or more, even though by human reckoning, 23 ms seems very quick.  Even single-digit round-trips can have a counter-intuitively significant impact on performance.

Fortunately, there's a fairly easy way to simulate latency to a MySQL server in an absolutely meaningful way -- by not simulating it, but actually injecting physical latency: configure a transparent TCP proxy in a remote data center.  For example, HAProxy.  Simple sample config:

listen mysql
  mode tcp
  bind *:3306
  server mysql-server 203.0.113.1:3306
  timeout tunnel 28800s

This overlooks features you'd want to use in a permanent deployment, like health-checks and ACLs, but if you set this up on a test machine in the remote data center, connect to it on port 3306, it would hairpin the connection back to the MySQL server at 203.0.113.1.  HAProxy is payload agnostic in TCP mode, and it's just forwarding packet payload (using splice where available, to avoid much time in userspace) so it will contribute no meaningful latency itself (and can handle a surprising amount of traffic on tiny machines).  If your app and db are local and this proxy is remote, this will perfectly model what your application behavior will be like with double the round trip time between the two data centers (because app to db, db to app traverses the entire path twice, not once)... the simulation is a worse than worst case scenario.

If this performance proves acceptable, your proposed round trip time is no problem.  If it isn't, you found the problem before it was too late.


If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL? If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp table created in the session that executes sp_ExecuteSql.

In other words, why does this work:

SELECT 1 AS TestColumn
INTO #TestTempTable

DECLARE @DS NVARCHAR(MAX) = 'SELECT * FROM #TestTempTable'
EXEC sp_EXECUTESQL @DS

Results:

My understanding for the reason why I can't do the opposite (create the temp table in Dynamic SQL and then access it outside the dynamic SQL query in the executing session) is because sp_ExecuteSql executes under a new session.

From an answer by Remus Rusanu:

  Dynamic SQL runs in the same session as the calling code. The issue is
  not session, but scope. Your dynamic SQL creates the temporary tables
  inside the sp_executesql call and thus the created #temp table is only
  visible within that sp_executesql call, as documented in MSDN:

You can validate that you are using the same session in the sp_executesql  call by running:

DECLARE @DS NVARCHAR(MAX) = 'SELECT @@SPID'
EXEC sp_EXECUTESQL @DS

Knowing that sp_executesql runs under a different scope but not a different session, adding information found in the docs on temporary tables:

  Temporary tables are automatically dropped when they go out of scope,
  unless explicitly dropped by using DROP TABLE:

And that same source on the visibility of temporary tables:

  Local temporary tables are visible only in the current session

These differences in scope vs. session explain why you are able to access the temporary table from the sp_executesql call but not the other way around.


How to reduce fragmentation of nonclustered index on date partitioned log table Looking for some advice before I follow my gut on this and rebuild the nonclustered index to lead with our partition key. This is an insert-only table which is never updated or deleted, we keep a sliding window my truncating/merging periods on the left and adding new periods on the right.  The nonclustered index is constantly at 99% fragmentation due to the makeup of data we are inserted (many unique accountids and inventoryids).  I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation or maybe I shouldn't worry about the fragmentation?
I'm aware that our table doesn't have an explicit unique index, we rely on the [UNIQUIFIER] that is added to changedAt automatically.
Usage pattern: Table is always queried with a date filter (ChangedAt)
Partition function:
CREATE PARTITION FUNCTION pf_Weekly_QuantityHistory (datetime2(2)) AS RANGE RIGHT FOR VALUES ( '01 Jul 2019','08 Jul 2019','15 Jul 2019',etc )

CREATE PARTITION SCHEME [ps_Weekly_QuantityHistory]  AS PARTITION [pf_Weekly_QuantityHistory]
ALL TO ( [ExampleFG] );

CREATE TABLE [dbo].[QuantityHistory](
    [AccountId] [int] NOT NULL,
    [InventoryID] [int] NOT NULL,
    [QuantityBefore] [int] NULL,
    [QuantityAfter] [int] NULL,
    [ChangedAt] [datetime2](2) NOT NULL DEFAULT (getutcdate())
) ON [ps_Weekly_QuantityHistory](ChangedAt)

CREATE CLUSTERED INDEX [CX_QuantityHistory] ON [dbo].[QuantityHistory]
(
    [ChangedAt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE)

CREATE NONCLUSTERED INDEX [IX_AccountId] ON [dbo].[QuantityHistory]
(
    [AccountId] ASC,
    [InventoryId] ASC,
    [ChangedAt] ASC
) INCLUDE(QuantityBefore,QuantityAfter) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE)

I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation

You've got the partition key in the nonclustered index, so it's on the partition scheme (by default), and only the head partition is going to get inserts and additional fragmentation.
So you can rebuild the older partitions rarely and they won't get fragmented, and rebuild the index for just the head partition on a more frequent basis.
But for an index with insertions spread across the sort order you'll always generate fragmentation.  And fragmentation doesn't always matter.  It really depends on the storage design and the workload.
Also you're keeping two complete copies of this table, one sorted by ChangedAt and one sorted by (AccountId,InventoryId,ChangedAt).  You can probably just make the (AccountId,InventoryId,ChangedAt) index the clustered index, and only store the table one time.


Creating an Updating trigger I have to create a trigger that calculate the the quantity in stock and this is my first one on PostgreSQL.

When entering the quantity bought at a Provider (Fournisseur) or a delivery at a client, the quantity has to be updated.

I tried:

CREATE OR REPLACE TRIGGER CalculQStockProduit(String)
    WHEN UPDATE QuantiteEnStock ON Produit
         BEGIN QuantiteEnStock = QuantiteEnStock-Quantite
         OR    QuantiteEnStock = QuantiteEnStock+Quantite

I know this wrong, not enough accurate. Can you help me learning how to create an accurate and right trigger?

The related database scheme in UML is (in French, unfortunately):

In PostgreSQL you have to create a trigger function that will perform the calculations and the use it in a trigger.

Example:

Create or replace function CalculQStockProduit() returns trigger as $$
begin;
 update QuantiteEnStock set NEW.QuantiteEnStock = NEW.QuantiteEnStock-NEW.Quantite;
return NEW;
end;
$$ language 'plpgsql';

Create trigger trgCalculQStockProduit after update on Produit
for each row execute procedure calc();

I would refer to trigger function in postgresql manual for better understanding

This is just a sample so you will have to work out the calculations needed.

PG Trigger functions

Choose according to your postgres version.

I have used plpgsql as language but there are various options. plpgsql, sql, plv8 etc etc


How wrong is my web app modeling schema? So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years now, but never had any theoretical education, so I'm totally ignorant about database best practices, and would really like to learn more.

The problem is as follows: the application is going to be a very basic CMS that allows to store blog posts, events, photo-galleries, and other various items. Now all those items share the same attributes (more or less, but let's say they do) which are user_id, title, date, content. Furthermore, they all can be "commented", "favorited", "shared", etc. And finally, they can be linked to each other (a blog post can refer to an event, which has pictures, etc.). Because of that, I am tempted to just create one table called "items" and have a "type" field to differentiate the items. The consequence would be to have only one "join" table for each action: "users_comments", "users_favorites", etc. and a join table for item connections between each other. Ultimately, there could be more item types in the future...

I kind of feel like this is a bad lazy way to go, mostly because of performance, but I really would like to have your opinion on this. Does the lock effect will render the site totally useless as multiple people will try to read and write those "items"? For what it's worth, I'm using MySQL with MyIsam, and CakePHP.

Beyond this question is the fact that I'm not used to large scale applications and always wonder about the performance. I just read about 3NF and am going to keep on learning those things, so any reading hints would be greatly appreciated.

Seriously look at third normal form.  I would use surrogate keys with the natural keys implemented as unique keys. You will likely find that author belongs in its own authors table.  You may find you have a few tables which are quite similar such as user_content_faves, user_author_faves, user_author_shares.  This is normal.

Having a single content table with a content_type column may be appropriate.  The content column would need to be capable of storing all the content types.

EDIT: For relationship tables I usually name the join table by concatenating the names of the joined tables, abbreviating as necessary. If there multiple relationships between the two tables, I use one of two options:

Append the purpose of the relationship (as I did above); or
Add a type/reason code to the relationship (in which case the type is not needed in the relationship name).

I made the assumption that you would want to track who favorited or shared things.  It appears that both the producer (author or user) as well as the content items.  Therefore you have users favoriting a producer (user_author_faves now user_user_faves), or a product (user_content_faves).   Depending on how you do sharing,

it could be an attribute on the content, or
a relationship like user_content_shares, where content is share with a particular user.  Re-sharing could be problematic, if you track who shared things, and multiple users share the same content to the same user.  Un-sharing re-shared items is problematic if you don't track who did the sharing.

You may want to consider (and set policies for):

tracking/audit information like when something was done (added, favorited, shared, etc).
whether to do physical or logical deletes.
if you do logical deletes how to handle refavoriing or sharing something after a logical delete.

When indexing relationship tables I generally have the primary key consisting of the primary keys of the two tables being joined.  A second index with the primary keys reversed, or just the primary key which is the second column in the primary key is usually required.  If the relationship between two rows can occur more than once, the column(s) used to differentiate the reason/type and/or timing (since date) of the relationship needs to added to the primary key.


SSRS2016 external parameters for mobile report How can external parameters be passed to a SSRS 2016 mobile report in order to pre-select values / settings for the report?

The same scenario is possible for paginated reports as it always was. It's one of the key functionalities.

In my scenario I need to integrate the reports into an existing application. So some settings like USERID or LANGUAGE must be passed to the integrated report to initialize it correctly. It's not tolerable to expect a user of the application to choose a language every time he/she runs a report inside of the application...

Besides setting parameters for the datasets within the mobile report I could not find any option to take values from external / url...?
Any solutions?

Microsoft help suggested:

.../<report-name>?<dataset-name>.<field-name>=<parameter-value>

Its seems SSRS does support parameters in the url,
here is a starting point


Is a "Ranking" query's ORDER BY in FROM subquery is preserved? I've read in the following links that the ORDER BY in FROM subquery can be ignored.

Quoted from MariaDB knowledge base

A query such as

SELECT field1, field2 FROM
(SELECT field1, field2 FROM table1 ORDER BY field2 ) alias

returns a result set that is not necessarily ordered by field2.

So, given the following query:

SELECT CASE
         WHEN   @prev = x.city_id
            THEN @a
         WHEN @prev := x.city_id
            THEN @a := @a + 1
         END
         `rank`
FROM
(SELECT @prev := NULL, @a := 0) y,
(
    SELECT *
    FROM sakila.address a
    ORDER BY a.city_id
) x

Will the rows returned from the FROM subquery be in the order specified in the ORDER BY clause?

Edit:

How about the query below, will the x.city_id be ignored or not?

SELECT ....
FROM (
    SELECT CASE
           WHEN   @prev = x.city_id
              THEN @a
           WHEN @prev := x.city_id
              THEN @a := @a + 1
           END
             `rank`
    FROM
        (SELECT @prev := NULL, @a := 0) y,
        sakila.address x

    ORDER BY x.city_id
)
ORDER BY another_order_by_clause

How I solved it.

After looking through the answer and feedbacks from @ypercube and @jkavalik, I decided to explicitly create a temporary table for each inner query that I had.

Reason being as pointed by them, adding LIMIT by a huge number or other hacks might not work in every version of MariaDb or MySQL.

Performance was still good for the query that I did.

e.g:

The second query above is changed to

   CREATE TEMPORARY TABLE temp1 SELECT CASE
                   WHEN   @prev = x.city_id
                      THEN @a
                   WHEN @prev := x.city_id
                      THEN @a := @a + 1
                   END
                     `rank`
            FROM
                (SELECT @prev := NULL, @a := 0) y,
                sakila.address x
            ORDER BY x.city_id;

    SELECT ....
    FROM temp1
    ORDER BY another_order_by_clause;

As the article explains: No, the internal ORDER BY will be recognized by MariaDB's optimizer as redundant and will be "optimized away". If you want a result set returned in a specific order, use ORDER BY - in the most external level of the query.

The solution is to use ORDER BY in the external query. The x subquery (derived table) can be removed and replaced by a simple sakila.address x (that's what the optimizer will recognize and do anyway):

SELECT CASE
         WHEN   @prev = x.city_id
            THEN @a
         WHEN @prev := x.city_id
            THEN @a := @a + 1
       END
         `rank`
FROM
    (SELECT @prev := NULL, @a := 0) y,
    sakila.address x                    -- remove the derived table

ORDER BY x.city_id ;                    -- add ORDER BY in the external query

About the second query, yes, I think the ORDER BY x.city_id will be ignored, too. The answer in the linked question is 6 years old and is accurate but it's for MySQL (and the versions available then). MariaDB recent improvements on the optimizer make this kind of subqueries with variables, sometimes to fail because the ORDER BY is "optimized away".


Add range of values under a column using rowid How do i add range of values in a column based on row id?
For example: I have to add all the values under column "time" where rowid>=1 and rowid<=5.

Additionally is it possible to divide the then obtained sum by 3600?

Technically, you could use Oracle's SUM function (good explanation here: http://www.techonthenet.com/oracle/functions/sum.php). Your code would look something like this:

select sum(time)/3600
  from table_name
 where rownum >= 1 and rownum <=5;

However, without knowing more about the table, I don't know if the TIME column is simply an integer, or is a date/timestamp field. If it is date/timestamp, SUM won't really do what you need it to.

One other word of caution, using rownum doesn't always return the same results between runs. Please take a look at http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html for a much more succinct explanation than I can provide.


Database reporting more space reserved than total size I have a database which has four data files and a log file. The data files are each around 5 GB, the log is about 1 GB. The database reports the total space reserved as around 21 GB, and of that 18 GB is in use. This information is corroborated by:

Disk Usage Report
Database Properties dialog (in Management Studio)
sp_spaceused
sys.database_files
The data files on disk

However, the Disk Usage Report and sp_spaceused are both reporting that there is around 61 GB of data file space reserved, leaving around 43 GB as "unused" (i.e. not containing data). This should be impossible as the total space reserved is only 21 GB.

This phantom reserved space certainly isn't on the hard drive, so it must be a case of incorrect data somewhere in SQL Server. Where is it and how do I restore normality?

Looking at the code behind sp_spaceused, it references a system table called sys.allocation_units which stores statistics about how big each object in the database is. In particular, the total_pages and used_pages columns relate to the reserved and used disk space reported by sp_spaceused. The following query can help to pinpoint which objects are reporting the excessive reserved space:

SELECT
    o.name,
    a.total_pages * 8 / 1024. AS 'Reserved_MB',
    a.used_pages * 8 / 1024. AS 'Used_MB'
FROM sys.allocation_units a
INNER JOIN sys.partitions p
    ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.all_objects o
    ON o.object_id = p.object_id
ORDER BY a.total_pages - a.used_pages DESC;

Once a table has (or some tables have) been identified, their statistics can be updated by running one of:

-- Update the whole current database
DBCC UPDATEUSAGE(0);

-- Update the whole database
DBCC UPDATEUSAGE(DATABASE_NAME);

-- Update a single table and its indexes
DBCC UPDATEUSAGE(DATABASE_NAME, 'TABLE_NAME');

-- Update a single index within a table
DBCC UPDATEUSAGE(DATABASE_NAME, 'TABLE_NAME', 'INDEX_NAME');

-- Update the whole current database and run sp_spaceused
EXEC sp_spaceused @updateusage = 'true'

The report should now correctly report (or be closer to correctly reporting) the true reserved and used disk space for the database.

More information is available at MS Docs on DBCC UPDATEUSAGE and sp_spaceused.


Super slow (day long) count(*) after upgrade to 5.6 After upgrading to Percona 5.6.20 from 5.5.34 I noticed counts on larger tables take a massively long time to execute.

The table is about 27G and 300M rows.  It is not compressed and was innodb before the upgrade as well.

Before a count(*) on that table would take maybe 3 minutes.  Now, it's consistently taking over a day to run.  The last one I actually let spin until it finished took 36 hours.

The DB is on the same hardware as before the upgrade.  Buffer pool size remained the same (20G).

One config change was increasing the number of buffer pools from 1 to 8.

Has anyone ran into this before?  Are there any new 5.6 cnf settings that might be related to this?

Table schema in question

CREATE TABLE `largetable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`char1` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`int1` int(11) NOT NULL,
`int2` int(11) NOT NULL,
`char2` char(2) NOT NULL,
`mydate` date NOT NULL,
PRIMARY KEY (`id`),
KEY `char1` (`char1`(8))
) ENGINE=InnoDB AUTO_INCREMENT=296639951 DEFAULT CHARSET=latin1;

Here's a pt-config-diff of a "good" 5.5 server and the newer 5.6 server http://pastebin.com/EM22WhBR

It appears to be a change in the optimizer.  I haven't tracked down the exact setting that caused this yet but the explain on the 5.5 version was showing it using the Primary Key.  On 5.6 It was using the secondary char1(8) index.

Adding force index (primary) got it back to it's 3-5 minute count time.


How large is "too large" for SQL 2008 Web Edition? For our soon-to-be-hosted application I need to choose between SQL Server 2008 Web edition and SQL Server 2008 Enterprise edition. There isn't any "must-have" functionality: the application currently runs on SQL Express, but it will exceed the 10GB maximum size that SQL Express imposes.

I'm looking at this comparison chart, and the most significant table in this page "Scalability & Performance". As tables get larger, functionality like "Table and Index Partitioning" would be vital to guarantee performance speeds. But at what table sizes would this be required?

What strategies should I use to determine if the size and usage of my database requires the Enterprise Edition Scalability and Performance functionality?

This is a very tough question to answer and the only correct thing to say is "it depends". Yes, it's trite but that doesn't make it untrue. That being said I'll try my best to give you an answer (use Standard) and explain the thought process behind it.

I'm not familiar with the "Web" Edition of SQL but I can certainly rattle off the differences between Standard and Enterprise. At the risk of a massive generalization I'd say that if you're looking at between 10 and 100GB for a single DB and you're not sure you need Enterprise features, the upfront cost difference combined with the ease of upgrading the SKU (Edition) if/when you need to mean that choosing Standard over Enterprise probably makes the most sense. I'm guessing that "Web" edition is some semi-stripped down Standard version and personally I'd avoid it based on the "Web Workloads Only" disclaimer. What does that even mean?!

So how does one truly determine if they'll need Enterprise features? Well, the best way I can think of is to download the Standard and Enterprise trials (or get an MSDN license) and do performance testing with both editions. If you find you can't hit/exceed your performance targets with the lower edition identify the bottlenecks and then see if the Enterprise features would assist. If so, upgrade & make the needed changes to use the features then re-test. In my experience the main features one makes use of in Enterprise are (in order of most common to least):

online reindexing
row/page compression
backup compression
filtered indexes
partitioning & partitioned indexes
indexed views
parallel index operations

And finally... When moving up from something that's running well on SQL Express I don't think there would be many cases that justify the jump to Enterprise. Alone, the additional system resources that the full version of SQL can use should deliver big gains.


Count all products (with qty > 0) group by all brands. But get aslo count zero for brand which have 0 products I have table:

brands

brand_id  name
1         Apple
2         Sony
3         Nokia
4         HTC

products

id  brand_id qty
1   1        3
2   3        0
4   1        1
2   2        1

I need to get (all brands from brands table and products.qty > 0)

brand_id   count_products
1          2
2          1
3          0
4          0

I tried

SELECT `p`.`brand_id` AS `id`,
       COUNT(p.brand_id) AS COUNT
FROM `products` AS `p`
WHERE AND `p`.`qty` > 0
GROUP BY `p`.`brand_id`

But I get results only for brands that available in products_table.
Thanks for help.

SELECT b.brand_id,
       COUNT(p.brand_id)
FROM brands b
LEFT JOIN products p ON b.brand_id = p.brand_id
                    AND p.qty > 0
GROUP BY b.brand_id


How can I optimize a query where a IN clause is replaced with multiples UNION ALL? I have a query like this:

select *
from slow_view
where id = 1
union all
....
union all
select *
from slow_view
where id = 1000

-- 50 ids, exec time: 10.615 sec,

This would run in a very acceptable time if it was replaced with:

select *
from slow_view
where id in (1, ...., 1000)

-- 50 ids, exec time: 1.3 sec,

or

with slow_view_cached as (
select * from slow_view)
select *
from slow_view_cached
where id = 1
union all
....
union all
select *
from slow_view_cached
where id = 1000

-- 50 ids, exec time: 1.83 sec,

But unfortunately it is generated by an ORM and I don't have a chance to modify it. It's quite bad as an ORM, I don't think I can do much to improve its behavior.

Is there something I can do to my query or to my view to make it "cached" for all the subqueries in this case? I know I could use a materialized view but it doesn't fit my case very well.

AFAIK in Postgres it's not possible to define indexes on a view, so id is not indexed or unique.

Is there anything else I can try?

I'm using Postgresql 9.5 (but if you have a solution for PG 9.6 or even PG 10 it would be acceptable, since I plan to update very soon).

The code is a bit too complex to post here. In essence this query is generated in the following case. slow_view is an association of a Model m. What I do is basically m.find().populate(slow_view, criteria); The documentation is here.

criteria is excluded from the query for the sake of simplicity, id is the foreign key from _slow_view_ to Model.

I could call a stored procedure from this ORM, but it would mean altering the application a bit too much.

if slow_view don't have too many records, you can consider fetching all the records to application and apply the filter in the application side result set.
This approach helped me in a specific case to reduce the database load.


Arrange SQL Server rows in a paired sequence I have a database table in which a row has its matching pair. Checkpoint A has a matching pair checkpoint B. Every IN status has a matching OUT status. To match these exists a 'related_id' column with the id of the row it is related to.
Normally the table order can be as follows:

id | registration_no | checkpoint | related_id | status
 1      H2080              A            2          IN
 2      H2080              B            1          OUT
 3      H2081              A            8          IN
 4      H2070              A            6          IN
 5      H2087              A            7          IN
 6      H2070              B            4          OUT
 7      H2087              B            5          OUT
 8      H2081              B            3          OUT

but instead i would like it to be ordered in this way

id | registration_no | checkpoint | related_id | status
 1      H2080              A            2          IN
 2      H2080              B            1          OUT
 3      H2081              A            8          IN
 8      H2081              B            3          OUT
 4      H2070              A            6          IN
 6      H2070              B            4          OUT
 5      H2087              A            7          IN
 7      H2087              B            5          OUT

in which the leading checkpoint A is immediately followed by the matching checkpoint B by somehow using the related_id to tell which columns are paired together. So the rows will alternate between A->B then A->B and so on.

I hope i managed to explain the question.

Any help is greatly appreciated. thank you

You can use this ORDER BY:

SELECT  ...
FROM ...
ORDER BY
    CASE checkpoint WHEN 'A' THEN id
                    WHEN 'B' THEN related_id
    END,
    checkpoint ;


What is the meaning of "dimension key"? I am a beginner trying to understand the basic concepts of database's. I know several types of keys (Primary key, Foreign key, candidate key, super key, surrogate key.....etc). While learning about the degenerate dimension, I came across "dimension key".

  In a data warehouse, a degenerate dimension is a dimension key in the fact table that does not have its own dimension table        ----Ralph Kimball

Could someone please helps me understand the dimension key ?

A dimension key is the primary key of a dimension table.

In the example below, the "Time_key" column is the dimension/primary key of the Time dimension. This column will be used to join the dimension with fact tables, just like any other key.

in https://www.kimballgroup.com/1997/07/its-time-for-time/

A degenerate dimension is, however, a different and slightly more advanced concept to model. In the degenerate dim case, a fact table contains a foreign key that, by design, does not link to a dimension.


Abbreviations in full text search index I have a text column containing various words that have been shortened into abbreviations.  For example, instead of "insured", the column might contain "insd".  I understand I can use a thesaurus file to create a list of synonyms, effectively allowing a search for "insd" to return rows that contain both "insured" and "insd".  Perfect.

However, some of the other abbreviations in the column contain "special" characters, like a slash or an ampersand, such as:

t/p - third party
o/s - other side
p/p - per person
i&o - inside and out

Is there some way I can make CONTAINS or FREETEXT understand queries containing these words?  For instance:

SELECT *
FROM dbo.MyTable
WHERE FREETEXT(MyColumn, 't/p');

I've created the thesaurus file, tsenu.xml in the correct location for the instance:

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            insured
            insd
        </expansion>
        <expansion>
            t/p
            third party
        </expansion>
        <expansion>
            o/s
            other side
        </expansion>
        <expansion>
            p/p
            per person
        </expansion>
    </thesaurus>
</XML>

And loaded it using EXEC sys.sp_fulltext_load_thesaurus_file 1033;, however the query returns unpredictable results.

When you want to add words involving special characters that you want treated as a single word, you will want to create a custom dictionary.

Creating Custom Dictionaries for special terms to be indexed ‘as-is’ in SQL Server 2008 Full-Text Indexes

Be aware that custom dictionaries are limited in size, plus the larger the dictionary the slower fulltext can become.

Custom dictionaries are great when you have a limited set of words to add like AT&T or M&Ms which are words with special characters that might be used over and over that you wanted treated as a single word instead of the default word breaking behavior.

Custom dictionaries are not great for for words like part numbers (ex. JY-14562, PTW-14432, etc.) where every row in a table would result in a new entry in the custom dictionary. You could potentially have several hundred thousand or more unique part numbers. That would not be a good use for custom dictionary.  If you need this type of behavior, you are better off writing your own word breaker. Creating word breakers is not well documented nor easy to create, but it does allow you to decide how you want words broken when you encounter special characters.


MS SQL server 2016 backwards compatibility Are there any reported compatibility issues between level 130 to 100?

Yes there are reported compatibility issues (difference) between level 100 and 130. Instead of me copy and paste Microsoft document I suggest you consult this document.

ALTER DATABASE (Transact-SQL) Compatibility Level

I also suggest you list all the changes and test in your development (non prod environment) with simulating production load (covering all aspect of the application).

You might also look at Microsoft® Data Migration Assistant v3.1.

  Data Migration Assistant (DMA) enables you to upgrade to a modern data
  platform by detecting compatibility issues that can impact database
  functionality on your new version of SQL Server. It recommends
  performance and reliability improvements for your target environment.
  It allows you to not only move your schema and data, but also
  uncontained objects from your source server to your target server.

Nice article by Aaron Bertrand:

Bad habits : Clinging to old compatibility levels


Removing Instances and SQL Server Express edition and going to Standard/Developer/Enterprise I would like to move from SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer or Enterprise.

I just noticed that I have the following services running:

SQL Server (MSSQLSERVER)
SQL Server (SQLEXPRESS)
SQL Server (SQLR1)
SQL Server (SQLR1V2)
SQL Server Agent (MSSQLSERVER)
SQL Server Agent (SQLR1)
SQL Server Agent (SQLR1V2)

What I would like to accomplish here is to maintain the databases I have in (local). How can I tell which instance this is running against?

Also, have the Express Edition completely removed and install either the Developer / Enterprise edition of SQL Server with the Management Studio interface. I ultimately want to have SQL Profiler at the end of the day.

I don't mind if I have to remove everything services, instances, management studios, etc.

As long as I can end up with 3 things:

SQL Server Developer / Enterprise
SQL Server Management Studio
SQL Profiler

Edit:
I have downloaded SQL Server 2008 R2 Developer (x86, x64, ia64) - DVD (English) from MSDN. However, according to the installation I already have Management Tools - Basic installed.

Edit2:
I actually created a new instance and I was given additional the 2 Management Tools options in the installation, as mentioned by mrdenny.

(Local) without an instance name is the instance named MSSQLSERVER (the "SQL Server (MSSQLSERVER)" service).

If your only goal here is to get SSMS and SQL Profiler from the normal edition and not the express edition of SSMS that's easy, just install the tools from the Developer edition DVD / ISO.

Launch the installer, get through to the list of services.  Select only the management studio options (there are two, one under the other) and finish the wizard.  This will give you the tools and leave your instances alone.


SQL performances of selecting recently modified records Solr I have a SQL Server 2916 database, and Solr.
The Solr indexer runs often such queries:

select book_collection from books group by book_collection having max(updated_on) > '2017-04-04 09:50:05'

The column updated_on is updated by a trigger on insert/update; at every run of the Solr incremental indexer (every 10 minutes), many queries like the above get the latest modified records and reindexes them.
This table, for instance, has about one million rows and at every time the query would return 10-20 rows maximum.
These queries end up in the list of the most expensive queries run on the database, so I would like to optimize them.
My questions:

1) Would a timestamp column perform better than a datetime column?

2) If I changed the query like this, would it be more efficient?

select distinct book_collection from books where updated_on > '2017-04-04 09:50:05'

The first query plan is the original one, the second is modified by me. The fact that the second plan asks for an index, and the first one doesn't, really suggests that the modified query can use an index, if present, and is therefore better.

In SQL Server, TIMESTAMP does not represent a time, it's just a number representing the rowversion. So it's unlikely you can just change the datatype of that column for performance reasons.
Yes, that will probably perform better, because it can use the index over updated_on, something the MAX()-function prohibits in the original query. As far as I can see, the result should be identical.


How do I create a database+tablespace+user in Oracle 11gR2? Good evening,

I'm trying to figure out how to create a database+tablespace+user in Oracle 11g.

I've tried some tutorials, but nothing seems to work.

I cannot find Database Configuration Assistant either.

I'm running Windows 8 x64 with Oracle 11g R2, please tell me how I can do this task.

Thanks for all suggestions,

Alec Taylor

I had no problems with a VirtualBox 4.14 Windows 8 preview VM and x64 Oracle 11gR2.

My VM uses 40 GB Hard Disk. While installing it downloaded .Net Framework 3.51
I started with a fresh Windows 8 VM in VirtualBox 4.14 and installed guest additions and run Windows update once. Afterwards I installed Oracle and got the items below on my Start page.

I find the databasefiles of my new database Bk_W8 in C:\app\berndk\oradata\Bk_W8. That is the location I would use for additional tablespaces.

The rest can be done by scripts which can be executed by sqlplus from this machine or from another client and is not Windows 8 specific.

Edit:

By right-clicking on the item on the start page, via advanced | open file location I found the path to the start menu:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle - OraDb11g_home1\Konfigurations- und Migrations-Tools

I guess you will find

C:\ProgramData\Microsoft\Windows\Start Menu\Programs

the other is specific for a German Oracle installation.

To get access to the hidden C:\ProgramData folder see this link showing how to access folder options in Windows 8

Final hint: My VM currently uses 23 GB for the virtual hard disk.

OK I'm going to retry this with the Oracle Database Express Edition 11g Release 2 (September 2011). But that takes some time.

Installation done:
The is the Web interface of Oracle 11g XE in Windows 8

For the question of creating addition databaseinstances:
from this question on SO we learn, that it is not possible.

Creating tablespaces and users can be done using sqlplus. Which I found at C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe.

Now going to remove that Virtual machine. I think there are better ways to learn Oracle.


Moved site to new, similar server - same query is taking 10x longer? Edit:
Old server SHOW GLOBAL VARIABLES: https://pastebin.com/raw/8GPTpub0
And for the new (problematic) server: https://pastebin.com/raw/XnfbFzh7

This morning I moved my WP website from one server to another, similarly configured with MariaDB 10.5.8 on the old, and 10.5.9 on the new.
According to slow-query log, some queries are going very slow. Here is one.
SELECT DISTINCT a.id  FROM wp_bp_activity a
WHERE a.type IN ( 'activity_update','created_group','new_avatar' )
  AND a.is_spam = 0
  AND a.hide_sitewide = 0
  AND a.type NOT IN ('activity_comment', 'last_activity')
ORDER BY a.date_recorded DESC, a.id DESC LIMIT 0, 16;

It takes 5,6,7 seconds. Running the exact, same query on the old server, it takes 0.0002 seconds.
Here is an EXPLAIN from both servers:

Why is one "index" and the other "index_merge" using different keys? It's the same query on the same table.

Both tables are nearly identical, one was live earlier today so it has relatively updated information.
The OLD server DDL of the table:
CREATE TABLE `wp_bp_activity` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) NOT NULL,
 `component` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `type` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `action` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `primary_link` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `item_id` bigint(20) NOT NULL,
 `secondary_item_id` bigint(20) DEFAULT NULL,
 `date_recorded` datetime NOT NULL,
 `hide_sitewide` tinyint(1) DEFAULT 0,
 `mptt_left` int(11) NOT NULL DEFAULT 0,
 `mptt_right` int(11) NOT NULL DEFAULT 0,
 `is_spam` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `date_recorded` (`date_recorded`),
 KEY `user_id` (`user_id`),
 KEY `item_id` (`item_id`),
 KEY `secondary_item_id` (`secondary_item_id`),
 KEY `component` (`component`),
 KEY `type` (`type`),
 KEY `mptt_left` (`mptt_left`),
 KEY `mptt_right` (`mptt_right`),
 KEY `hide_sitewide` (`hide_sitewide`),
 KEY `is_spam` (`is_spam`),
 FULLTEXT KEY `content` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=1060622 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The NEW server DDL:
CREATE TABLE `wp_bp_activity` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) NOT NULL,
 `component` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `type` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `action` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `primary_link` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `item_id` bigint(20) NOT NULL,
 `secondary_item_id` bigint(20) DEFAULT NULL,
 `date_recorded` datetime NOT NULL,
 `hide_sitewide` tinyint(1) DEFAULT 0,
 `mptt_left` int(11) NOT NULL DEFAULT 0,
 `mptt_right` int(11) NOT NULL DEFAULT 0,
 `is_spam` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `date_recorded` (`date_recorded`),
 KEY `user_id` (`user_id`),
 KEY `item_id` (`item_id`),
 KEY `secondary_item_id` (`secondary_item_id`),
 KEY `component` (`component`),
 KEY `type` (`type`),
 KEY `mptt_left` (`mptt_left`),
 KEY `mptt_right` (`mptt_right`),
 KEY `hide_sitewide` (`hide_sitewide`),
 KEY `is_spam` (`is_spam`),
 FULLTEXT KEY `content` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=1060840 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The fast query avoids sorting the result by reading in date_recorded index order matching your ORDER BY. It filters the hard way, comparing your conditions in the WHERE clause to rows one by one, but once it gets enough matching rows to satisfy the LIMIT, it stops scanning the table.
Whereas the slow query tries to use indexes on a couple of columns, thinking this will help. But it ends up having to scan hundreds of thousands of rows to find those that match, then finds the set of rows that matched both index searches (that's the Using intersect note), and finally sorts the large set of rows the hard way, using a filesort, just to return 16 rows and discard the rest.
This is a good counter-example case that shows that searching by index isn't always the best strategy. But the MySQL cost-based optimzer is biased to think that searching by index is usually better.
In this case, you might want to use an index hint to make it choose the date_recorded index.


SQL server 2005 SP4 differential backup is failing We have an SQL server 2005 with SP4, x86. When we attempt to take the differential backup, we are getting the following error message.

  Cannot perform a differential backup for database "ABCD", because a
  current database backup does not exist. Perform a full database backup
  by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
  [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating
  abnormally.

Any help in resolving this issue is appreciated

A differential backup is a "changes only" backup, and therefore can only be done when there is a previous full backup to work from. If you create a new database (or attach one from another server), and don't take a full backup, there no "original backup" to get changes since.