Store Mesh in database I hope this is the correct forum but will ask anyway:

I have lots of Mesh data I want to store. Mesh is actually triangles. I hope it's not too of beginner question but wouldn't Geometry type of SQL Server be the best way to store that?

Specifically I'm interested in storing FBX file that are created in other programs sometimes in different formats such Autodesk Revit then converted to FBX. My other option is use file system which is risk and not managed.
Or using binary field but storing large binary data in a database rarely ends well I understand.

This question is not about how to actually load the data I know this could be a problem but what SQL datatype should I use.

I was planning to make the FBX files very small so that for example I can have lookup tables of types. Example: a door will always be a single FBX and then the table will have: FBXType FBXData 1 (door) 2 (furniture)

At the moment is just querying so I can build AssetBundles for Unity. Then I was thinking if it is geometry maybe I can use the smart indexes and functions like intersect that is available for geometry type.

The data will be changing that is the reason I want it in a database however it will probably be in deliveries (this one day of the month everything is updated) and probably new rows (will have another column IsDeleted) can even be a new table but not a must. If splitting by type my colleague says only few FBX files will be large for buildings skeleton but even this is not sure because we can split by floor. Most FBX files will be 5MB or less

Are you considering using SQL Server to query data inside the mesh? For example, are you trying to pass in a list of points, and then have SQL Server figure out whether points in that area match up to any of the mesh data for existing rows inside the database?

If so, you're gonna have a bad time - that's not what SQL Server is designed for.

If, on the other hand, you're just using SQL Server as a key/value store ("give me the mesh data for this desk design") then you can use any data type you like, like JSON or XML or BINARY. You're correct, though, by saying that this rarely ends well. If you're looking for key/value storage, then use a key/value data store instead.


Nested table hierarchy: foreign key duplication vs. join I have a nested table hierarchy, similar to the following one (this is an example but demonstrates the schema well enough):

+---------+  +---------+  +---------+  +-------------+
| users   |  | blogs   |  | posts   |  | comments    |
+---------+  +---------+  +---------+  +-------------+
| user_id |  | blog_id |  | post_id |  | comment_id  |
| email   |  | user_id |  | blog_id |  | post_id     |
+---------+  | name    |  | content |  | comment     |
             +---------+  +---------+  +-------------+

So, the object hierarchy is clear: a comment belongs to a single post, which belongs to a single blog, which belongs to a single user.

Now, in my app code, I want to verify that comments can only be read by the user who owns the blog which owns the post (assume these are private comments, ok?). E.g., when I SELECT * FROM comments WHERE comment_id = 666, I want to verify that the requesting user "owns" that comment.

My dillema is this: Would you advice that I:

duplicate the user_id to comments, so I always have a quick reference to the owning user, and checking the user id (during the select or later) is straightforward, or
Write my DB access code (a mapper or what have you) to always JOIN and fetch the user_id field as part of the returned object. Mind you that in this case I need two JOINs to do that

Basically I see it as performance vs. the so called bad practice of data duplication - but is it really such a bad practice in this case? It is unlikely that user_id values will ever change or that a blog changes its user_id.

I am using MySQL (MariaDB in fact) if that makes any difference.

You should go with second option obviously for many reasons:

Will you add user id again if you need to come up with an entity at same level of comment? May be it  won't happen in your scenario, but still.
Storage, as your database size grows
There won't be performance impact much as your query is highly selective and if you have good index design.


Incomplete MYSQL Dumps I am using MySQL dump to back up my MySQL Databases and I'm having trouble with a couple dumps on one box.

Server Specs
Windows 2008
MySQL 5.1.48-community

I've tried various parameters in the mysqldump command and not much seems to help.
mysqldump --user=%mysqluser% --password=%mysqlpassword% --max_allowed_packet=64M \n
    INSERTDBNAMEHERE INSERTTABLENAMEHERE > %basedir%\export.sql

What is odd, is if I do a dump out of phpmyadmin, it is fine.

Use the -v (or --verbose) option for mysqdump which can show the errors that occur


EXPDP snapshot too old live monitoring The likely errors that raises "snapshot too old"s during datapump export are well documented all over the internet such as :

undo_retention parameter being too short
redo logs groups are too small or there are too few
undo tablespace is too small

Is there a way to monitor those failure cases while the job is running ?

The kind of solution I'm looking for would be a query that would report :

total percentage of the snapshot's undo tablespace usage until it needs to auto-extend or maxes out
total percentage of redo logs usage until it overwrites itself
time left until undo retention is reached by the snapshot

Any query that does part of these things would be welcome. Does looking up such information requires oracle analytic packages? Is it possible at all to query such a thing ?

You need to fully understand how undo works to troubleshoot this error. I recommend this blog post:

http://blog.oracle48.nl/wordpress/oracle-database-undo-space-explained/

You can use this query to see the current profile of your Undo tablespace(s):

SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*)
    FROM DBA_UNDO_EXTENTS
    GROUP BY STATUS, TABLESPACE_NAME;

ACTIVE extents contain uncommitted or currently-rolling-back
transactions.
UNEXPIRED extents contain transactions required to be
kept in the Undo tablespace to meet the undo_retention parameter
EXPIRED extents are transactions still in the Undo tablespace that
are older than the undo_retention parameter.

The database will delete the oldest expired extents to make room for new active ones. If there are no expired extents, it will attempt to allocate a new extent. If that means extending the datafile, it will do that.

If the datafile cannot extend, the database will delete some Unexpired extents to make room, violating the undo_retention value. Thus, the undo_retention parameter is not a guarantee, but a guideline that the database will attempt to adhere to as best it can.

Now, there are two common failure modes here:

If a query tries to read from a deleted extent, it will fail with ORA-01555 Snapshot Too Old
If the whole undo tablespace becomes full of active undo, transactions will
fail with ORA-30036 unable to extend segment in Undo tablespace

  total percentage of the snapshot's undo tablespace usage until it needs to auto-extend or maxes out

If the sum of Active and Unexpired bytes in DBA_UNDO_EXTENTS is close to the size of the relevant undo tablespace, you have a chance of experiencing an ORA-01555.

If the sum of Active bytes is close to the size of the relevant undo tablespace, you are likely to experience an ORA-30036 error.

  total percentage of redo logs usage until it overwrites itself

I'm not aware of anything you can change in the redo log configuration that will effect Snapshot Too Old errors. Where did you get this info?

  time left until undo retention is reached by the snapshot

It is very difficult if not impossible to do this accurately, because it depends on every other transaction in the database. If you subtract the query running time from the undo_retention parameter, you will have a good idea, but that's only assuming that the undo tablespace was big enough to meet the undo_retention value. Once that line has been crossed, all bets are off.


Dedicated Resources on VMs hosting SQL Server My understanding is that one of the big benefits of using a VM is that you can share resources between the virtual machines on a host.  So you can have a host with 120 CPUs and put 5 VMs on it with 32 CPUs each.  The extra CPUs are "shared" between the VMs with the host assigning CPUs dynamically based on need.  Same for memory.

I also understood that this is a big no-no for VMs that house SQL Servers but my VM admins disagree.  Does anyone have any evidence or documentation one way or the other?

I don't know if it matters but we are using VMWare.

What you're asking is, "Is it okay if we overcommit CPUs?"

It's okay until you start experiencing performance bottlenecks that are related to CPU consumption. The same answer holds true with network overcommitment as well - it's not like you're putting 5 separate 10Gb Ethernet cards in the host, and dedicating one card per VM. Virtualization is all about overcommitment, and walking the line between resource availability and resource requirements.

Generally speaking, you don't want to overcommit on memory, though, since SQL Server will actively use whatever memory you present to it. Rather than letting the VMs page to the host's disk, you're better off doing VM-by-VM analysis to figure out which VMs could live with less memory, and then configuring them with less memory to begin with.


Can I rely on functions being executed first in SQL Please consider the following script:

create or replace function f(p_limit in integer) return integer as
begin
  set_global_context ('limit', p_limit);
  return p_limit;
end;
/

create view v as
select level as val from dual connect by level<=sys_context('global_context','limit');

select f(2), v.* from v;

/*
F(2)                   VAL
---------------------- ----------------------
2                      1
2                      2
*/

select f(4), v.* from v;

/*
F(4)                   VAL
---------------------- ----------------------
4                      1
4                      2
4                      3
4                      4
*/

Can I rely on f(x) being executed before the context is read inside the view, as it has been in this test case run on 10.2?

No.

If you re-write your view with the context filtering against the where clause (instead of the connect by), you'll get the previously set value for the context:

create table t as
 select rownum r from dual connect by level <= 10;

create or replace view v as
  select r val from t where r <=sys_context('global_context','limit');

select f(2), v.* from v;

F(2) VAL
---- ---
   2   1
   2   2

select f(4), v.* from v;

F(4) VAL
---- ---
   4   1
   4   2

select f(4), v.* from v;

F(4) VAL
---- ---
   4   1
   4   2
   4   3
   4   4

As the where clause is evaluated before the columns are selected, the value passed to the function isn't set until after the context is read. The location of the sys_context call in your query (select, where, group by, etc.) will affect exactly when this value is set.


How can I troubleshoot issues with dynamic registration? For testing I have been trying to install Oracle 11g on a test system with a single database several times with the aid of snapshots. This most recent iteration I have been having issues getting the database to register with the listener.

I am to understand that PMON, as long as the database is running, will attempt to register itself at regular intervals with a listener that is running locally on the same machine using port 1521. I have such a listener running but the service is not registered it seems.

lsnrctl status
.....
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.13.134)(PORT=1521)))
....
The listener supports no services

I can connect to the db using sqlplus / as sysdba and running select name from v$database; shows the DB in question. So between that tnsping and netstat -an I know the listener is at least running. alter system register does not appear to have an effect either. Not that it should matter since I should just have to wait a minute for it to work on its own.

I then went looking for any PMON related logs. It should have been in the trace folder ($ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_pmon_PID.trc) with other trc files but it was not there. I then used the following query to verify where it should be

select pid, program, tracefile from v$process where program like '%PMON%';
d:\app\administrator\diag\rdbms\prod\prod\trace\prod_pmon_1464.trc

Windows 2008R2 with firewall disabled for testing (not that it changed anything). Listener.log doesn't show anything of worry. Just evidence of me restarting the listener many times.

I am aware that I could configure a static listener with SID_LIST but I would like to understand why this is not working. This is the 4th time around that I have been doing this and I have not run into this problem yet.

I am basically stuck since I am not sure where else I should be looking.

Listener registration in the database can be traced with:

alter system set events='immediate trace name listener_registration level 3';

Disable tracing when it is not needed anymore:

alter system set events='immediate trace name listener_registration level 0';

PMON registers to addresses defined in local_listener and remote_listener parameters. If you have an invalid address defined there, then it will not be able to register the instance in the listener.

If you have nothing defined there, then it tries to register on the local machine using the default 1521 port.

You could also try to set your local_listener as:

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.13.134)(PORT=1521))';
alter system register;


Using a filtered index when setting a variable I know from other questions and posts that when SQL compiles a query plan it can only use a filtered index if the filtered index is guaranteed to be able to be used every time the query runs. This means you can't use a variable in the where clause because sometimes it might be able to use the filtered index and sometimes not.
One way around this is to use OPTION(RECOMPILE), so that the times it can use it, it'll get the filtered index.
Doing some testing, I found that this query can use a filtered index (note, I'm forcing the index just to prove a point):
SELECT MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

However, if I want to assign the result to a variable, I'm bang out of luck:
SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

results in:

Msg 8622, Level 16, State 1, Line 15 Query processor could not produce
a query plan because of the hints defined in this query. Resubmit the
query without specifying any hints and without using SET FORCEPLAN.

The query can clearly use the filtered index as it runs find when I don't want to save the output to a variable.
I have ways of rewriting this query to hard-code @variable to remove the problem, but can someone explain why the first query can use the filtered index, but the 2nd query can't?

The optimization that allows you to use the filtered index with a RECOMPILE hint is called the "parameter embedding optimization."  That's a process where the query parser replaces the variable reference with the literal value inside the variable.
See this post from Paul White for the reason why it doesn't work in your second case: Parameter Sniffing, Embedding, and the RECOMPILE Options

There is one scenario where using OPTION (RECOMPILE) will not result in the parameter embedding optimization being applied. If the statement assigns to a variable, parameter values are not embedded:


In oracle XE, what is the difference between the autobackup and backupset direcories? I am learning oracle db, and in Oracle XE, there are autobackup and backupset directories under flash_recovery_area/XE. In both directories, there are bkp files under the datestamped directoy.

What is the difference between the bkp files in  autobackup and backupset  directories?  In other words, what is the difference between the autobackup and backupset direcories? Thank you very much.

AUTOBACKUP, by default, contains automatic backups of the controlfile and the spfile (if the database has one), if the AUTOBACKUP feature is enabled.

rman target /
show all;

There you should see:

CONFIGURE CONTROLFILE AUTOBACKUP OFF# default

Or ON, if it is configured. With AUTOBACKUP enabled, the controlfile will be automatically backed up on backup operations, and on changing the structure of the database (e.g creating a new tablespace, adding a new datafile). In 10g it is performed immediately after changing the structure, starting with 11.2, it is performed a bit later (Controlfile Autobackup Deferral), allowing the execution of multiple structure changes without creating an unnecessary controlfile autobackup for each action.

BACKUPSET, well, that is where all other backups go, by default. Backups of the datafiles, archivelogs and controlfile and spfile backups if AUTOBACKUP is disabled, or created explicitly.


pg_dump permission denied for sequence I have a strange issue with dumping post-data section.

Dump command is:

pg_dump -Fc --verbose --section=post-data --no-owner --no-acl -h localhost -p 9999 -U root db_name -W > constraints.dump

Here is an error message:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation verification_code_user_mapping_id_seq
pg_dump: [archiver (db)] query was: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL      WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL      WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL      ELSE min_value END AS min_value, cache_value, is_cycled FROM verification_code_user_mapping_id_seq

But when I'm trying to execute this query in psql it works well:

db_name=>  SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL      WHEN increment_by < 0 AND max_value = -1 THEN NULL      ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL      WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL      ELSE min_value END AS min_value, cache_value, is_cycled FROM verification_code_user_mapping_id_seq;
             sequence_name             | start_value | increment_by | max_value | min_value | cache_value | is_cycled
---------------------------------------+-------------+--------------+-----------+-----------+-------------+-----------
 verification_code_user_mapping_id_seq |           1 |            1 |           |           |           1 | f
(1 row)

Here are the permissions granted to user root

db_name=> \dg root
                        List of roles
 Role name |          Attributes           |    Member of
-----------+-------------------------------+-----------------
 root      | Create role, Create DB       +| {rds_superuser}
           | Password valid until infinity |

I granted default privileges, select and all to all tables and sequences in schema public but with no luck.

Could you assist please?
Thanks in advnce!

UPDATE

db_name=> \dp verification_code_user_mapping_id_seq
                                              Access privileges
 Schema |                 Name                  |   Type   | Access privileges | Column privileges | Policies
--------+---------------------------------------+----------+-------------------+-------------------+----------
 public | verification_code_user_mapping_id_seq | sequence | root=rwU/root     |                   |
(1 row)

I'm using an Amazon RDS.

Might be an AWS-RDS specific issue but it works if you explicitly specify the schema to be dumped.

pg_dump -f lol.sql -Fc -v -n public -O -x --section=post-data -h 127.0.0.1 -p 9999 -U root db-name


Why should a key be made explicit? I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table.  Is this primarily to tell the user that the given column value is (hopefully) guaranteed to be unique within each row?  The uniqueness should still be there even if it isn't mentioned.

You are obviously suggesting that CONSTRAINTs in a database should be enforced by the application(s) that/which access that database?

There are many reasons why this is a bad (bad, bad...) idea.

1) If you are building a "roll-your-own" constraint "engine" (i.e. within your application code), then you are merely emulating what Oracle/SQL Server/MySQL/PostgreSQL/<.whoever...> have spent years writing. Their CONSTRAINT code has been tested over those years by literally millions of end-users.

2) With all due respect to you and your team, you are not going to get it right even in a matter of years - from here, MySQL code alone cost 40 Million dollars. And MySQL is the cheapest of the 3 servers above, and they don't even implement CHECK CONSTRAINTs. Obviously, getting R.I. (Referential Integrity) completely right is difficult.

I used to frequent the Oracle forums and I can't tell you the number of times that some poor manager/programmer has had a project thrust upon him where the genius who had his job before had the "bright" idea of doing what you suggest.

Jonathan Lewis (he wrote a 550 page book on the fundamentals of the Oracle optimiser) gives as no. 2 of his Design Disasters in another book ("Tales of the Oak Table" - the Oak Table is a group of Oracle experts) is

  We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.

3) Even if by some miracle you can properly implement RI, you will have to completely reimplement it time and again for every application that touches that database - and if your data is important, then new applications will. Choosing this as a paradigm will lead to you and your fellow programmers (not to mention support staff and sales) to a life of constant fire-fighting and misery.

You can read more about why implementing data CONSTRAINTs at the application level is nothing short of madness here, here and here.

To specifically answer your question:

  Just why are they declared at all? It seems very helpful, but is it
  actually necessary to have a database that functions

The reason that KEYs (either PRIMARY, FOREIGN, UNIQUE or just ordinary INDEXes) are declared is that, while it is not strictly necessary for a database to have them for it function, it is absolutely necessary for them to be declared for it to function well.


Import CSV in Postgres with static data in other columns How can I COPY a CSV file in Postgres along with static data for the columns not in the CSV? In MySQL I can do LOAD DATA INFILE ... and profile the column names along with any values for the columns not present in the file. I want to do something similar with Postgres but this is not supported by the COPY command.

You will have to create an intermediate table as the target of COPY, and then insert into the final table in the second step.

If you don't have to keep the intermediate table (for cross-checking your results, or as an easy retry point), you can make it temporary and unlogged:

BEGIN;

CREATE TEMP UNLOGGED TABLE intermediate (... columns matching the CSV contents ...);

COPY intermediate FROM 'your.csv' WITH (FORMAT csv, ...);

INSERT INTO final_table (column1, column2, column_with_default1)
SELECT col1, col2, 'default'
  FROM intermediate;

-- check if everything looks fine, row counts match the expectation, etc.

COMMIT;

UNLOGGED will prevent a lot of WAL writes when the CSV is big.  I put the defaults into the SELECT part of the INSERT instead of the (temp) table to save resources - there is no need to store the same default values many times.


Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed I've got this sequence of error messages showing up regularly in my server error log:

  Error: 9245, Severity: 16, State: 1.
  During the last time interval 257 query notification errors were suppressed.

But I cannot find any specifics on Error 9245. I've looked at the spid that is generating the error, and it's related to the service broker. I also have errors like these showing up regularly, and I suspect they are all related:

  The query notification dialog on conversation handle '{1C8BCD7B-3366-E211-93C7-005056BA3606}.' closed due to the following
  error:
  <?xml version="1.0"?>
  <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
  <Code>-8490</Code>
  <Description>Cannot find the remote service &apos;SqlQueryNotificationService-b7240bb0-6586-4cee-bb7c-a3d2e706c0f8&apos; because it does not exist.</Description>
  </Error>

That error is one we have known about for a while, but haven't been able to resolve in our application code as of yet. The first two errors have just started showing up in the last few days though, and both the frequency of the errors and the number of query notifications that are being suppressed seem to be increasing.

Does anyone have any insight on further troubleshooting steps, or information on Error 9245?

Edit: Some additional information -

Regarding Jon's question, we are implementing SqlDependency in the App, and I believe it may not be done correctly, resulting in the query notification dialog messages. When we previously ran this application on SQL 2008R2 we enabled trace flag 4133 as outlined in this KB: http://support.microsoft.com/kb/958006 which at least alleviated the number of messages that were occuring in the event log.

That said, the first sequence of error messages that I posted above (Error: 9245) are new (to my knowledge). We upgraded the server to SQL 2012 SP1 a few weeks back and had NOT enabled trace flag 4133 as of the time that the 9245 error started showing up.

Also of note, while the errors were occurring with increasing frequency (nearing 1 error every five to ten minutes) for approximately a 12 hour period they stopped abruptly yesterday morning shortly after I asked here about Error: 9245. About an hour after that I enabled trace flag 4133, and I have not seen the error since. While it seems the two errors are related, I'm not entirely sure they're directly connected.

I'm going to see what I can find from other windows event logs on this server and see if anything else correlates to the times these errors occur. I've also disabled 4133 to confirm that it has not had an effect on the 9245 error.

Edit2: Minutes after disabling trace flag 4133 the errors have begun occurring again, and I believe I know the relation between the two now as well, so I'm going to go ahead and answer my own question.

The error 9245 message appears to be directly connected to the 'The query notification dialog on conversation handle...' message in that SQL Server 2012 SP1 appears to only show that error a maximum of 10 times (with the same timestamp) in the error log, the rest are aggregated into a single error message:

  During the last time interval 257 query notification errors were suppressed.

Which is preceded by the message:

  Error: 9245, Severity: 16, State: 1.

Prior to SQL 2012 I do not believe the error notifications were aggregated at all, and did not result in a severity 16 error.


MS SQL Server: Replication Monitor Transactional Replication Publisher Log Reader Agent Latency Too High? Is my Transactional Replication's Publisher's Log Reader Agent Latency too high?

It depends. But from your screenshot it seems like you have small workload with 6 seconds on log reader agent..

you can use the below script to check for the latency of your logreader agent:

USE [distribution]
SELECT la.name, delivery_latency / 1000, [time]
--AVG(delivery_latency / 1000) AS delivery_latency_Sec
FROM MSlogreader_history as lh
INNER JOIN MSlogreader_agents as la ON lh.agent_id = la.id
--WHERE la.name = ''
ORDER BY [time] DESC
--GROUP BY la.name

What you can do/check:

check you publisher database t-log. How big is your t-log? How many VLF it got? Make sure your t-log is properly pre-size.
How often you are doing reindexing? This can also bloat your t-log and slow down logreader agent reading your t-log. Read it here. So be careful on your index maintenance.
Do you have large batch processing? (from your screenshot seems like no but be aware of large batch processing as it can also slow down the logreader reader/write thread)
Slow IO latency on publisher db t-log?
blocking/locks on distribution database? (highly unlikely for small workload)

Note that there's no magic number for the log reader agent latency. It is going to be depending on how you maintain your publisher database t-log. It should not be cause of concern IMO.


Is it possible to deploy Microsoft PDW on Azure? I've read about it and want to play around with it for a bit.

I think it requires a minimum of 4-5 machines to run, which is feasible with Azure IaaS, but how do I actually deploy it there?

Hopefully you have seen the Azure SQL Data Warehouse announcements by now. It is Microsoft's data warehouse as a service offering. The syntax is very similar to PDW and it is also massively parallel like PDW.


How to clean up memory usage of a database after a big sql query I made a select query which returned about 20 million rows.
Memory usage jumped from 300MB to 3.x GB.

How can I release the additional memory for this database after the query is done, without restarting the SQL process?

I tried:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Declare @dbid int = db_ID()
DBCC FLUSHPROCINDB (22)
DBCC FREESYSTEMCACHE ('Comments')
DBCC FREESESSIONCACHE

But it only decreases memory usage by 300MB.

Paul White wrote a response to the question What's a good way to get SQL Server to release memory to the operating system on a dev machine?.  This approach IS at the instance level, but that may be acceptable for your situation.

Quoting Paul:

  I routinely increase and decrease the max server memory configuration
  option for the SQL Server 2005-2016 instances running on my laptop
  (with lock pages in memory enabled). Has never caused me any problems,
  and memory always seems to be released quickly.

With that in mind and referencing Server Memory Server Configuration Options, it would seem that you could simply invoke the following T-SQL once to reduce the memory and then again to increase the memory by modifying the max memory value:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO


Does adding a postgres index concurrently take up disk space gradually or all at once? I'm adding a unique index (concurrently, over the course of hours or maybe even days, on a column of 5 billion bigints, all of them NULL) on a Postgres 9.6 server that's pretty low on disk space. I can see that it's increased disk utilization from 94% to 95%, and I'm not sure if it's set aside all the space it wants, or if it's going to write more to disk later and take down the server over the weekend.
Does Postgres set aside disk space for indexes in advance, use more disk space gradually over the entire course of indexing, or write intermittently?

When running CREATE INDEX in PostgreSQL, disk space will be allocated gradually as needed. It should be easy to observe with a simple command like watch du -s $PGDATA.

Same applies to any data-generating command (INSERT, COPY, CREATE TABLE AS, etc etc).

It's hard (and in most cases, pointless) to pre-calculate and pre-allocate all required space before object is created. If the transaction breaks, data can be simply discarded.


SQL Staging Tables: Primary Key Clustered or Heap We are taking legacy flat txt files and inserting them into stage tables with SSIS.The question arose whether table should have primary clustered key index or not. This is direct flat file import with no transformation.

create table dbo.CustomerTransaction
(
     CustomerName varchar(255),
     PurchaseLocation varchar(255),
     Productid  int,
     AmountSold float,
     CustomerAddress varchar(50)
)

create table dbo.CustomerTransaction
(
     -- discussion for adding this column
     CustomerTransactionId int primary key clustered identity(1,1)

     CustomerName varchar(255),
     PurchaseLocation varchar(255),
     Productid  int,
     AmountSold float,
     CustomerAddress varchar(50)
)

-- both tables have nonclustered indexes
create nonclustered index idx_ProductId on dbo.CustomerTransaction(ProductId)
create nonclustered index idx_CustomerAddress on dbo.CustomerTransaction(CustomerAddress)

-- Actually have more indexes, tables above are just for sample

1) Before ETL, the staging tables are truncated. There are No Deletes and No Updates. Only Inserts.

truncate table dbo.[CustomerTransaction]

2) Then disable all indexes before ETL.

alter index all on dbo.[CustomerTransaction] DISABLE

3) We conduct SSIS data flow with default fast load, which I read is equivalent to bulk insert. No transformations occur here.

4) Then reenable all indexes after import is done.

alter index all on dbo.[CustomerTransaction] REBUILD

5) The staging tables are then selected on join and where clauses, and placed into datawarehouse. This we why we have nonclustered indexes. After data warehouse is loaded, we truncate the staging tables.

We are hearing information that ETL Stage tables are good as heaps. However, also learning of fragmentation and performance issues with heaps. Reading all the articles below

I am reading conflicting opinions. One says Binary tree clustered are maintenance headaches for import ETL. Other says Heaps have performance issues with fragmentation. Our performance testing does not show much difference, but our data may change later. So we need to make a good design decision.

https://sqlsunday.com/2016/09/01/compelling-case-for-heaps/

https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

http://kejser.org/clustered-indexes-vs-heaps/

https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-heaps-and-their-fragmentation/

We know a good reason to have identity is for row labelling, however question is mostly about internals and performance.

Having an identity column doesn’t force you to use it as a clustered index key.

You’re right that heaps work well here. I would consider Thomas Kejser to be the authority on the subject, and it’s good you’ve listed him as one of your resources.

As for fragmentation in heaps - doesn’t happen on insert-only.

Edit: Go through this article about parallel insert, and notice the comparisons between heaps and clustered indexes. https://blogs.msdn.microsoft.com/sqlcat/2016/07/21/real-world-parallel-insert-what-else-you-need-to-know/


SQL Server database stuck in Recovery after Restoring .BAK to same instance We've had an issue crop up a few times -- maybe we're not using 'best practices' to restore, but appreciate any advice. We run an IIS app with a SQL Server database, and sometimes we run two+ instances (multi-site) on the same server / SQL Server instance.

Let's say we have a database 'site1', and we create a site1.bak of that site. Then we need to set up another, so we restore site1.bak to a new database site2.

However, on restoring that database (and ensuring the file names are different), the ORIGINAL site says it's IN RECOVERY. The 'fix' is easy -- we run:

RESTORE DATABASE NameofDatabase
WITH RECOVERY

...but the fear is something else is going on that could be detrimental. Why would the original database go into restore when the .bak is being restored to a new database with different file names?

on restoring that database (and ensuring the file names are different), the ORIGINAL site says it's IN RECOVERY. The 'fix' is easy ...

You are restoring on the same database and using norecovery in your script. You should use (pseudo code) ..

restore database dbname
from disk = 'path to your backup'
with recovery, stats =10,
move 'logicalName' to 'physical_path\new_dbname.mdf',
move 'logicalLogFileName' to 'physical_path\new_dbname_log.ldf'


Splitting a table into two tables randomly with 50-50 percent of records in impala I have a table having n number of records in impala. I need to find out how can I divide that table into two equal halves with 50 - 50 percent of records in each.

I assume that that you have 3 tables with identical structure, where the first has the data while the two others are empty.
I also assume that the tables or at least table1 has a primary key.

Searching through the Impala SQL documentation, I don't find any window functions, so a ROW_NUMBER() solution is out of the question.

We could use a random function to split the table in 2 (almost equal) halves.:

-- insert half of the rows into table 2
INSERT INTO table2
SELECT *
FROM table1
WHERE rand() < 0.50 ;

-- insert the rest rows into table 3
INSERT INTO table3
SELECT t1.*
FROM table1 AS t1
  LEFT JOIN table2 AS t2
    ON t1.pk = t2.pk
WHERE t1.pk IS NULL ;

After that, we can drop table1.


MySQL hang in load data infile command From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runs pretty well . But I want to ask more question around why my db server hangs?

I'm not sure of the main reason that @RolandoMySQLDBA mention that lets the db server hang. You can test my command:
Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date
 FIELDS TERMINATED BY  ',';
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2:
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date
 FIELDS TERMINATED BY  ',';
COMMIT;
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

can any one explain for me?
Thanks,

Please look carfully at session 1.

Lock table v3_zone_date WRITE;
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date
FIELDS TERMINATED BY  ',';
COMMIT;
SET AUTOCOMMIT=1;

should be

Lock table v3_zone_date WRITE;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date
FIELDS TERMINATED BY  ',';
UNLOCK TABLES;

No need to have these lines

SET AUTOCOMMIT=0;
COMMIT;
SET AUTOCOMMIT=1;

because MySQL 5.0 Certification Study Guide, Chapter 29, Page 418 Bulletpoint #1 says

  UNLOCK TABLES implicitly commits only if you have explicitly locked
  tables with LOCK TABLES. SET AUTOCOMMIT = 1 implicitly commits only
  if autocommit mode wasn't already enabled.

LOCK TABLES will hold up any transaction-based row locks that are trying to be acquired.

Since the tables are locked independently, you must just need to increase the size of your InnoDB Buffer Pool (innodb_buffer_pool_size) to make more room for row locks across all InnoDB tables.


Cleanup / Prune Unreferenced Data in Dimension Tables We have a star schema data warehouse running on MySQL 5.6. We keep a rolling 18 months of data in our fact tables using partitions by month. We have a number of dynamic dimension tables that are referenced by multiple fact tables. However, we have no easy way to remove the rows from dimension tables that are no longer referenced by any fact table. Quick summary looks like this:

dim_url - 1B rows - 360GB
fact_ranks - 2.3B rows - 240GB
fact_seen - 2.8B rows - 295GB

Currently we are attempting to use a combination of Percona Archiver and triggers to generate "used dimension keys" tables, so we can do the process online. We then use the key table to build a new dimension that only has referenced rows. However, we have been unable to complete this process in production and estimate it could take up to a month.

This has to be a common problem with a more elegant solution.

(Normally, I would say "don't bother".  But I see that your Dim table is nearly as big as the Fact table.  Hmmmm...  Perhaps it was unwise to even have the Dim table??  Think about that.)

For building the Used table (and assuming a Trigger handles incoming Inserts):

From the Fact table, build a list of useful values (Used).  Do this one chunk at a time, say, 1000 rows, as determined by the PRIMARY KEY.  This list goes into an extra table, Used.  One transaction per chunk.  De-dup and add a PK.  See my comments on chunking.

Now, walk through the Dim table, perhaps only 100 rows at a time, and do a multi-table delete, using LEFT JOIN Used ... WHERE ... IS NULL to determine what is not needed.

Be cautious -- the DELETE and the TRIGGER may step on each other.  That is, there may be deadlocks necessitating re-execution.  But 100 rows at a time should keep that at a nice balance between minimizing deadlocks and maximizing speed.  One transaction per chunk.

Yes, it will take time -- both for the discovery of Used and for the DELETEs.  But it should not matter.  Everything else is humming along, with only very rare and brief interference.

I guess the TRIGGER would need to insert into Used when a row is inserted into Dim.


change encoding of database from latin1_swedish_ci to utf8 I have an huge database in latin1_swedish_ci. Now i need to convert all data to utf8 collation.

My question is about the consistency of the information. When I do this change it is possible corrupt the data that is in database?

something like this:

mysql> alter database user_db default character set utf8 collate utf8_unicode_ci;

there is any risk of changing the information?

If you have any stored procedures that use database defaults, you must drop and recreate those stored procedures.

If the ALTER DATABASE command runs quickly, chances are the tables were not altered.

You may have to run ALTER TABLE on all your tables as follows:

ALTER TABLE tblname CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];


How do I connect my MS Access database to the LAN? I wrote a database program in MS Access a couple of years ago, and it is used to keep track of some personal stuffs. I now want to expand this personal project and allow several computers to have access to this database. Assuming that all I am interested in connecting to are my home computers, i.e. all connected to the same router (same LAN), what are the basic steps I need to do this?

Also, I am very new to database, but I happened to know how to write VBA on MS Access. Is there any tutorial that can help on what I do?

Thanks!

There is nothing magical about this.  Just put the Access database file in a file share on your LAN and let people access it from whatever computer they are using.

If you will need to be sure to open the database in shared mode.  Go to the options, select client settings, advanced, and set the default open mode to "shared".  (Depending on your version of access, the way to get to this setting may vary.)

You can do something more elaborate, like segregating your data and your GUI and joining the front end databases to the back end with linked tables, but that may well be overkill.


how to check list of users present in all postgres database I have around 50 of postgres database. I need to check all users with their respective database name. I know \du gives the list of all users like,

                                    List of roles
            Role name             |            Attributes             |      Member of
----------------------------------+-----------------------------------+----------------------
foo                             Superuser                              {}
..
..

which doesn't display database name. I can manually login to each database and run \du to see the list of users present in that database, but that would take lot of time. So is there single line command or simple workaround which can display user list with their respective database name ?

Users are global to the Postgres instance (aka "cluster"). So there is no such thing as "their respective database name". \du will always show you the same list of users regardless of the database you are connected to.

If you want to get a list of databases a user is allowed to connect to, you can do that with this query:

select u.usename,
       (select string_agg(d.datname, ',' order by d.datname)
        from pg_database d
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_databases
from pg_user u
order by u.usename

To turn that query "around" and list the users that are allowed to connect to each database, you can use this:

select d.datname,
       (select string_agg(u.usename, ',' order by u.usename)
        from pg_user u
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_users
from pg_database d
order by d.datname;


Subqueries in SQL Server I am taking a SQL course and am completely stumped on this question.
Using a subquery have a query that returns:
CustomerID, EmailAddress, FirstName, LastName, '#3: Customers who have ordered than 1 product' as
queryInfo
The subquery from this query will be: All customers with who have ordered more than 1 product
Note: More than one Product, not Quantity > 1 So a customer who ordered 10 of the same
product is NOT what I'm looking for.
Here is the full query I used:
SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE c.CustomerID IN (
           SELECT p.ProductID
             FROM Customers c
             JOIN Orders o
                  ON c.CustomerID = o.CustomerID
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             JOIN Products p
                  ON oi.ProductID = p.ProductID
         GROUP BY p.ProductID
           HAVING COUNT(*) > 1
         )
     ;

Not sure what I am doing wrong, but any input is greatly appreciated. Thanks!

Your code has a number of issues:

You can simplify to a  COUNT(DISTINCT here as you want the number of distinct products
The subquery returns ProductID you would need CustomerID instead. Since you need the grouped count, you are better off with an EXISTS query instead
You are querying Customer again unnecessarily. Products is also not needed as you can get ProductID from OrderItems

SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE EXISTS (SELECT 1
             FROM Orders o
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             WHERE c.CustomerID = o.CustomerID
           HAVING COUNT(DISTINCT oi.ProductID) > 1
         )
     ;

You could also remove the HAVING and check the COUNT(DISTINCT from the subquery on the outside:
 WHERE (SELECT COUNT(DISTINCT oi.ProductID)
             FROM Orders o
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             WHERE c.CustomerID = o.CustomerID
         ) > 1


Is it benefit to create a index key if unique key already exists on the same columns? The table schema for example:

CREATE TABLE `foos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bar1` bigint(20) unsigned NOT NULL,
  `bar2` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uqx_bar1_bar2` (`bar1`,`bar2`),
  KEY `idx_bar1` (`bar1`),
  KEY `idx_bar2` (`bar2`),
  CONSTRAINT `fk_bar1` FOREIGN KEY (`bar1`) REFERENCES `bars1` (`id`),
  CONSTRAINT `fk_bar2` FOREIGN KEY (`bar2`) REFERENCES `bars2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This table is created for m:n relations, index key is created separately for bar1 and bar2, and unique key is created for both columns bar1 and bar2.

In most cases, this query will be executed:

SELECT * FROM foos WHERE bar1=? AND bar2=?

The question is: Will query optimizer get benefit if index key created for both columns bar1 and bar2? That is:

ALTER TABLE foos ADD INDEX `idx_bar1_bar2` (`bar`,`bar2`)

More discussion on building indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Furthermore your table smells like a many:many mapping table??  Here are several tips on efficiency for such:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table


Sql Server - Database Backup Time I've been doing a lot of database backups and restores of late, and I've been wondering how if the backup takes 10-15 minutes to capture, how it can still show a snapshot of the database at a split second in time?

How does Sql Server accomplish this?

You are asking how Database Snapshots work - there is a good article on this on MS TechNet

The short answer: it is so fast because when you do a snapshot you do not backup or copy any data. All that is done - is an empty file created.


Can anyone help me understand the following paragraph? I was reading Database Systems Design, Implementation and Management by Carlos Coronel. In the chapter of Normalization after he explained 3NF, he writes

  The existence of multiple candidate keys can also influence the identification of transitive dependencies. Previously, a transitive dependency was defined
  to exist when one nonprime attribute determined another nonprime attribute. In
  the presence of multiple candidate keys, the definition of a nonprime attribute as
  an attribute that is not a part of any candidate key is critical. If the determinant of a
  functional dependence is not the primary key but is a part of another candidate key,
  then it is not a nonprime attribute and does not signal the presence of a transitive
  dependency.

I am having hard time to understand what he wants to say. Can anyone explain this paragraph in simpler terms.

I do not know if the following can be useful to explain the involuted phrase that you cited, however all the following facts are true.

A relation must have at least a candidate key and can have more then one candidate key.
By definition, an attribute is prime if it is part of any candidate key.
A transitive dependency is a dependency that derives from two other dependencies through the “transitive” Armstrong’s axiom (if X -> Y and Y -> Z, then X -> Z), so for instance if in a relation R(A, B, C, D) we have that A -> BC and BC -> D, the dependency A -> D is a transitive dependency.
A transitive dependency can be problematic (that is can produces data anomalies) if the second dependency of the transitive rule (the “Y -> Z” dependency of the rule) has a determinant which is not a full candidate key, but (a) a subset of a candidate key, or (b) contains attributes that are not prime.

Consider the relation R(A, B, C, D E), with dependencies AB -> CDE, CD -> ABE, C -> E. In this case there are two candidate keys, AB and CD, so C, the determinant of C -> D, is a prime attribute, since it is an attribute of the candidate key CD (“it is not a nonprime attribute”).

Note that in the previous example the attribute E is transitively dependent from AB (from AB -> CDE we can derive AB -> C, and from  AB -> C and C -> E, we can derive AB -> E) and the relation is not in 3NF, neither in BCNF.


Can't search for non-ascii characters I have a database with Turkish names and surnames. Here is some information about my table:

CREATE TABLE `sorted_tc` (
  `TCKIMLIKNO` double NOT NULL,
  `ADI` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `SOYADI` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ANAADI` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `BABAADI` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `DOGUMYERI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `DOGUMTARIHI` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `NUFUSILI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `NUFUSILCESI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ADRESILI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ADRESILCESI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`TCKIMLIKNO`),
  KEY `anababa` (`BABAADI`,`ANAADI`),
  KEY `adsoyad` (`SOYADI`,`ADI`),
  KEY `adresili` (`ADRESILI`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

select hex(ADI), hex(SOYADI) from sorted_tc WHERE TCKIMLIKNO=12161429422;

5941C59E4152 41524142414349

5941C59E4152 is hex value for utf8 encoded 'YAŞAR'.

When I search for 'yasar', I want to get both 'yasar' and 'yaşar'. Similiarly, when I search for 'yaşar', I should get both 'yasar' and 'yaşar'.

However, when I search for 'yaşar', I am not getting any rows. Here is hex dump of relevant query from mysql query log:

0004-15a0:  51 75 65 72-79 09 73 65-6c 65 63 74-20 2a 20 66  Query.se lect.*.f
0004-15b0:  72 6f 6d 20-73 6f 72 74-65 64 5f 74-63 20 57 48  rom.sort ed_tc.WH
0004-15c0:  45 52 45 20-41 44 49 3d-22 79 61 c5-9f 61 72 22  ERE.ADI= "ya..ar"
0004-15d0:  20 41 4e 44-20 53 4f 59-41 44 49 3d-22 61 72 61  .AND.SOY ADI="ara
0004-15e0:  62 61 63 c4-b1 22 0a 31-35 31 32 32-36 20 31 34  bac..".1 51226.14

My table orignally used collation utf-8 general ci, and I could search for 'yaşar' with success.

I am using Mariadb 5.3

Sometimes ago, I faced a similar issue and I solved that issue with the help of my friend. How I did was I changed the collation to uft8_turkish_ci in MySQL. Also from MySQL website, I found the following document, which may be helpful for you,

MySQL Character set

Thanks.


Proper way to create table friends in single statement I have table users

CREATE TABLE IF NOT EXISTS users(
    id int AUTO_INCREMENT PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE,
    goal VARCHAR(255) DEFAULT NULL,
    age INTEGER DEFAULT NULL,
    gender VARCHAR(255) DEFAULT NULL,
    country VARCHAR(255) DEFAULT NULL,
    city VARCHAR(255) DEFAULT NULL,
    comment VARCHAR(255) DEFAULT NULL)

I want to create table friends. One key feature that is every user by default friend for itself.

This is my current statement

create table friends (user_id int,  friend_id int, FOREIGN KEY (user_id) REFERENCES users (user_id), foreign key (friend_id) references users (user_id)) as select user_id from users;

update friends set shown_id = user_id;

I think it looks pretty kludgy, creating a table and changing it immediately, may be there a better way to implement it?

Another important note: the database loses connection after creating the friends table.

create table friends (user_id int,  friend_id int,
        FOREIGN KEY (user_id) REFERENCES users (user_id),
        foreign key (friend_id) references users (user_id)
) as select user_id from users;
ERROR: 2006: MySQL server has gone away
The global session got disconnected..
Attempting to reconnect to 'mysqlx://root@localhost:33060/test_db'....
The global session was successfully reconnected.

Don't know where shown_id came from, but I guess it's a typo.

Instead of populating only one column (user_id) of your friends table, you can add another user_id column to the select statement with friend_id alias. Immediate update query is not needed.

create table friends (
    user_id int,
    friend_id int,
    foreign key (user_id) references users (user_id),
    foreign key (friend_id) references users (user_id)
) as (
    select user_id, user_id as friend_id
    from users
);


Hive. Over partition by vs where conditions I have a table partitioned this way.

# Partition Information
# col_name              data_type

year                    string
month                   string
day                     string
hour                    string

I want to run the query on a whole month. I understand that I could do

where day > 01 and day < 31

But the table is VERY big. I was told it would take too much time and that I should run it separately on every day. I thought about over partition by. Like

select col1, col2, sum(col3) over (partition by day)
from table

but I am not sure how this will work. Would the query work on every day successively. Would it be easier on the cluster? Would col1 and col2 be grouped automatically or would I need to add group by?

Table
col1 col2 col3 month date....
1    s    4    01    01
1    s    3    01    01
1    q    5    01    01

What I want in the result

col1 col2 col3 month date....
1    s    7    01    01
1    q    5    01    01

In order to make use of the partitions -

Your query on the whole month should look something like this:

select      col1,col2,sum(col3),year,month,day
from        mytale
where       year  = '2017'
        and month = '02'
group by    col1,col2,year,month,day

Your query on a single day should look something like this:

select      col1,col2,sum(col3),year,month,day
from        mytale
where       year  = '2017'
        and month = '02'
        and day   = '01'
group by    col1,col2,year,month,day

Your query on a range of days should look something like this:

select      col1,col2,sum(col3),year,month,day
from        mytale
where       year  = '2017'
        and month = '02'
        and day between '01' and '07'
group by    col1,col2,year,month,day


Implications of removing unnecessary column from primary key I have a database in which a number of tables are defined with multiple UUID columns forming the Primary Key when they should instead be defined as Foreign Keys, e.g.
CREATE TABLE [dbo].[SomeTable](
    [SomeTableID] [uniqueidentifier] NOT NULL,
    [WorkEntryID] [uniqueidentifier] NOT NULL,
    [TimeEntryID] [uniqueidentifier] NOT NULL,
    [RateEntryID] [uniqueidentifier] NOT NULL,
    ...

 CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED
 (
    [SomeTableID] ASC,
    [WorkEntryID] ASC,
    [TimeEntryID] ASC,
    [RateEntryID] ASC
 )
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SomeTable] ADD  CONSTRAINT [DF_SomeTable_SomeTableID]  DEFAULT (newsequentialid()) FOR [SomeTableID]
GO

The additional columns should have been defined as Foreign Keys and in some cases have been. I'm assuming there are performance implications of having the additional columns as part of the PK? Are there any considerations I should be aware of before redefining the PK for the tables to include only the PK column?

The ideal clustered index in SQL Server meets the following criteria:

Unique
Never changes
New values are strictly ascending
Narrow
Queries against the table run faster because they can take advantage of range seeks or the ordering of the clustered index

In production you may not be able to pick a clustered index which meets all of those criteria, which is fine. You can only do the best that you can do. A clustered index of four GUIDs is an uncommon choice. I assume that it will meet the first two criteria but not the other three. One performance impact of the current index is that nonclustered indexes will take up additional disk space because they each store a copy of the clustered index. That's why clustered indexes are ideally narrow.
Based on the information provided in this question, you will likely be better off by changing the clustered index to just be the SomeTableID column. I'm making some assumptions here, but it's unlikely that you'll see performance degradation caused by that switch.
This answer addressed clustered indexes and not primary keys because the clustered index is the thing that matters for performance, which seems to be the focus of this question. Primary keys are to enforce data integrity and business constraints. Continuing to match the columns for the clustered index and the primary key should be fine in this case unless there is a business reason to keep the current primary key columns.


Easier way to handle so many isnull() situation Is there any good way to avoid writing isnull() function so many times inside a stored procedure?

I have a stored procedure that uses the isnull() function almost 30 times, I think that I am missing a concept, but until I find better way to get my data I'll love to clean my code from so many isnull() functions.

Can I for while set SQL Server 2008 R2 to using null values as float 0? Most of my isnull() is just adding zero 0 value if there is no data so I can do math operations.

EDIT: I am not lazy, I just trying to clean my code and avoid select parts looks like this

select
   vrsta.NAZIV
   ,isnull(sum(prod.prod_mpci),0) as prod_MPCI
   ,isnull(sum(isnull(mal_MPCI,0) + (vel_kol * isnull(mal_MPC,vel_VPC))),0) as lager_mpci
   ,isnull(sum(vel_NCI),0)+isnulL(sum(mal_NCI),0) as lager_nci
   ,sum(   case
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) > isnull(prod.prod_kol,0) and isnull(dzn.dzn,'2010-01-01') < @dzu
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_MPC,vel_VPC)
                    else 0 end
            ) as visak_MPC
   ,sum(   case
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) > isnull(prod.prod_kol,0) and isnull(dzn.dzn,'2010-01-01') < @dzu
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_NC,vel_NC)
                    else 0 end
            ) as visak_MPC
    ,sum(   case
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) <= isnull(prod.prod_kol,0)
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_MPC,vel_VPC)
                    else 0 end
            ) as manjak_MPC

    ,sum(   case
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) <= isnull(prod.prod_kol,0)
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_NC,vel_NC)
                    else 0 end
            ) as manjak_NC

No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.


PostgreSQL unique continue on violation I am creating ETL where I rely on the database to refuse duplicate values for a business key. In SQL Server this works like a charm. The database gives an error, the ETL ignores the error, continues and the next row gets inserted just fine. With PostgreSQL my UNIQUE constraint (or index, they show the same behaviour) blocks any further inserts. This makes this solution useless and PostgreSQL a less useful candidate for the datavault I'm creating for my client.

I find a solution with using a rule, but this turns out to be extremely slow. The performance is multiple times worse. This makes using a rule also no good way to have the database deal with doubles.

Is there any way to have PostgreSQL keep on inserting new rows after a UNIQUE constraint violation?

Check PostgreSQL documentation for detailed syntax for INSERT statement.

In particular, there's an optional ON CONFLICT part:

ON CONFLICT [ conflict_target ] conflict_action

  where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )
[ WHERE index_predicate ]
ON CONSTRAINT constraint_name

  and conflict_action is one of:

DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
                ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
                ( column_name [, ...] ) = ( sub-SELECT )
              } [, ...]
          [ WHERE condition ]

In your case, you probably want to append at the end of your INSERT statement:

ON CONFLICT DO NOTHING

and the conflicts will be skipped, and further inserts will continue.

This answer is based on @Akina's comment. Thanks!


Postgres 9.4.1 on Ubuntu 14.04.2 LTS fails to install properly I'm trying to install PostgreSQL 9.4.1 on Ubuntu 14.04.2. I'm using the packages from apt.postgresql.org.

The package installs but it fails running pg_createcluster. If I run manually, I get:

Creating new cluster 9.4/main...
  config /etc/postgresql/9.4/main
  data /var/lib/postgresql/9.4/main
  locale en_US.UTF-8
Flags of /var/lib/postgresql/9.4/main set as --------------e-C
Error: initdb failed

I can't find any details as to why initdb fails.

If I manually run initdb as the postgres user, that works, but that does not create the config files.

I checked everything I could think of, including on-line and can't find a solution.

Not an elegant solution but after installing the package using apt-get (which fails creating the cluster but installs PostgreSQL), I switched to the postgres user and created the database using initdb. Then back to root, I created the cluster using the pg_createcluster command.

This moved the configurations to /etc/postgres/9.4/main and set it all up.


Give third-party access to AWS hosted MySQL I have a production MySQL database hosted on AWS for an eCommerce store I work with. Employees in the firm are requesting access to our live SQL database to use with BI tools like Tableau/Qlikview. How can I grant these users read-only permissions to analyze data from the live database and not the ability to write data?

GRANT SELECT ON My_Database.* TO bi_user;

It's explained in detail here (sample code below).

Don't forget though that people with SELECT privileges can cripple a database as surely as if they had DELETE privileges by running complex reports.

Code from website:

If you want give a user read-only access to a database, you can grant to him/her only the "SELECT" privilege, so that he/she can not run any DDL statements, and any INSERT, UPDATE, or DELETE statements. The tutorial exercise gives you a good example:
>cd \mysql\bin
>mysql -u root -ppassword

mysql> USE fyi;
Database changed

mysql> CREATE TABLE links (id INTEGER, name VARCHAR(80));
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO links VALUES (1, 'dba.fyicenter.com');
Query OK, 1 row affected (0.05 sec)

mysql> CREATE USER guest IDENTIFIED BY 'other_password';
Query OK, 0 rows affected (0.24 sec)

mysql> GRANT SELECT ON fyi.* TO guest;
Query OK, 0 rows affected (0.00 sec)

mysql> QUIT;

>mysql -u guest -pother_password

mysql> use fyi;
Database changed

mysql> SELECT * FROM links;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | dba.fyicenter.com |
+------+-------------------+
1 row in set (0.04 sec)

mysql> INSERT INTO links VALUES (2, 'dev.fyicenter.com');
ERROR 1142 (42000): INSERT command denied to user
'guest'@'localhost' for table 'links'


What and How is the ID antipattern? What is the ID antipattern and why / under which circumstances is it considered an antipattern?

  The id antipattern is having a unique ID column in each table. The ID
  is generated for each new record. Why is this an antipattern? –
  Andrew Wolfe 11 mins ago

The id antipattern is having a unique ID column in each table without requiring (or often discouraging) the application of alternate unique keys.  The ID is generated for each new record.  Why is this an antipattern?

Numeric surrogate keys are fine, including when they are singleton primary keys.

However, every time I have seen a schema in which EVERY table has this id column, the designer wanted the row to be just like a memory handle.  This avoids the crucial question of primary key design and fosters repeated insertion of contradictory redundant data.  It makes database designers tear their hair out.

In the memory of a running application, memory handles like this are transient.  They are dead when the application exits, and no restart of the application tries to use them again.  This makes pointers and object handles useful in that context.

Similarly, storing a handle/pointer to one object as a piece of another object makes sense.  It imposes ownership of the one object over the other.

These handles have an intrinsic meaning only insofar as some client came in and needed data munged around.  Apart from client action the handle is devoid of meaning.

However, a database stores permanent data with permanent IDs.  You need to have a way of getting back to the data you want three days afterward.  To do that you need to have something meaningful somewhere, not just memory handles made permanent.

Why do database designers care about primary keys and alternate keys?  Because when someone comes back to the database and can't find their data, the database is worthless.  If you need to browse and analyze the data, how will you figure it out if everywhere you look there are columns named 'id' with no other constraints to tell you what they mean?  Which of the 90000 rows with object_label = 'Important Thing' is the one you want?


What are System Dynamics Variables on MariaDB? I'm a nweeby and i really didn't understand what is a Dynamic Variable. I didn't found on documentation. And really what is the concept of Dynamic Vatiables.

For the MySQL/MariaDB server "VARIABLES":

Dynamic: Yes -- You can SET the variable and immediately see the effect.
Dynamic: No -- You need to change the configuration file (my.cnf) and restart the server.

That covers most cases; there are a few that are more complicated.  Also, there is some move toward making more of the Variables "dynamic", so you will see differences based on Version of MySQL/MariaDB.

Also, this is usually the history of the values:

When the server starts, the GLOBAL copy of the variables are set from my.cnf or formulas, etc.
When a user connects, the GLOBAL values are copied into SESSION copies.
If dynamic, SET variable_name = value changes the SESSION value.

SET GLOBAL ... will change the default value for all subsequent connections, not your current connection.  There are exceptions to this...

Some VARIABLES do not distinguish between SESSION and GLOBAL.  For example, since InnoDB's buffer_pool is shared among connections, most settings are GLOBAL; there is no separate value for each SESSION.

(Sorry if I digressed too much; it gets messy.  But usually you don't need to worry about these issues.)


how to edit and migrate DTS package on SQL server 2000 I currently have an old SQL server 2000, with some DTS packages in system database msdb, table msdbsysdtspackages.

I need to migrate those packages to sql server 2016, is there a tool I can do that?

if not, is there a way I can know what are those packages doing, so I can at least rewrite them? Because now they all stored as hex data, and I don't even know what are they doing...

Thank you and any advice would be welcome.

Per all the references I can find (most notably Upgrade Integration Services Packages on MS TechNet, and this StackOverflow answer), the options are:

Use the tools available with SQL Server 2005 or 2008 to convert the DTS package to an SSIS package; then, upgrade the SSIS package with SSDT; or
Rewrite the package with SSDT, for your current SQL Server version.

Note - in the article, it states that any ActiveX scripts will have to be thrown out. That's not entirely true - you'd need to rewrite them as a script component in SSIS. I believe I did this at one point (could be wrong - I had a lovely Perl script to break street addresses into number, pre-direction, street name, suffix and post-direction that I had to convert multiple times to different things, ending up as an SSIS C# script component).

As 2005 and 2008 are no longer supported, I suspect getting a developer copy to install and do the conversion could be difficult. And, even if you did (as noted in the SO post), the conversion process may not give you what you want.

Back in 2009, I was involved in moving our database (and about 100 DTS packages) from 2000 to 2008. We did use the conversion process, and most of the time it worked fairly well. That said, we also tested the SSIS packages side-by-side against the original DTS packages, to confirm the results. And, now, the conversion process would be a two-step one.

If you've got 10 packages to move, a simple rewrite makes sense (and gives you an opportunity to learn about the additional capabilities that SSIS provides). If you've got dozens of packages, and happen to have access to the 2005 or 2008 SQL Server tools (you don't need to run a server with that version to do the job, I don't believe), then it may make sense to go through the conversion process and see what you get. If most of what you have makes it through fine, that's wonderful - worst case scenario, you're back to a manual rewrite.


Audit kernel module usage in Oracle In my continuing effort to bring my database up to a given standard, I need to ensure that all loading, unloading, and restarting of kernel modules are being audited. I have no idea what this thing is talking about or how I would go about auditing such a thing. This is one in a list of auditable events. Before the list begins, the standard claims that some may require OS settings to audit; I'm assuming this is one of them. Unfortunately, the people who may know are not easily accessible.

Any enlightenment would be greatly appreciated.

I don't think this question can be answered without further details but some general thoughts:

What kernel are you talking about? If it's the OS Kernel then there are a few different ways depending on your OS and/or your Linux distribution. And it's depends  on your job role - are you responsible for the database server's Os, too or are you just responsible for the database?
Do you have some kind of monitoring software like e.g. EnterpriseManger/GridControl? If so, a lot of baselining and auditig can be done with this tool, but beware that there a a lot of features that require special licenses. Read the most recent licensing documentaition for that. Even if you don't use the tool itself (e.g. for integration in another monitoring tool) but want to query the underlying views, you have to buy the license.
If database auditing is required have a look at the documentation for your database release. With 12c the unified auditing is the way to go.


Query to partially summarize data in a table using SQL I'm learning how to handle databases, so this is a trivial question, and quite likely has been answered already. I'm just not familiar to the lingo and have no idea how to search for it (although I tried).

My data is on LibreOffice, which runs HSQLDB v1.8 (very old engine version). For the moment, I have to stick to it. Using a more up-to-date SQL engine is not possible.

I have a payment table with the following structure

member_id  pay_year pay_unit
0          2016     1
0          2017     2
0          2017     1
0          2017     1
0          2018     2
1          2015     1
1          2018     2

I need to sumarize this table into something like

member_id  pay_year pay_unit_year
0          2016     1
0          2017     4
0          2018     2
1          2017     1
1          2018     2

The pay_unit_year is the number of years of membership purchased. So eventually I need to operate with the years and units, and getting a single line per member

member_id  expire_year
0          2023
1          2020

I'm using

SELECT DISTINCT member_id,
pay_year,
pay_unit
FROM payments

and I'm getting

member_id  pay_year pay_unit
0          2016     1
0          2017     2
0          2017     1
0          2018     2
1          2017     1
1          2018     2

Is there any way to perform an operation on a subset of pay_unit that has the other columns the same information?

I'm aware that SQL has many available date operations, but HSQLDB v1.8 has not them implemented.

Thanks for any help, and for your patience

It's indeed a very basic concept: aggregation. You need the GROUP BY clause:

SELECT member_id,
pay_year,
SUM(pay_unit) AS sum_pay_unit
FROM payments
GROUP BY member_id, pay_year;


Schema For "Other - Add Comment" Many user forms contain an option for "Other - Add Comment" where the user can provide an item not found in the given list.

In other words, the developer sets the list of items. The user must either select an item or type a value.

Examples:

How did you hear about us? {Web, TV, Friend, Radio, other}
Reason for contacting us?  {Sales, Tech Support, Customer Service, other}
Select your printer model?

Proposed schema:

model:     model_id (PK), model_txt (UK), show (bool)

request: request_id (PK), model_id (FK), {other non-relevant fields}

Display models where show = 1

On insert,

If model selected from list, use its model_id
If model typed,

If model_txt exists, use existing model_id
Else, insert new model_txt and set show = 0

Likes:

This solution avoids NULL values and empty strings
Obtaining model_txt is the same for all requests regardless of if the user typed the entry.

Dislikes:

The model table will be polluted with items only tied to one request.
The insert model_txt requires an extra step before the INSERT command.

How can I improve this schema?

Nice question. My objection to your design is: you choose to consider user input and predefined input as the same thing (the only difference being the value of show). But they are not, in my opinion. Because predefined input are different options, but different user inputs could indicate the same idea written with synonyms, wrong spellings, etc, or could be just jokes or junk characters.

You say that this saves you from having empty strings or NULLs. But I believe that the absence of a value (custom text) is a valid use for an empty string.


MDX datetime comparison I have a MDX calculated measure for rolling 30-day totals, and I want to mask the calculation for future members. I'm trying to use a CASE statement and the comparison does not work correctly. If the key of the Fiscal Month member is a datetime, shouldn't this work?

CASE
WHEN [Time].[Fiscal Month].CurrentMember < NOW()
THEN 1
ELSE 0
END

An alternative, and sometimes better performing approach could be to add an attribute to your date dimension along the lines of "IsFuture".

Depending on your data load/processing schedule you could have a field in your datawarehouse or a calculated column in your dsv precalculating if the date is in the future or not.

Instead of performing an on the fly calculation with an IIF in your MDX you could then create a calculated measure using SCOPE like this (untested, from memory)

Scope
 (
    [Date].[IsFuture].&[0]
 ) ;

   This = 1;
end scope;

Just an idea I'm offering, your question doesn't provide a lot of detail to create a working piece of sample code but this could be an alternative approach.
This would return empty if the date is in the future instead of 0 but depending on your requirements that could provide better results if your client is only returning NONEMPTY/NONEMPTYCROSSJOIN results.


Can a trigger create a TABLE when inserting data? I have a trigger that keeps track of history of table dev by inserting a row in table dev_hist:

CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$
    DECLARE action_name text;
    BEGIN
        IF TG_OP = 'DELETE' THEN
            OLD.timestamp := current_timestamp;
            action_name := 'deleted';
            INSERT INTO dev_hist SELECT action_name, user, OLD.*;
        ELSE
            NEW.timestamp := current_timestamp;
            IF (TG_OP = 'INSERT') THEN
                action_name := 'added';
            ELSIF (TG_OP = 'UPDATE') THEN
                action_name := 'modified';
            END IF;
            INSERT INTO dev_hist SELECT action_name, user, NEW.*;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$dev_hist$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS dev_hist ON dev;
CREATE TRIGGER dev_hist AFTER INSERT OR UPDATE OR DELETE ON dev
    FOR EACH ROW EXECUTE PROCEDURE process_dev_hist();

Problem is, if table dev_hist doesn't exist, it throws error.

Is it possible to change it, so it would create table if it doesn't exist?

You can use INFORMATION_SCHEMA.TABLES to check if dev_hist table exists, if not you can create it in your trigger:

CREATE TABLE T (id int, name text);

CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$
    DECLARE action_name text;
    BEGIN

        -- add your catalog information.
        CREATE TABLE IF NOT EXISTS dev_hist
        (
            action text
        );

        IF TG_OP = 'DELETE' THEN
            action_name := 'deleted';
            INSERT INTO dev_hist SELECT action_name;
        ELSE
            IF (TG_OP = 'INSERT') THEN
                action_name := 'added';
            ELSIF (TG_OP = 'UPDATE') THEN
                action_name := 'modified';
            END IF;
            INSERT INTO dev_hist SELECT action_name;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$dev_hist$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS dev_hist ON T;
CREATE TRIGGER dev_hist AFTER INSERT OR UPDATE OR DELETE ON T
    FOR EACH ROW EXECUTE PROCEDURE process_dev_hist();

INSERT INTO T VALUES (1);
DELETE FROM T WHERE id = 1;

1 rows affected

1 rows affected

SELECT * FROM dev_hist;

| action  |
| :------ |
| added   |
| deleted |

db<>fiddle here


Is LIMIT processed before or after retrieving data? MySQL.

My heavy query crashed our MySQL server.

If I add LIMIT 10 will it make my query more lightweight? (both for indexed and unindexed queries).

Which way LIMIT are processed: before the rest of the work or after?

I feel that LIMIT makes indexed queries more lightweight, but does not do for unindexed queries. Right thought?

By suggestion of @RolandoMySQLDBA, I'm combining my comments into an answer.

So, in many cases, LIMIT clause enables the database server to do less work before delivering the result. MySQL docs explain such situations.

It is best to check queries with EXPLAIN to see the execution plan. If the execution plan is different with and without limit clause, then some optimizations were performed (query can be executed more efficiently because of limit by clause, or at least the optimizer thinks so).

For example, using the Sakila test database: a table film has a column named title which is indexed. It has 1000 rows. EXPLAIN for query

select * from film order by title

shows that the databases reads and sorts the entire table (this being cheaper than using the index on title and accessing table rows from index entries, since the whole table must be read). On the other hand, EXPLAIN for the same query with LIMIT clause,

select * from film order by title limit 10

shows that the database now decides to use index on title column; it only needs to read the first 10 index entries and retrieve rows that those entries point to. The rest of the index or table are never accessed. Certainly much cheaper than the first query, if you only need the first 10 rows.


How to order by specific column without breaking groups? I have a table like below and I want to order this table by date without breaking groups.
id    group       date
1 |   group1 | 2011-11-24
2 |   group2 | 2011-08-20
3 |   group1 | 2011-03-11
4 |   group3 | 2011-05-05
5 |   group3 | 2012-01-01

I only consider the latest updated group element when ordering by date.
I want to get order like;
id    group       date
5 |   group3 | 2012-01-01
4 |   group3 | 2011-05-05
1 |   group1 | 2011-11-24
3 |   group1 | 2011-03-11
2 |   group2 | 2011-08-20

Another option, using a window function:
select
    id, "group", date
from
    table1
order by
    max(date) over (partition by "group") desc,
    "group",
    date desc ;

Thnx to @nbk for providing the dbfiddle.uk


xp_cmdshell called from procedure WITH EXECUTE AS OWNER I am trying to understand details of wrapping xp_cmdshell functionality in user defined stored procs, so that other users can just be given execute permission to the stored procs rather than xp_cmdshell.

The steps in this scenario are:

xp_cmdshell is already enabled and a xp_cmdshell proxy has been created
A user with db_owner membership creates a stored proc WITH EXECUTE AS
OWNER which calls xp_cmdshell
The user executes the stored proc and therefore executes arbitrary shell code

This is unexpected to me.  I would not expect a user with only db_owner to be able to achieve this.  (Obviously assuming xp_cmdshell has already been enabled by a sysadmin.)

When the database owner is changed from sa to another low privileged login, then the user stored proc is no longer able to call xp_cmdshell.

USE MASTER;
CREATE DATABASE testdb;
CREATE LOGIN testuser WITH PASSWORD = 'password', CHECK_POLICY=OFF;
CREATE LOGIN dummyuser WITH PASSWORD = 'password', CHECK_POLICY=OFF;
SELECT * from sys.credentials WHERE NAME LIKE '%cmdshell%';
-- returned: 101 ##xp_cmdshell_proxy_account## .....

USE testdb;

EXEC sp_changedbowner 'sa';
CREATE USER testuser FOR LOGIN testuser;
ALTER ROLE db_owner ADD MEMBER testuser;

EXECUTE AS LOGIN = 'testuser';
GO

CREATE PROCEDURE [dbo].[testproc]
WITH EXECUTE AS OWNER
AS
    SELECT SUSER_NAME() as [SUSER_NAME()], USER_NAME() as [USER_NAME()];
    exec xp_cmdshell 'echo %time%';
GO

SELECT SUSER_NAME() as [SUSER_NAME()], USER_NAME() as [USER_NAME()];

EXEC dbo.testproc;
-- returned: sa dbo  proving that the call to xp_cmdshell has succeeded

EXEC xp_cmdshell 'echo %time%';
-- returned: The EXECUTE permission was denied on the object 'xp_cmdshell'

REVERT

EXEC sp_changedbowner 'dummyuser';
EXECUTE AS LOGIN = 'testuser';
EXEC dbo.testproc;
-- returned: The EXECUTE permission was denied on the object 'xp_cmdshell'
-- proving that the sysadmin role of the database owner is relevent

REVERT

Note that I have not granted execute xp_cmdshell permission to any particular user.

I thought that enabling xp_cmdshell was ok if care was taken to only grant execute xp_cmdshell permission carefully, but my example seems to show otherwise.

Since a sysadmin is often a database owner, does this example show a serious security problem, or am I misunderstanding something?

does this example show a serious security problem, or am I misunderstanding something?

You are misunderstanding what is actually going on here.

  the database owner is changed from sa to another low privileged login, then the user stored proc is no longer able to call xp_cmdshell.

That is true in this case, but that is merely due to how you did (or more accurately: did not) configure things (more on that in a moment).

  I thought that enabling xp_cmdshell was ok if care was taken to only grant execute xp_cmdshell permission carefully,

It is.

  but my example seems to show otherwise.

I would disagree with that. This is just a matter of not understanding the security mechanism, and this does happen to be a tricky case of it, so that is quite understandable.

A few things:

EXECUTE AS OWNER is not specifically the issue. You could have done EXECUTE AS N'dbo' to get the same effect.
The database being owned by a sysadmin is not specifically the issue. You could have done EXECUTE AS N'other' where other is the name of a User that is associated with a Login that has a User in [master] that has been granted execute on xp_cmdshell to get the same effect.
No, server level permissions are not being granted. To prove this, add the following two lines to your test stored procedure:

EXEC sp_configure N'allow updates', 1; RECONFIGURE;
SELECT * FROM sys.dm_exec_sessions;

Go ahead and change the db owner back to sa and execute the proc. You will get errors on the sp_configure and the RECONFIGURE, and you will only get 1 row back from sys.dm_exec_sessions, your session's row, because you do not have the VIEW SERVER STATE server level permission, something sa definitely has.

What you are experiencing is a function of two things:

You enabled the proxy account which lets non-sysadmins accomplish this if they have execute permission (not sure why you enabled this, unless you don't want to be using the SQL Server service account, which is a good idea, but doesn't work for sysadmins)
the DB was owned by a login that was associated with a user present in master that was granted permission to exec xp_cmdshell (this associated is derived from the EXECUTE AS combined with the DB ownership)

To prove this, exec the following (at the end of your current test code in the question, with dummyuser owning the DB):

REVERT;
USE [master];
CREATE USER [dummyuser] FOR LOGIN [dummyuser];
GO
GRANT EXEC ON xp_cmdshell TO [dummyuser];
USE [testdb];

Then do:

EXECUTE AS LOGIN = 'testuser';
EXEC dbo.testproc;

and you will now, instead of getting the permission denied error, either have success with xp_cmdshell, or you will get the following error if the proxy account is not configured:

  Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line YYYYY [Batch Start Line XXXXX]
  The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Even better would be to do the following, which removes the permission from dummyuser, creates a new low-privileged account, and grants that account the xp_cmdshell permission:

USE [master];
CREATE LOGIN [cmdshell] WITH PASSWORD = 'password', CHECK_POLICY=OFF;
CREATE USER [cmdshell] FOR LOGIN [cmdshell];
GRANT EXEC ON xp_cmdshell TO [cmdshell];

REVOKE EXEC ON xp_cmdshell FROM [dummyuser];

USE [testdb];
CREATE USER [cmdshell] FOR LOGIN [cmdshell];

And then, change the EXECUTE AS clause in the stored procedure to be WITH EXECUTE AS 'cmdshell'. Execute the proc again and you will either have success or the proxy account error if it isn't set up. In this case:

the database owner does not have the permission
you aren't tied in any way to a sysadmin login.

This works because the EXECUTE AS user is associated with a login that has a user in master that has been granted execute on xp_cmdshell.

ALSO, this behavior is most likely tied to the object (i.e. xp_cmdshell) truly existing in the mssqlsystemresource database. You can't create an object in [master], grant a user (the same being used as the EXECUTE AS user of the stored procedure, even if that user is dbo / OWNER and the database is owned by sa) EXEC on the user proc in [master], and have it work (not without enabling TRUSTWORTHY, which you shouldn't do). Not even if the user stored procedure in [master] is marked as a system stored procedure (I've tried).

Of course, ideally you would use Module Signing to take care of the permission instead of EXECUTE AS (either associate with sysadmin server role to use SQL Server service account, or associate with user in [master] that has been granted EXECUTE on xp_cmdshell to use proxy account, assuming that it has been configured).


Is it Possible to Overload SQL Server Functions? Is it possible to overload a sql server function? Either a scalar, like ltrim, or an aggregate function, like count?

Even if this was a really, really, bad idea. Is it possible?

Somewhat of a duplicate of T-SQL User defined function overloading? I would say it isn't 100% a duplicate, since that was for 2005 version. Maybe this has changed?

There is no straight-forward way of carte-blanche overriding a built-in function in SQL Server.

You can kind of fake it by creating a function with the same name in a different schema, then calling that function with the schema name, as in:

SELECT dbo.COUNT(1)
FROM dbo.SomeTable st
GROUP BY st.SomeCol;

However, this is quite likely to cause more confusion than it is worth.


Converting a Crystal Report Formula to a CASE expression With attempting to convert a crystal report formula into a SQL CASE expression, I seem to be having trouble understanding the concept of "total_pallet_weight" and "order_no" utilizing the "AND" in the formula with "cubic feet" and "order_no". I thought with my attempt below I would start out with a CASE just for "total_pallet_weight" then have a sub CASE for cubic feet to represent the "AND" in the formula. But I'm not to familiar with how the syntax "({a_ras_shipping_order_PCF_vw.total_pallet_weight},
{a_ras_truck_shipment_vw.order_no}) > 0" works. Is the forumla saying "total pallet weight + order_no > 0"?

Crystal Report Formula:

IF Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight},
{a_ras_truck_shipment_vw.order_no}) > 0
AND Sum ({a_ras_shipping_order_PCF_vw.cubic_feet},
{a_ras_truck_shipment_vw.order_no}) > 0
THEN
Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight},
{a_ras_truck_shipment_vw.order_no})
/Sum ({a_ras_shipping_order_PCF_vw.cubic_feet},
{a_ras_truck_shipment_vw.order_no})
ELSE 0

Attempt at replacing formula:

CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0
     THEN
        ,CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
         TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0
             THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) +
             (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
             TPM.dim_ext_x * TPM.dim_ext_y) / 1728)))
             ELSE 0
        END
     ELSE 0
  END AS 'Total_PCF'

Syntax for "total pallet weight"

SUM (TCT.[weight]) + SUM(TPM.[weight]) AS 'Total_Pallet_Weight'

Syntax for "cubic feet"

CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND
TPM.dim_ext_y > 0
THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x *
TPM.dim_ext_y) / 1728)
ELSE 0 END AS 'cubic_feet',

It looks as though "order_no" is not used a reference for the CASE expression unlike the formula. With making some minor tweaks and replacing my "+" in my sub CASE with "/" I received the correct outcome.

CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0
     THEN
        CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
             TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0
             THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) /
             (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
             TPM.dim_ext_x *
             TPM.dim_ext_y) / 1728)))
             ELSE 0
        END
     ELSE 0
  END AS 'TOTAL_PCF'


Why is my biggest segment resulting in an empty table? I'm trying to identify top tables by size in my Oracle DB, I tried with

select  segment_type,
        segment_name,
        sum(bytes/1024/1024) MB

from    user_segments s

where   segment_type like 'TABLE%'

group by segment_type,segment_name
order by 3 desc
;

and my top biggest table is ABC_TABLE. Querying USER_EXTENTS for the table

select  *
from    user_extents e
where   segment_name = 'ABC_TABLE'
;

I get 218 records, and querying USER_SEGMENTS again for this specific table this is what I get:

 SEGMENT_NAME   | PARTITION_NAME | SEGMENT_TYPE | SEGMENT_SUBTYPE | TABLESPACE_NAME | BYTES      | BLOCKS | EXTENTS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | MAX_SIZE   | RETENTION | MINRETENTION | PCT_INCREASE | FREELISTS | FREELIST_GROUPS | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE
 ABC_TABLE      |                | TABLE        | ASSM            | ABC             | 2341470208 | 285824 | 218     | 65536          | 1048576     | 1           | 2147483645  | 2147483645 |           |              |              |           |                 | DEFAULT     | DEFAULT     | DEFAULT

What I don't understand is that by querying ABC_TABLE no records are shown, it seems to be empty. What am I getting wrong?

You might have empty blocks below the high water mark (HWM). Delete statement, by default, doesn't lower the HWM; however, TRUNCATE, CTAS to name few do (down to MINEXTENTS on the table). Thus the empty blocks are considered as used blocks.

Simple Demo:

SQL> create table t1(col1 varchar2(100));

Table created.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
     65536

SQL> insert into t1 select 'sample text of line'||level from dual connect by level<=2000;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
    131072

SQL> delete from t1;

2000 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
    131072

You see, the size of the table is still same though the table is empty.

SQL> truncate table t1;

Table truncated.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
     65536


How to change a user's name in SQL Server 2000? I'm not asking how to change the user's login, which is doable with sp_change_users_login, but really the user's name (which comes under the Name column of the list of users in Enterprise Manager).

Is there a stored procedure to do it? Some other command?

In SQL Server 2000, I think you'll have to drop and re-create the user, unless you want to reply on crazy hacks like this one:

Rename User Login

I don't really recommend that, both because updating system catalogs is dangerous, and also because it uses undocumented, unsupported, and buggy sp_MSForEachDB (see why I urge you to stay away from that one).

In versions newer than SQL Server 2000, it is as simple as:

ALTER USER foo WITH NAME = bar;


Unique triplets from pairs I have a table (mysql) in the following pairs' structure:

 home   | door
 table  | window
 door   | table
 window | home
 home   | table
 table  | door

I'm looking for the best solution (fast and logical, to run on large tables) to get all unique triplets i.e home is paired with door and also with window so that's a triplet, and it's cardinal order so window,door,home is not distinct from door,home,window etc.

Wanted results:

home  | door   | window
home  | door   | table
home  | window | table
table | door   | window

Not sure I understand the problem properly but here is a sketch that might get you going:

create table t
( a char(10) not null
, b char(10) not null
,     primary key(a,b));

insert into t (a,b)
values ('home','door')
      ,('table','window')
      ,('door','table')
      ,('window','home')
      ,('home','table')
      ,('table','door');

For simplicity, we define a view so that a < b:

create view v as
    select distinct least(a,b) as a, greatest(a,b) as b from t;

Now we know that we can chain v via a self-join. As ybercube pointed out:

select v1.a, v1.b, v2.b
from v as v1
join v as v2
    on v1.b = v2.a;

won't work if we have the tuples (a,b),(a,c). So we add other connections as:

select distinct v1.a, v1.b, v2.b
from v as v1
join v as v2
  on v1.a in (v2.a, v2.b) or v1.b in (v2.a, v2.b)
where v1.a<v1.b
  and v1.b<v2.b;

Needless to say, this will be very inefficient for large tables.