How to find out if anything is running as or relies upon a user account in anyway before removing it?

I don't think there's a surefire method to find everything. After all, they could have access to things merely by virtue of being in a specific server or database role, or even a Windows AD group (you didn't specify if this is a SQL auth login or a Windows login). There also isn't a surefire way to identify what might break if this is changed - for example, if they are the owner of a job or database, will changing that to a different owner cause any problems? Maybe, maybe not. I can think of a few things that are easy to find, but not really any way to verify they won't break without actually changing it and seeing what breaks.

For initial discovery, and this is by no means exhaustive (just a start for the most common things off the top of my head). Note that I've broken these into chunks for readability, but you'll want to fire them all at once to reuse the variables declared at the beginning.

SET NOCOUNT ON;
GO

USE [master];
GO

DECLARE @name NVARCHAR(128) = N'some login name',
---------- change this ---------^^^^^^^^^^^^^^^
        @sid VARBINARY(85),
        @pid INT,
        @sql NVARCHAR(MAX) = N'';

-- figure out their sid and *server* principal_id
SELECT @sid = [sid], @pid = principal_id
  FROM sys.server_principals
  WHERE name = @name;

-- linked server logins

SELECT [linked_servers] = COALESCE(remote_name,
  N'<<LOCAL>> (' + @@SERVERNAME + ')')
  FROM sys.linked_logins
  WHERE local_principal_id = @pid;

-- databases with authorization rights

SELECT [databases_owned] = name
  FROM sys.databases WHERE owner_sid = @sid;

-- jobs owned by this login

SELECT [jobs_owned] = name
  FROM msdb.dbo.sysjobs
  WHERE owner_sid = @sid;

-- jobs with this login having an execute as step

SELECT [jobs_with_steps_having_login_as_proxy] = j.name
  FROM msdb.dbo.sysjobs AS j
  INNER JOIN msdb.dbo.sysjobsteps AS s
  ON j.job_id = s.job_id
  INNER JOIN msdb.dbo.sysproxylogin AS proxy
  ON s.proxy_id = proxy.proxy_id
  WHERE proxy.[sid] = @sid
  GROUP BY j.name;

-- server roles they are member or owner of

SELECT [member_or_owner_of_server_roles] = r.name
  FROM sys.server_role_members AS rm
  INNER JOIN sys.server_principals AS p
  ON p.principal_id = rm.member_principal_id
  INNER JOIN sys.server_principals AS r
  ON r.principal_id = rm.role_principal_id
  WHERE p.[sid] = @sid OR r.owning_principal_id = @pid;

-- server permissions that have been granted or revoked

SELECT [server_permission] = [permission_name], state_desc
  FROM sys.server_permissions
  WHERE grantee_principal_id = @pid;

-- roles in database

SELECT @sql = N'';

SELECT @sql += ' UNION ALL
SELECT [database] = ''' + name + ''',
[member_or_owner_of_database_roles] = r.name
  FROM ' + QUOTENAME(name) + '.sys.database_role_members AS rm
  INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
  ON p.principal_id = rm.member_principal_id
  INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS r
  ON r.principal_id = rm.role_principal_id
  WHERE p.[sid] = @sid OR r.owning_principal_id =
    (SELECT principal_id FROM ' + QUOTENAME(name)
    + '.sys.database_principals WHERE sid = @sid)'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- permissions in database

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT
  [database] = ''' + QUOTENAME(name) + ''',
  [permission] = state_desc
     + '' '' + [permission_name],
  [object] = COALESCE(QUOTENAME(s.name)
    + ''.'' + QUOTENAME(o.name) + '' (''
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS)
    + '')'', p.class_desc)
FROM ' + QUOTENAME(name) + '.sys.database_permissions AS p
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON p.major_id = o.[object_id]
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- modules in database with EXECUTE AS

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT
  [database] = ''' + QUOTENAME(name) + ''',
  [object_with_execute_as] = QUOTENAME(s.name)
    + ''.'' + QUOTENAME(o.name) + '' (''
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS)
    + '')''
FROM ' + QUOTENAME(name) + '.sys.sql_modules AS m
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON m.execute_as_principal_id = dp.principal_id
INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON m.[object_id] = o.[object_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- objects in database the login "owns" (ALTER AUTHORIZATION)

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT
  [database] = ''' + QUOTENAME(name) + ''',
  [object_with_auth] = QUOTENAME(s.name)
    + ''.'' + QUOTENAME(o.name) + '' (''
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS)
    + '')''
FROM ' + QUOTENAME(name) + '.sys.database_principals AS dp
INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON o.[principal_id] = dp.[principal_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- schemas in database the login "owns"

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT
  [database] = ''' + QUOTENAME(name) + ''',
  [schemas_owned] = QUOTENAME(s.name)
FROM ' + QUOTENAME(name) + '.sys.schemas AS s
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON s.principal_id = dp.principal_id
WHERE dp.[sid] = @sid'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

I am sure there are others that could be added to the list - I have no problem helping figure out the query if you tell me what those things are.


SSRS 2008 Report toggling between images for various field values

You can use expressions for this type of conditional work. To do this click on the image and select the properties tab in BIDS. Find the expression button (fx) and click it. Enter something like the below to get what you want:

  =Iif(Fields!Gender.Value = "Female", "Female.jpg", "Male.jpg")

This is assuming that you have have set up the images to be available as global resources to your report. If not, replace the image names with the paths to the images etc...

A good reference for expressions is below:

http://msdn.microsoft.com/en-us/library/ms157328.aspx

I hope this helps you.


Can I improve the way pg_dump exports inherited tables?

You could mess with the pg_attribute catalog table and set attislocal to FALSE, but modifying the system catalogs is unsupported and dangerous.

In my opinion, that is not warranted for a mere esthetic improvement. The dump works just as fine as it is.


Is there a way to see what locks a stored procedure obtains?

Stored procedures don't acquire locks. Queries do. But you can't really detect which query obtained a lock. You can only see which session/transaction is holding a lock.


select members with the longest running absence streak

This is your "general idea" translated to SQL

select m.id, m.name, max(a.datetime_attended) as max_attened
from attendance as a
join members as m
  on m.id = a.member_id
group by m.id, m.name
order by max_attened
limit 7


How to restart server without rebooting system

On CentOS server, you can use these below commands:

PostgreSQL 9.6:

/bin/systemctl restart postgresql-9.6.service

PostgreSQL 10.x

/bin/systemctl restart postgresql-10.service

PostgreSQL 11.x

/bin/systemctl restart postgresql-11.service


How do I count the number of columns in each table?

You can look at columns in sys.columns:

  Returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns:

Table-valued assembly functions (FT)
Inline table-valued SQL functions (IF)
Internal tables (IT)
System tables (S)
Table-valued SQL functions (TF)
User tables (U)
Views (V)

For columns count in tables, this query can be used:

SELECT [Schema] = s.name
    , [Table] = t.name
    , number = COUNT(*)
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.name, s.name

sys.tables can be replaced by sys.views for count in views
sys.objects can also be used with a WHERE clause on required type(s):

SELECT [Schema] = s.name
    , [Table] = o.name
    , number = COUNT(*)
    , o.type_desc
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE type IN (
    'U'     --> U = Table (user-defined)
    , 'V'   --> V = View
    , ...)
GROUP BY o.name, s.name, o.type_desc;

This would work too although it is preferable (read The case against INFORMATION_SCHEMA views from Aaron Bertrand) to use the first query:

SELECT TABLE_SCHEMA
    , TABLE_NAME
    , number = COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME;


Identical query, tables, but different EXPLAIN and performance

Just make a simple join. Sub-queries does not provide the best result quite often

EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf
ON uf.level_id = l.id
WHERE l.user_id = 2;


SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login?

Local temporary objects are separated by Session. If you have two queries running concurrently, then they are clearly two completely separate sessions and you have nothing to worry about. The Login doesn't matter. And if you are using Connection Pooling that also won't matter. Local temporary objects (Tables most often, but also Stored Procedures) are safe from being seen by other sessions.
While your code has a single, common name for the local temporary objects, SQL Server appends a unique string to each object name per each session (and if needed, per each sub-process) to keep them separate. You can see this by running the following queries in SSMS:
CREATE TABLE #T (Col1 INT)

SELECT * FROM tempdb.sys.tables WHERE [name] LIKE N'#T%';

You will see something like the following for the name (I removed most of the underscores from the middle of the name to prevent the need to scroll here):
#T_______________00000000001F

Then, without closing that query tab, open up a new query tab and paste in those same 2 queries and execute them. You should now see something like the following:
#T_______________00000000001F
#T_______________000000000020

So, each time your code references #T, SQL Server will translate it to the proper name based on the session. It is all handled auto-magically :-).
To illustrate this point. I have worked on systems that are highly transactional (thousands of transactions per second) and were SaaS (Software as a Service) web apps running 24 / 7. All T-SQL code was in Stored Procedures (meaning: same local temp table name for every execution of that code) and we made pretty good use of local temporary tables. Being a web app, the Login was the same across nearly all connections and we definitely used connection pooling. We never had any problems indicating any cross-Session access of same-named local temporary objects. And quite frankly, we would have been shocked and making use of our support contract with Microsoft to get it fixed if that ever did happen.
Additional points to keep in mind regarding local temporary tables:

While their names are unique, their dependent objects are not. You cannot create Triggers or Foreign Keys on temporary tables, so this is really about Primary Keys, Check Constraints, and Default Constraints. This means that naming a Primary Key #PK_#T does not make it a unique name with a behind-the-scenes unique id appended to the name. If you attempt this, you will get "Cannot create object. Object already exists." errors (well, assuming multiple concurrent executions this same code). So, if you need any of these 3 object types for a local temporary table, create the dependent object(s) inline so it/they will get system-generated names which will be unique and not conflict between Sessions.
You don't need to worry about Index names since those are already separated per [object_id].

Given that you get "Cannot create object. Object already exists." errors when creating named dependent objects, that brings up a good point that is most often overlooked regarding this question of "are local temporary objects completely isolated per session": If local temporary tables could be seen by other Sessions, then the CREATE TABLE statement would get an error, right?

A nuance of local temporary tables (that is also a loose counter-point to #2 above) is that if you reference a local temporary table in a sub-process (i.e. EXEC) that was created prior to that sub-process starting, it will be able to see (and even modify) that local temporary table. HOWEVER, if that sub-process creates another temporary table having the same name, then there will be two objects, with separate unique IDs appended to the end, that are accessible via the short name. I recall reading somewhere that there is no guarantee that referencing #name in the sub-process will always resolve to the version of that object that was created in the sub-process. Hence, it is best to have some uniqueness of local temp table names between Stored Procedures that have any possibility of being executed in a chain of nested Stored Procedure calls.


What is the purpose of BACKUP WITH NAME?

This merely allows you to look up a backup by name, using:

SELECT * FROM msdb.dbo.backupset WHERE [name] = N'the_name_you_used';

This can be useful as a kind of tagging mechanism, e.g. if you specify backups for different things (such as restoring to dev vs. initiating log shipping vs. shipping off-site). Much easier than relying solely on the file name (which can be changed in Windows after the backup is complete), or the file number when you've appended multiple backups to the same file.


all tables used by a stored procedure and all sps that use a particular table - recursive cte

the following script comes from this question
and it has worked fine for my tests
using sql server 2005 (stored procedures inside other stored procedures)

example of output:

this is the code:

--=========================================================================

--https://stackoverflow.com/questions/15072445/query-to-recursively-identify-object-dependencies

--== utlGetAllDependentObjectsRecursive - Uses recursive common table
--==     expression to recursively get all the dependent objects as well
--==     as the child objects and child's child objects of a
--==     Stored Procedure or View or Function.  can be easily modified to
--==     include all other types of Objects

--Wrote this Stored Procedure below which RECURSIVELY lists all
--the dependent child objects and child's dependent objects and child's child...etc.

--The input parameter can be Stored Proc, User Function, View.

--Can easily be altered to get a Unique List of Column 5, regardless of what Level the Object
--was called and how deep and by which object.

--COLUMNS

--UsedByObjectId - The parent object that uses the dependent object
--UsedByObjectName - The name of the parent object
--UsedByObjectType - Type of the parent object (P,V,FN)
--DependentObjectId - The child object the parent uses
--DependentObjectName - Name of the child object
--DependentObjectType - Type of the dependent child object (P,V,FN, U)
--Level - How deep, the nested recursive level which the object is used

--=========================================================================

USE US15WINMPRODUCT
GO

DECLARE
      @sp_name nvarchar(128) -- NULL shows all stored procedures that use the table @table
     ,@table   nvarchar(128)

SELECT
      @sp_name = NULL
     ,@table   = 'ProductItemDetailsDenorm'

   -- Supports Stored Proc, View, User Function, User Table
declare @PARAM_OBJECT_NAME VARCHAR(500)

select @PARAM_OBJECT_NAME = 'sp1'
--select @PARAM_OBJECT_NAME = 'ProductItemDetailsDenorm'

   ; WITH CTE_DependentObjects AS
    (
        SELECT DISTINCT
        b.object_id AS UsedByObjectId,
        b.name AS UsedByObjectName, b.type AS UsedByObjectType,
        c.object_id AS DependentObjectId,
        c.name AS DependentObjectName , c.type AS DependenObjectType
        FROM  sys.sysdepends a
        INNER JOIN sys.objects b ON a.id = b.object_id
        INNER JOIN sys.objects c ON a.depid = c.object_id
        WHERE b.type IN ('P','V', 'FN') AND c.type IN ('U', 'P', 'V', 'FN')
    ),
    CTE_DependentObjects2 AS
    (
       SELECT
          UsedByObjectId, UsedByObjectName, UsedByObjectType,
          DependentObjectId, DependentObjectName, DependenObjectType,
          1 AS Level
       FROM CTE_DependentObjects a
      WHERE a.UsedByObjectName = @PARAM_OBJECT_NAME

       UNION ALL

       SELECT
          a.UsedByObjectId, a.UsedByObjectName, a.UsedByObjectType,
          a.DependentObjectId, a.DependentObjectName, a.DependenObjectType,
          (b.Level + 1) AS Level
       FROM CTE_DependentObjects a
       INNER JOIN  CTE_DependentObjects2 b
          ON a.UsedByObjectName = b.DependentObjectName
    )

    SELECT DISTINCT * FROM CTE_DependentObjects2
    ORDER BY Level, DependentObjectName


Log Shipping: two secondary DBs on the same instance - is this possible?

It is not possible.

Ref:

About Log Shipping

  SQL Server Log shipping allows you to automatically send transaction
  log backups from a primary database on a primary server instance to
  one or more secondary databases on separate secondary server
  instances.

  Provides a disaster-recovery solution for a single primary database
  and one or more secondary databases, each on a separate instance of
  SQL Server.

  The log can be shipped to multiple secondary server instances. In such
  cases, operations 2 and 3 are duplicated for each secondary server
  instance.


Query FROM Indexed View queries underlying table in Execution plan

The optimizer might choose to use the underlying table (it "expands" the view) based on how it chooses to implement your query.  To force the query to use the indexed view, you could add a WITH (NOEXPAND) hint to the FROM clause of the query where the view is referenced.

This is useful for two reasons:

It gets the query to use the view you want
It might show you why the optimizer chose not to use the view (maybe the index definition needs to be tweaked to get better performance, etc)

By the way, it's recommended to use this hint regardless when referencing indexed views directly, in order to avoid other problematic behavior.  See this article from Paul White for details: Indexed Views and Statistics


You could set a trigger to watch for a session coming in from phpmyadmin or a specific user. That trigger could fire once the session is created and enable autocommit. Since MySQL 5.7 still doesn't support all the DDL command you could use init_connect to drive the procedure when a user connects.

Here is a document outlining the init_connect operation. Logon Init_connect operation


InnoDB: "Only one log file found" after binary import

Assuming you have run innobackupex --apply-log successfully you either have a configuration problem (probably innodb_log_files_in_group is 1 in the original and 2 -or not set- in the restored server) or you lost ib_logfile1 somewhere in the process.

InnoDB is ok, it is normal for it to fail when it detects a different configuration on disk than on the file.

Please be aware that xtrabackup/innobackupex only copies data files, not the /etc/my.cnf.

Final answer: apply-log wasn't being executed successfully- a mistake while logging was concealing that. The actual error was due to a specific bug in an old version of Percona XtraBackup. Update to a newer version >= 2.1.8 should fix that.


How to execute system command in mysql stored procedure?

This cannot be accomplished. Why ?

According to Can MySql stored proc call a system cmd? (from July 19, 2009 11:47AM)

  This question is asked several times a week. The answers are (i) not out of the box, for very good reasons, (ii) you could write and link into your server a UDF which calls the operating system, but that would create a glaring security issue for your app.

For example, I have written about using system before : Why would I use the MySQL "system" command?. In my post, I showed how easy it would be to step into the operating system with

mysql> \! bash
[root@**** ~]# exit
exit
mysql>

Imagine what one could do in the OS because of such a breach.

Usually, stored procedures are used to prevent SQL injection. If system was allowed within a stored procedure, that eliminates such security.

You will have to run the script as follows

mysql> system /home/yogaraj/Documents/Maastry/firefox.sh

or

mysql> \! /home/yogaraj/Documents/Maastry/firefox.sh

This is all you can really do


Oracle DBAI Theory : is it REALLY not possible to have Oracle Database Corrupted after Instance Crash?

A dirty block can only be written to disk if all changes until the modification time of this block was already written to the redo logs. The Oracle 12g2 Database Concepts Guide says

  Before DBW can write a dirty buffer, the database must write to disk the redo
  records associated with changes to the buffer (the write-ahead protocol). If DBW
  discovers that some redo records have not been written, it signals LGWR to write
  the records to disk, and waits for LGWR to complete before writing the data
  buffers to disk.

So after a database was recovered it contains all modification until to a certain time and no modifications that where made after this time. If a transaction was committed the redo logs contain the modification oft his transaction and so its modifications where written to the data files during the recovery. Transactions that weren't committed either only modified memory structures (buffer cache) and so all there modifications are lost because of he crash, or they did some modifications of the file but then the undo information was also written to the files that contains the undo segments and this undo information will be used to undo the changes of this transaction.

case 3:  If a dirty block of a data file is written back to disk then all changes done before the modification of this block is already in the redo logs and so the undo information is already in the redo logs

case 1: If no user issues a commit that does not mean that changes are written to data file or redo log. But if something was written to disk it can be undone as described above.

case 2: Uncommitted changes must not be written to disk. It is only guaranteed that committed changes are written to disk.


MySQL hidden files on ubuntu taking up space

looks like this command worked for temporary solution:
PURGE BINARY LOGS BEFORE '2019-10-19 22:46:26'; #your date

(mysql manual)
Still working on the long term solution. Per mysql manual - these were the logs of select statements (which are typically huge in my case). Not 100% yet on whether I need these :) In either case, thank you everyone for helping troubleshoot. This was blowing my mind for a bit, but all it took was browsing into the folder itself, and seeing hundreds of huge files!


How to solve this complex insertion problem

Setup:

create table policycase
(requested_id int
,status       varchar(30));

insert into policycase values (438957,'HISTORICAL');
insert into policycase values (451423,'ARCHIVED');

create table policy
(id           int
,uniqueid     varchar(10));

insert into policy values (438957, '91157235');
insert into policy values (451423, '91157235');

create table policy_map
(policy_id    int
,map_key      varchar(100)
,map_value    varchar(30));

insert into policy_map values (438957, 'isPropertyOwner'               , 'TRUE');
insert into policy_map values (438957, 'livingAtProperty'              , 'TRUE');
insert into policy_map values (438957, 'stateOfMaintenance'            , 'GOED');
insert into policy_map values (438957, 'stateOfMaintenanceRoof'        , '0');
insert into policy_map values (438957, 'unsecuredGuaranteeHouseContent', 'J');
insert into policy_map values (438957, 'unsecuredGuaranteeHouseFire'   , 'J');

insert into policy_map values (451423, 'isPropertyOwner'               , 'TRUE');
insert into policy_map values (451423, 'livingAtProperty'              , 'TRUE');
insert into policy_map values (451423, 'stateOfMaintenance'            , 'GOED');

Before insert:

SELECT
    policycase.status,
    policy.id,
    policy.uniqueid,
    policy_map.map_key,
    policy_map.map_value
FROM policycase
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'
order by policy.uniqueid,policy.id,policy_map.map_key;

status     |     id | uniqueid | map_key                        | map_value
---------- | ------ | -------- | ------------------------------ | ---------
HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE
HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE
HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED
HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J
ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE
ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE
ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED

The proposed INSERT:

insert into policy_map

select p2.id,
       pm1.map_key,
       pm1.map_value

from   policycase pc1
join   policy p1
on     pc1.requested_id = p1.id

join   policy_map pm1
on     pm1.policy_id = p1.id

-- join with p2/pc2 to obtain id for ARCHIVED rows
join   policy p2
on     p2.uniqueid  = p1.uniqueid

join   policycase pc2
on     pc2.requested_id = p2.id

where  p1.uniqueid = '91157235'
and    pc1.status  = 'HISTORICAL'
and    pc2.status  = 'ARCHIVED'

and    not exists(select 1
                  from   policycase pcx
                  join   policy px
                  on     pcx.requested_id = px.id

                  join   policy_map pmx
                  on     pmx.policy_id    = px.id

                  where  px.uniqueid      = p1.uniqueid
                  and    pcx.status       = 'ARCHIVED'

                  and    pmx.map_key      = pm1.map_key
                  and    pmx.map_value    = pm1.map_value);

NOTE: There may be a way to eliminate some of the joins but I'll leave it like this for now as it's easier (for me) to understand what's going oin.

After insert:

SELECT
    policycase.status,
    policy.id,
    policy.uniqueid,
    policy_map.map_key,
    policy_map.map_value
FROM policycase
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'
order by policy.uniqueid,policy.id,policy_map.map_key;

status     |     id | uniqueid | map_key                        | map_value
---------- | ------ | -------- | ------------------------------ | ---------
HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE
HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE
HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED
HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J
ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE
ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE
ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED
ARCHIVED   | 451423 | 91157235 | stateOfMaintenanceRoof         | 0
ARCHIVED   | 451423 | 91157235 | unsecuredGuaranteeHouseContent | J
ARCHIVED   | 451423 | 91157235 | unsecuredGuaranteeHouseFire    | J

And here's a dbfiddle


Migrate small sets of data from production to staging/sandbox

The first thing you need to identify in your db are which tables require all their data (the seed data, sometimes called "domain" data), and which tables are suitable for bringing only recent data. You have a couple choices then.

1: Use a tool like SSDT to deploy your SCHEMA to dev & staging, keep the domain data as a bunch of INSERT statements in SSDT and apply those, and then write a custom SSIS package (or use the data transfer wizard, which is just SSIS) to pull only the useful, recent data over the wire.

PRO: this will definitely meet your requirement to pull only the
minimum necessary data.
CON: lots of queries will go over the wire on SSIS, have
to develop separate packages for pretty much every table. Have to keep the packages up-to-date as schema changes hit your db.

2: Backup the whole database, restore it somewhere else, and run a purge script. Then backup the purged db and restore this to dev & staging.

PRO: a pretty simple solution, requiring only DELETE scripts for the older data.
CON: requires another server with enough space to restore your full
backup, lots of time taken in first restore, probably time taken in
delete.

3: Partition your big tables so that data older than a month goes to a new filegroup, and use the Piecemeal restore option to only restore your PRIMARY filegroup.

PRO: A very simple restore process.
CON: Requires design work before the fact to partition the tables.

In our production environment, we have 1.5TB database, and we use option 2. I have tested option 3 with smaller db's and it does work rather well. Option 1 requires no changes to your db, but seems like the most work to me.


PostgreSQL: how do I query over a column with a custom range?

The range type would allow best performance and simple queries.

If you have some string values like k and Pre-K mixed with numeric values, just create a grade table with a rank value (an integer value representing actual sort order):

CREATE TABLE grade(
   grade text PRIMARY KEY
  ,grade_int UNIQUE int NOT NULL
);

Your numeric grades can conveniently be assigned the same grade_value, other grades get integer values according to their rank.

You might write a simple function to convert ranges with your original string values to their integer representations on the fly ...
And vice-versa to display your grades_served column where needed.

Convert grades_served to an int4range type.
Using the overlap operator &&, your query for a range of grades can then simply be:

SELECT * FROM tbl WHERE grades_served && '[1,4]'::int4range;

Or, for a single grade, the "contains element" operator @>

SELECT * FROM tbl WHERE grades_served @> -1;

Index

If your table is not very small I suggest you create a GiST index to support that (and so does the manual).

CREATE INDEX tbl_grades_served_idx ON tbl USING gist (grades_served);


How can I force MySQL to IGNORE ALL indexes?

It's absolutely not clear why you want this but you can use the hint USE INDEX () to tell the optimizer not to use any index. From MySQL docs: index hints

  It is syntactically valid to omit index_list for USE INDEX, which means “use no indexes.” Omitting index_list for FORCE INDEX or IGNORE INDEX is a syntax error.

Your query becomes:

SELECT count(*) AS c,
       substring_index(domain_name, '.', -1) AS tld
FROM domains_import
       USE INDEX ()        -- use no indexes
GROUP BY tld
ORDER BY c DESC
LIMIT 100 ;

Side note: the complex expression:

SUBSTRING(domain_name, LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2)

can be simplified from 4 function calls to 1:

SUBSTRING_INDEX(domain_name, '.', -1)


SQL Server service permissions on network share depending on IntegratedSecurity=true/false

The problem is due to how security is handled for bulk operations and the default limitations of Impersonation.
When connecting with a SQL Server Login, there is no external Windows SID / security context, so SQL Server uses the security context of the service account when needing to reach outside of SQL Server (i.e. interact with the OS, network, etc.). So, if the SQL Server service account is the Local System account, then that is the account used for external access. Since your file is on the network, it would have to be that "Local System" has access to that network share. This is possible by giving the server itself access (by specifying <domain_name>\<computer_name>$ for the NTFS permissions). In this case, the SQL Server process is not using Impersonation: the external access is being made in the security context of the process itself.
When connecting to SQL Server using a Windows Login (whether AD account or local account on the server), there is an SID to use, and SQL Server will use that SID (Security ID that points to the Windows / AD account) for external access. This is what happens when connecting with "Integrated Security" / "Trusted_Connection" = true. Now, using the SID of the account logging into SQL Server means that SQL Server is using Impersonation to make it's network request appear to be coming from that other SID instead of from "Local System" (remember, the service account owns the sqlservr.exe process, and it is that process, not the SID logging into SQL Server, that is making the network request). Impersonation, by default, is quarantined to the machine in which the process was started (similar to how using Impersonation via EXECUTE AS user is, by default, quarantined to the Database). So, the BULK INSERT / OPENROWSET(BULK... commands should be able to see files locally on the server that SQL Server is running on, but network shares are not local, and the impersonated security context, by default, does not extend that far. What you are seeing here should be the same thing as the infamous "double-hop" issue.
So, you can do one of the following:

give permission on that network share to EVERYONE

continue logging in via the SQL Server Login and not worry about using Integrated Security / Trusted Connection.

Enable "Delegation", though I am not sure if that will work on a Local System account (you mentioned SQL Server Agent running as "Local System", so this is what would be connecting to SQL Server). It might require setting up an AD account and enabling that account for "Delegation" so that it can go beyond the local server. Either way, "Delegation" is how one configures AD to allow an account (maybe even an account / service combination) permission to go beyond the default quarantine.
P.S. @StrayCatDBA mentioned that using the Network Service account (i.e. NT AUTHORITY\NETWORK SERVICE allows for Delegation. Also, please see the "Configure Windows Service Accounts and Permissions" link at the bottom as that documentation goes over the various options you have.

Some info here:

Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (the Security considerations section; link should go directly there)
Kerberos Constrained Delegation Overview
How to Configure the Server to be Trusted for Delegation
How to Implement Kerberos Constrained Delegation with SQL Server 2008
Configure Windows Service Accounts and Permissions


Do you have access to the root account? Are you pointing to the right host? Sometimes it is useful to identify the host with the -h option to avoid DNS issues

mysql -u root -pSOMEPASSWORD -h127.0.0.1

If you still can't get into MySQL, you can reset the root password by starting MySQL with
--skip-grant-tables option

Stop MySQL

In Linux, as root user or equivalent

/etc/init.d/mysqld stop

Start MySQL with --skip-grant-tables option, you can find the mysqld_safe binary file with the which command

which mysqld_safe

/usr/bin/mysqld_safe --skip-grant-tables &

Login to MySQL without password

mysql -u root

Login to MySQL database

use mysql;

Update the root user password

update user set password=PASSWORD("mynewpassword") where User='root';

This will reset the password for any account called root, regardless of the host.

Flush privliges

flush privileges;

quit

Stop and Start MySQL

/etc/init.d/mysql stop
/etc/init.d/mysql start

If you are using Windows, you can simply update the my.cnf file with this option under the [mysqld] section

[mysqld]
skip-grant-tables

If you are not sure where the my.cnf file is, you can find the path by going to Administrative Tools available in Control Panel

In this case, you would most likely find the my.cnf file in a subdirectory of C:\Program Files\MySQL\MySQL Server 5.5\

And then restart MySQL using the Windows Services control panel in Administrative Tools.

From there you would be in safe mode and the commands would be the same.

update user set password=PASSWORD("mynewpassword") where User='root';
flush privileges;
quit

remove the skip-grant-tables from the my.cnf file and then restart MySQL

Conclusion

Now that you have root access again to MySQL, you can setup new users again


How do I rank the total score in order of positions

I hate giving complete solutions but here you go:

START TRANSACTION;
create table total_sc_tmp as (select student_id, SUM(total_score) tot_sc
FROM
    scores
GROUP BY student_id);
select student_id,  tot_sc,
FIND_IN_SET(
tot_sc
, (SELECT
    GROUP_CONCAT(
    distinct tot_sc
        ORDER BY tot_sc DESC) AS score
FROM
    total_sc_tmp)) as rank from total_sc_tmp;
  COMMIT;

reorder, use @row and avoid the tmp table if you want.
Happy Easter to all of you with constructive comments


How does my system understand if data got masked?

Data masking occurs just before the data is sent to the client (your program, SSMS, etc).  In that sense, it is "replaced and gone" by the time it gets to a client that is requesting the data to view it (assuming the user accessing the data doesn't have the UNMASK privilege).  Thus, you can perform logic on it in your queries (apply where clauses to it, update it, etc).  But you won't see the real data if you include it in a SELECT statement.
However, the data is still stored on the server unencrypted.  And in fact, you can use this knowledge to figure out exactly what data is in the table - even if you don't have the UNMASK privilege.  See Joe Obbish's great post on that topic here: Dynamic Data Unmasking
The difference between that and something like "Always Encrypted" is that only the encrypted version of the data is stored, and a key must be used to decrypt and use / view the data.


Using Service Broker internal to a single SQL Express instance?

From what I understand the limitation only applies when you attempt to send messages outside of the instance. Sending messages within the same instance is not limited by using Express. See the last reply in the following link:

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/3b03a610-8159-4be8-b2d8-e2abb4ba1225

I hope this helps you.


How to Add AFTER Delete Trigger in My case?

You should take the vbalues of OLD and use them in INSERT command.

Something like this:

CREATE TRIGGER triger_name_after_delete
AFTER DELETE
   ON resident FOR EACH ROW
BEGIN
   -- Insert record into Outpatient table
   INSERT INTO Outpatient (residentID, [rest columns names...] )
   VALUES ( OLD.residentID, OLD.[rest columns names...])
END;


Setting up indexes to support a flexible API search endpoint

InnoDB does not support HASH indexes; only B-TREE. You could certainly implement your own with another column called createdByHash. Use something like CRC32 as your hashing function to make it integer-based and save a few bytes per row. Index this column instead and make your queries SELECT .. FROM .. WHERE createdByHash = CRC32(<input>) AND createdBy = <input> (you have to have both parts because there could be collisions with CRC32)

Yes, MySQL uses multi-column indexes in left-to-right order. SELECT city, length, price FROM .. WHERE city = x will still use the index. If you have any range-based queries, the index should put those range'd columns to the right. An index on (A, B, C) and WHERE A=4 AND B>5 AND C=3 will only use the first two columns of that index.

If you are doing a simple greater-than/less-than on the lat/long, what you have will probably be OK. This one is tough since they are both ranged predicates. You may have to experiment on (lat, long) vs (long, lat) in your application.


Oracle's left join and where clauses errors

When you add WHERE filters to the optional/outer table, then you change the query into an INNER JOIN. You need to add the condition into the join, or derived table, or CTE.

select ATABLE1.column1, count(ATABLE2.column1)
    from ATABLE1 Left OUTER JOIN ATABLE2
         on ATABLE1.column1 = atable2.column1 AND atable2.column2 = '1'
    GROUP BY ATABLE1.column1;


Can I provide a default for a left outer join?

SELECT a.a1,b.b1,
    CASE WHEN b.b1 is NULL THEN 5 ELSE b.b2 END AS b2
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1


Prevent auto commit of DROP without SET AUTOCOMMIT OFF

DROP and TRUNCATE are Data Definition Language commands and thus cannot be rolled back. However, in Oracle you can use the following technologies to recover your table:

Flashback Drop
Flashback Database
Tablespace Point in Time Recovery
Table-Level Recovery From Backups (new in 12c)

Flashback Drop

If the Recycle Bin is enabled in your database, you can use the following command to recover your table (if it's still in the Recycle Bin).

SQL> flashback table1 to before drop;

If your table is not in the Recycle Bin (it was purged by the user manually or due to space pressure), you can attempt to recover your database from backup to another host and export/import the data and associated metadata to the production database using Data Pump Export and Import commands (or DBMS_DATAPUMP package).

Flashback Database

If you have Flashback Database enabled (check the flashback_database column in v$database view), you can flashback your database to the point before your table was dropped. But you must ensure that there's enough data in flashback logs which you can check using v$flashback_database_log view.

Tablespace Point in Time Recovery

To recover your table using tablespace point in time recovery (TSPITR), you can use the recover tablespace until ... command. But be sure to check what objects can be lost in the tablespace being recovered by checking SYS.TS_PITR_OBJECTS_TO_BE_DROPPED view. These objects were created after the recovery point you specify in this command. You would export them before TSPITR and re-import once TSPITR is complete.

Table-Level Recovery From Backups

New in Oracle Database 12c is the ability to recover individual tables. You employ it by using recover table command in RMAN, and it works very similar to TSPITR – creating auxiliary instance, – but on a more granular (table) level.

See also.

Using Flashback Drop and Managing the Recycle Bin in Database Administrator's Guide.
Using Flashback Database and Restore Points in Database Backup and Recovery User's Guide.
Performing RMAN Tablespace Point-in-Time Recovery (TSPITR) in Database Backup and Recovery User's Guide.
Table-Level Recovery From Backups in Oracle Database 12c Release 1 (12.1.0.1) New Features Guide.
Recovering Tables and Table Partitions from RMAN Backups in Oracle Database Backup and Recovery User's Guide.


What is the point of a SRID in a geometry type?

You are correct in your interpretation of a SRID.  It identifies the projection used to locate the Geometry or Geography.  Even if the database in question (I'm assuming SQL Server) doesn't make use of the SRID directly, the point of it is to identify how to work with the coordinates in relationship to others.  I suppose you could look at it like having a country code on a phone number.  Not always necessary, but important none the less.

Your understanding of Geometry vs Geography isn't correct.  Geometry is used to store objects which are projected to a Cartesian or Planar coordinate system.  They are not restricted to just 2 dimensions.  They use distance units to (metres, feet, etc) to represent points.
Geography is used to store objects that are projected on a Spherical coordinate system.  These systems use degrees (Latitudes and Longitudes) to represent points.

It is important to use the correct data type for your information.  If your objects have Latitude and Longitude coordinates, store them in a Geography.  This means when you use distance and area functions you will get a sensible result.

While some systems (SQL Server) mostly ignore the SRID in geometries, it is still important metadata and should be set correctly.  Other databases and software can and do use the SRID to determine how to treat the coordinates in the object, allowing for the coordinates to be reprojected to other coordinate systems and compared correctly to other objects that are projected differently.


ORACLE DB literal does not match format string

First you convert sysdate to a char type, then you convert it back to a date type without specifying the format string, which may or may not work based on the settings of the session running this code.

This is completely unnecessary, just use trunc(sysdate) to remove the hours, minutes and seconds.

Also, if you return a date type, then the format depends on the nls_date_format variable, not the SQL that produces the date value.


How to do the complex nested select SQL?

I have tested this query:

SELECT budget.aid,
       sum(CASE change_type.table2_id WHEN 1 THEN 1 ELSE 0 END) as login,
       sum(CASE change_type.table2_id WHEN 3 THEN 1 ELSE 0 END) as search,
       CASE budget.table2_id WHEN 2 THEN IF(LEFT(budget.message, 8) = 'increase', 1, -1) ELSE 0 END as budget
FROM table1 budget
LEFT JOIN table1 change_type
ON budget.aid = change_type.aid
AND change_type.date BETWEEN budget.date - INTERVAL 1 DAY AND budget.date
AND change_type.table2_id <> 2
WHERE budget.table2_id = 2
GROUP BY budget.aid, budget.date;

It was a fun query, I made it with joins, maybe the subselect version could be easier to understand. I perform SELF join to match budgets with other actions in the range requested. Please note that it filters other budget actions and that you will get an overlap if at least 2 budget actions are performed within 1 day.

To fill the second and third column, a sum of a CASE is performed after the grouping by aid and the reference date.

This is the result:

+-----+-------+--------+--------+
| aid | login | search | budget |
+-----+-------+--------+--------+
| 121 |     0 |      0 |      1 |
| 121 |     0 |      2 |      1 |
| 123 |     0 |      0 |     -1 |
+-----+-------+--------+--------+
3 rows in set (0.00 sec)

Please note that no login for the aid 123 so this query returns the right results. There is a mistake on your example. Alternatively, if you do not want to have into account the aid for the actions, then the result would be:

+-----+-------+--------+--------+
| aid | login | search | budget |
+-----+-------+--------+--------+
| 121 |     0 |      0 |      1 |
| 121 |     0 |      2 |      1 |
| 123 |     1 |      0 |     -1 |
+-----+-------+--------+--------+
3 rows in set (0.00 sec)

To obtain this, remove the budget.aid = change_type.aid AND from my query.

Also please note that no query has been done on table2, so I assume values are hardcoded and do not change. You can expand the query to join the other table if that is not correct.


You can if you are using the table multiple times in different clauses and/or sub-queries like

SELECT SeniorManager = sm.displayName, Manager = m.displayName, Worker = w.displayName
FROM   people AS w
LEFT OUTER JOIN people AS  m ON  m.id = w.bossID
LEFT OUTER JOIN people AS sm ON sm.id = m.bossID

But you can not give the same use of a table more than one alias - the following for instance is invalid syntax:

SELECT a1.name, a2.name FROM people AS a1 AND a2

Also, you can not (in most DBMSs) use a table by both its real name and alias, so the following is invalid too:

SELECT people.name, a1.name FROM people AS a1

If ause of an object is given an alias you must refer to that instance by that alias even if the real name is unambiguous within the query.


Can I reference a specific column in a remote table (MySQL)?

SELECT a.foo, b.bar
    FROM db1.tableA AS a
    JOIN db2.tableB AS b  ON a.something = b.whatever;

This assumes both "databases" are on the same instance of MySQL on the same server.  Otherwise, you need to reword the question.


By @Akin's help I make this query

SELECT c.CompanyID,  c.`CompanyName`, c.`CompanyCode`, c.`URLPart` FROM `company` c
JOIN reports r ON r.CompanyID = c.CompanyID
WHERE r.ProductServiceID= 14 AND c.RegionID=1 AND c.IsActive = 1
GROUP BY c.CompanyID
ORDER BY MAX(r.`CreatedDate`) DESC

which solve the issue.

Thank you very much @Akin.


Financial Inst - Model Role+Relationships b/w Person and Account

Four tables will solve and normalize the data:

Person
- person_id
- name
- dob
- address

Account
- account_id
- creation_date

PersonAccountRole
- person_id (PK)
- account_id (PK)
- role_id
- etc...

Role
- role_id
- role_descrip
- etc...


UPDATE tbl
    SET col = REPLACE(col, 'É', 'é') COLLATE utf8_bin;

I don't know if the COLLATE clause is needed at all.
If col is not CHARACTER SET utf8, then use the appropriate "_bin" collation.
Test before applying:

SELECT col, REPLACE(col, 'É', 'é')
    FROM tbl
    WHERE col LIKE '%É%'
    LIMIT 22;


DB2 Online Restore but Without Roll Forward?

As per Information Center:

  "If this option is specified when restoring from an online backup
  image, error SQL2537N will be returned."

So you you are left with rolling forward. That being said, you could try the following

db2 rollforward db mydb to end of backup and complete

According to the information for rollforward command

  END OF BACKUP
      Specifies that all partitions in the partitioned database should be rolled forward to the minimum recovery time. See Examples section
  below for an example.

This should restore whatever it is you are attempting to restore up to the point of the end of the backup and not include any rolling forward through transactions logs that may be occurring after the backup. Normally you still have to issue at least this statement for a restore from an offline backup where you use archived logs.

I personally haven't tried nor had the need to do an online and only rollforward to the end of the backup. Give it a shot and let us all know what happens.

  EDIT: To answer your questions below: You must have archival logging
  enabled in order to take online backups. You can still take offline
  backups with archival logging. You do not have to switch back to
  circular to do so. However, once you have archival logging enabled,
  regardless of whether you are restoring from an offline or an online
  backup, you must perform a rollforward when you are done. No way
  around it. See my own previous question on this.

  In order to set up archival logging, you will need to alter the
  database configuration parameter LOGARCHMETH1. By default, this is
  set to OFF, which means circular logging. Your choices for it are as
  follows:

  DISK
  TSM
  USEREXIT
  VENDOR
  LOGRETAIN

  LOGRETAIN tells DB2 to leave the archived logs in the logging
  directory. It will not clean up any of these logs for you (leaving you
  as the DBA to do so). USEREXIT leaves it up to a non-IBM third party
  tool to take care of storing and archiving your logs (usually hand coded in C). Not too
  many people use this anymore from what I understand. DISK and TSM seem
  to be used a fair amount. DISK tells DB2 where you want to archive
  your logs off to (although with a few other parameters set, DB2 can
  automatically clean up logs no longer needed-I'll show you that
  below). TSM tells DB2 to let IBM's Tivoli Storage Manager to archive
  off the logs. There are a few other settings you have to get into for
  that to work. Where I work, we haven't tried that yet.
  And lastly, VENDOR is used to use a third-party vendor product to archive off your logs.

  To set up archival logging of to a different disk spot and have DB2
  automatically clean things for you, you can do the following
  (NOTE:these are in Unix path)

db2 connect to mydb user <admin user> using <admin user password>
#NOTE THE double and single quotes needed on the command line in Unix
db2 "update db cfg using logarchmeth1 'DISK:/dbarch/dblogs'"
db2 update db cfg using num_db_backups 2
db2 update db cfg using rec_his_retentn 2
db2 update db cfg using auto_del_rec_obj on
db2 terminate

  This will tell DB2 to keep 2 full backups for at least two full days.
  Two backups have been kept for two days and another one is taken, the
  oldest backup will be deleted by DB2. DB2 will also then clean up any
  logs that were between the now deleted backup and the next oldest
  retained backup.

  Also note: once you switch to archival logging, DB2 will require you
  to immediately take an offline backup as your next action (ie, the
  database will be placed into BACKUP PENDING mode) and you will be
  unable to do anything else until you do so.

  Hope this helps!


SQL Server 'Create Database' statement. How to inherit autogrowth settings?

For SQL Server 2005+ You can use the following:

SELECT FILE_ID, type, type_desc,
CAST( CAST( (CASE WHEN size < 128 THEN 128 ELSE size END) * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' AS SIZE,
CASE max_size WHEN -1 THEN 'UNLIMITED' ELSE CAST( CAST( max_size * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' END  AS MAXSIZE,
CASE is_percent_growth
    WHEN 0 THEN CAST( CAST( growth * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB'
    WHEN 1 THEN CAST( growth AS VARCHAR(30) ) + '%'
END AS FILEGROWTH
FROM sys.master_files
WHERE database_id = DB_ID('model')
GO

This will retrieve File definitions for Model Database.
Later on You can use it either in CREATE DATABASE or ALTER DATABASE.

SSMS actually uses SMO to retrieve these parameters and then creates a Script which looks like:

CREATE DATABASE [aaa] ON  PRIMARY
( NAME = N'aaa', FILENAME = N'D:\Data\aaa.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'aaa_log', FILENAME = N'D:\Logs\aaa_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

If Your Application is creating the Database and You can't modify the application -
then You have to use ALTER DATABASE after creation is done:

ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa', MAXSIZE = 102400KB )
GO
ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa_log', FILEGROWTH = 10240KB )
GO

This requires at least ALTER DATABASE Permission
(implied by ALTER ANY DATABASE Server Permission)
Obviously, You can generate that script using the first code sample (FROM sys.master_files).

I wouldn't use sysaltfiles for SQL Server 2008R2 because it is Deprecated.

Good luck,
Roi


SQL Injection penn testing from the queries only

Parameters are passed as an RPC (remote procedure call) to SQL Server over the TDS protocol. Consequently, a parameterized query will show as RPC starting/completed events in a trace (SQL Trace or Extended Events). Depending on the API and application methods used, parameterized queries might call API system procedures (e.g. sp_prepare) instead of sp_executesql or may call user stored procedures directly.

Ad-hoc queries are passed as a batch one or more SQL statements. These will show as a batch starting/completed events in a trace. Ad-hoc queries can be a SQL injection risk when:

the query is not static or
it built in the application code with string concatenation from untrusted sources.

Static queries might contain literals too so one would need to guess at the context and examine the app code to determine if the query is a vulnerability.

Be aware that even parameterized queries can pose an injection risk if built dynamically with a mix of parameters and literals, or by concatenation from untrusted sources. I can't think of a way to identify such practices without examining the app code or finding the vulnerability in penn testing.


Select where next row's value is greater

select
  insp_id, road_id, insp_date as cond_error_date, condition as cond_error
from
(
  select
    insp_id, road_id, insp_date, condition,
    lag(condition) over (partition by road_id order by insp_date) as condition_before
  from
    road_insp
)
where condition > condition_before;

   INSP_ID    ROAD_ID COND_ERRO COND_ERROR
---------- ---------- --------- ----------
         8          4 01-JAN-06         12
        16          5 01-JAN-18         20


Access articles from a table if user belongs to qualified group

So, as I understand, the input to the query is:

a group name;
a user name.

And the output should be the list of all the articles belonging to the given group provided either of the following is true:

the group is open;
the given user belongs to the group.

If that is correct, I believe the missing condition could be implemented as an EXISTS predicate:

...
    OR EXISTS
    (
      SELECT
        *
      FROM
        myuser AS u
        INNER JOIN membership AS m ON u.id = m.myuser_id
      WHERE
        m.mygroup_id = g.id
        AND u.name = 'BAZ-ID'
    )
...

The join to membership in the main SELECT would then be redundant.

Alternatively, you could outer-join the result of membership INNER JOIN myuser and check for presence of a match in the WHERE clause, like this:

SELECT
  *
FROM
  article AS a
  INNER JOIN mygroup AS g ON g.id = a.mygroup_id
  LEFT JOIN
    membership AS m
    INNER JOIN myuser AS u ON m.myuser_id = u.id
  ON m.mygroup_id = g.id AND u.name = 'BAZ-ID'
WHERE
  (g.name = 'FOO-BAR')
  AND
  (
    (g.open IS TRUE)
    OR
    (u.id IS NOT NULL)
  )
;

An EXISTS predicate is often faster than an equivalent join, though, so you might prefer the former option, but test in your environment to see which is actually better.

If the user is specified by ID instead of a name, the query becomes simpler in each case. The EXISTS predicate would look like this:

EXISTS
(
  SELECT
    *
  FROM
    membership AS m
  WHERE
    m.mygroup_id = g.id
    AND m.myuser_id = <input-user-ID>
)

For the second option, using an ID instead of a name to specify the user would mean a simpler left join:

LEFT JOIN membership AS m
  ON  m.mygroup_id = g.id
  AND m.myuser_id = <input-user-ID>

And, of course, you would need to replace the u.id reference in the WHERE clause with a reference to a non-nullable membership column. The m.id would do, so:

WHERE
  (g.name = 'FOO-BAR')
  AND
  (
    (g.open IS TRUE)
    OR
    (m.id IS NOT NULL)
  )


DB design for my project (addressing people)

Vague requirements

Your requirements are too vague. Add more detail. Talk about how each entity relates to another in very precise terms.

Address

The address should be assigned to the office. If the office can have only one address, then address should likely be part of the office table.

The hierarchy of country-state-city makes no sense to me. Explain exactly how your requirements demand that structure. Reporting on such values is done by queries, with sorting and grouping, not building out separate tables. Generally, a related table with only a single semantic column should not be a separate table.

You talked about assigning people to offices but your design makes no such association.

Data-Validation

Some of your concerns seem to be related to validating the data-entry of the address. Usually the app is the front-line of defense for that kind of data-validation, with checks on the database side playing a secondary role.

Either way (app or db), your attempts shown here are meager, and insufficient to ensure valid data. If you are going to do data-validation on the address, do it seriously: Rather than hack your own checks, leverage a postal address verification system driven by postal-code. Either use a remote service or provide your own locally with complete dataset of known addresses and an existing library for look-up and comparing of address info. Your business-problem data should not be directly linked to such tables, but rather your app or database trigger should call out to the remote or local address-verification service.

Naming issues

Change People to singular Person.

Fix spelling of Adress to Address.

Use all lowercase in the names for maximum portability and avoid problems.

Fix Name appearing twice on the address table.


How to count the rows from 1 table but from two different column

You can use a cross apply and values to create a row for each value and an extra column in the values clause to keep track of what value is a major and what value is a minor.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE ApplicantCourseDetail(
    [Major] [nvarchar](50) NULL,
    [MinorI] [nvarchar](50) NULL
)
INSERT INTO ApplicantCourseDetail
           ([Major]
           ,[MinorI])
     VALUES
           ('Chemistry','History'),
('English','Chemistry'),
('Botany','Zoology'),
('History','English'),
('Chemistry','Chemistry'),
('English','Philosophy'),
('History','English')

Query 1:

select T.Value,
       count(case when T.IsMajor = 1 then A.Major end) as MajorCount,
       count(case when T.IsMajor = 0 then A.MinorI end) as MinorCount
from dbo.ApplicantCourseDetail as A
  cross apply (values (A.Major, 1),(A.MinorI, 0)) as T(Value, IsMajor)
group by T.Value;

Results:

|      VALUE | MAJORCOUNT | MINORCOUNT |
|------------|------------|------------|
|     Botany |          1 |          0 |
|  Chemistry |          2 |          2 |
|    English |          2 |          2 |
|    History |          2 |          1 |
| Philosophy |          0 |          1 |
|    Zoology |          0 |          1 |


could not open log file "/var/log/postgresql-2015-05-24_181212.log": Permission denied

I know that only root can write to /var/log ...

You can change that. As root user (or with sudo) in the shell:

mkdir /var/log/postgresql
chmod 0700 /var/log/postgresql
chown postgres:postgres /var/log/postgresql

And in postgresql.conf:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = '1d'          # That's what makes it "dynamic"

Depesz has a detailed article on the topic:
https://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/


There is an example about this feature in the PostgreSQL "Whats new in 9.0". If the optimizier detects, that a join can not add any additional rows, it will be dropped. The PostgreSQL Feature does not remove joins from views if they are not needed because the requested columns are not selected.

Also, the feature is limited on LEFT JOINs right now ( version 9.4 ). This may change in future versions.


Are there reasons why I should not set my db owner to [sa]?

Making SA the owner of a database actually simplifies and/or solves a number of things, but can have some security implications.

In particular, remember that if SA is the owner of a database, then dbo = 'SA'.  This means that, among other things, any procedures in the [dbo] schema (which is the default) that have "EXECUTE As Owner" in them, are actually executing as SA.  That's not quite as bad as it sounds, because unless you've marked the database as TRUSTWORTHY, SQL Server will not let a session or task out of the database with an impersonated server-level principal like that.

Which brings up the next point: never mark such databases as TRUSTWORTHY, unless you're really, really sure that it is secure.  Because anyone with the ability to create procedures in the [dbo] schema can execute as SA, on the entire server, if they want to.

Another issue can come up because many products and applications that have their own SQL Server database, often specify that their application login has to be the DBO of the database.  Obviously you could resolve that by making their application login be 'SA'.  Hopefully, it's also obvious that you should never do that, unless that SQL Server Instance is not used for anything else (even then, I would recommend against it).


Count Over returns Row_Number based on Order By

If I run this query I will get the ROW_NUMBER() instead of the COUNT()

OVER Clause (Transact-SQL)

  If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification.


Scenario of huge SQL Server Database backup

What you're looking to do is called a "piecemeal restore" and is pretty complicated. Here's a small pull quote from the MSDN article:

  Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups.

I'm assuming that your database is in simple recovery since you didn't mention any log backup schedule. So, if you ever need to restore your archive filegroup, you'll end up restoring the entire database.

If I were you, I'd try tuning your backup before going this route. For instance, try taking the mirroring out of the backup command note the time difference. You can always copy it to the secondary location after the full backup completes.

But, if you're looking to proceed with the piecemeal restore solution, the actual syntax isn't that bad:

restore database [yourDB]
filegroup = 'PRIMARY'
from disk = '\\path\\to\\full_backup.bak'
with partial, norecovery;

restore database [yourDB]
filegroup = 'PRIMARY'
from disk = '\\path\\to\\diff_backup.bak'
with partial, recovery;

restore database [yourDB]
filegroup = 'ArchiveFG'
from disk = '\\path\\to\\archive.bak'
with recovery;


What is the equivalent of the SQL Server CHAR function?

Have a look at the docs on PostgreSQL String Functions and Operators,

  chr(int) Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate an ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes.

Demonstrating chr(), its inverse, and both.,

# SELECT chr(65), ascii('A'), chr(ascii('A'));
 chr | ascii | chr
-----+-------+-----
 A   |    65 | A
(1 row)

In your question you use E'' (the "bytea Hex Format") which is totally possible too, though I wouldn't suggest it for simple problems like this. You can do that like this,

SELECT E'\x41'; -- 41 is hex for 65 (in base 10)
SELECT E'\101'; -- 101 is octal for 65 (in base 10)
 ?column?
----------
 A
(1 row)


I believe id is autoincrement field, and you want this value to start with 1 in new table.  You can specify column list in your first query, so it won't fail :

INSERT INTO test2 (`timestamp`, datenum)
SELECT `timestamp`, datenum
FROM test1 WHERE datenum>=23 and datenum<=1000000


SQL 2019 install....what is AzureAttestService?

This service has to do with the Always Encrypted with Enclaves feature.  I have sent a note to the team to get this more explicitly documented for customers.  Some scenarios will break if that service is not enabled.

Conor Cunningham


Use OR with EXISTS or IN:
(the IN version only if the memberships.record_id column is not nullable)

SELECT r.*
FROM records AS r
WHERE r.user_id = ?
   OR EXISTS
      ( SELECT 1
        FROM memberships AS m
        WHERE m.record_id = r.id
          AND m.user_id = ?
      ) ;

SELECT r.*
FROM records AS r
WHERE r.user_id = ?
   OR r.id IN
      ( SELECT m.record_id
        FROM memberships AS m
        WHERE m.user_id = ?
      ) ;

This way you also do not get the duplicate rows of records that the join introduces (and then are get rid in the UNION DISTINCT part of your query.)

You could use the LEFT join query but you would have to either add DISTINCT or GROUP BY the primary key of the records table to avoid the duplicate rows:

SELECT r.*
FROM records  AS r
  LEFT OUTER JOIN memberships AS m
    ON m.record_id = r.id
WHERE r.user_id = ?
   OR m.user_id = ?
GROUP BY r.id ;


Capture Extended Events Histogram only during time window

So I've solved it.

I created a table with xml column and I'll query the sys.dm_xe_session_target directly with a scheduled job right before stoping the session and save the xml result to my table (which will be persisted).


Getting the number of Saturdays and Sundays in a month

I'll suggest the calendar table also discussed in that thread. It will help for a lot of calculations beside this one. Your query can then be expressed as:

select count( case when DAYOFWEEK(calendar_date) in (1,7) then 1 end)
from Calendar
where calendar_date between :start_date and :stop_date

or as:

select count(1)
from Calendar
where calendar_date between :start_date and :stop_date
  and DAYOFWEEK(calendar_date) in (1,7)


Adding wiredtiger nodes to a non-wiredtiger replica set

To migrate data between different storage engines in MongoDB 3.0 you will need to perform an initial sync to the new replica set node or use mongorestore to seed a new replica set with your data.

If you want to add a node with a new storage engine to a live production replica set, rs.add() is the correct approach. To ensure a smooth upgrade I would also upgrade all existing nodes to the latest version of MongoDB 3.0.x with MMAP storage (the default) before adding your new WiredTiger node(s).

The key information in your question (and comments) is:

socket exceptions related to networking timeouts:

I NETWORK  [rsSync] Socket recv() timeout  x.x.x.x:x
I NETWORK  [rsSync] SocketException: remote: x.x.x.x:x error: 9001 socket exception [RECV_TIMEOUT] server [x.x.x.x:x]

deploying on Azure, which has some known issues with timeouts

As per the MongoDB production notes on Azure, you should reduce the TCP keepalive setting to avoid network timeouts:

  The TCP keepalive on the Azure load balancer is 240 seconds by default, which can cause it to silently drop connections if the TCP keepalive on your Azure systems is greater than this value. You should set tcp_keepalive_time to 120 to ameliorate this problem.

As further preparation for successful initial sync with WiredTiger you should review the MongoDB production notes for your operating system.

Since you mention using Linux, I would recommend checking:

ulimits are appropriately set. WiredTiger creates more files than MMAP (separate data & index files per collection rather than per database) so will use more file handles than MMAP nodes in the same replica set. If you don't increase the ulimits from the default you may find the initial data transfer completes but initial sync fails at the index build stage with a fatal error like Too many open files.
the WiredTiger data directory is on a volume using the XFS file system. As at the time of writing, there are known performance issues (such as throughput stalls) with EXT4.

The production notes in the MongoDB manual are updated regularly based on issues and experiences reported by MongoDB users, so should be part of your preflight checklist for production O/S deployments or upgrades.


Restricting Database Access/Functions to User Groups?

Server roles are made for this.

Read this for the details http://msdn.microsoft.com/en-us/library/ms188659.aspx

The dbcreator role looks like what you are looking for.


T-SQL: If parameter1 is null, use parameter2

The simplest answer to your question involves telling SQL Server what to look for by placing brackets around grouped requirements.  So, for instance, if we only want to check Field2 when @parameter1 IS NULL, we could do this:

WHERE (@parameter1 IS NULL AND Field2 = @parameter2)
    OR (Field1 = @parameter1)

SQL Server will evaluate Field2 = @parameter2 when @parameter1 IS NULL.  When '@Parameter1` is not null SQL Server will only return rows that match it.

If you have a lot of rows in the source table, you may want to use a stored procedure to help optimize the possible choices.  The following example creates a table, populates a couple of rows, and creates a procedure to query the table.

I'm doing this in tempdb since this is just an example:

USE tempdb;

Create the table, and populate it with a couple of rows:

IF OBJECT_ID('dbo.SomeTable') IS NOT NULL
DROP TABLE dbo.SomeTable;
GO
CREATE TABLE dbo.SomeTable
(
    SomeID INT NOT NULL
        CONSTRAINT PK_SomeTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Column1 NVARCHAR(30) NULL
    , Column2 NVARCHAR(30) NULL
    , Column3 NVARCHAR(200) NOT NULL
);
INSERT INTO dbo.SomeTable (Column1, Column2, Column3)
VALUES (NULL, 'col2_value', 'some data for row 1')
    , ('col1_value', NULL, 'some data for row 2');

Since I know the precise patterns my queries against this table will take, I can create a couple of simple covering indexes that will allow SQL Server to very quickly find the relevant rows:

CREATE INDEX IX_SomeTable_c1 ON dbo.SomeTable(Column1) INCLUDE (Column2, Column3);
CREATE INDEX IX_SomeTable_c2 ON dbo.SomeTable(Column2) INCLUDE (Column1, Column3);
GO

This will add an extra 10,000 rows to the table just to give us enough data for the indexes to be considered by the query optimizer:

INSERT INTO dbo.SomeTable (Column1, Column2, Column3)
SELECT TOP(10000) LEFT(o1.name, 30), RIGHT(o2.name, 30), o3.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3

Here, I'm creating a procedure that will search our table using the contents of either Column1 or Column2 depending on the parameters passed in.

IF OBJECT_ID('dbo.MySearch') IS NOT NULL
DROP PROCEDURE dbo.MySearch;
GO
CREATE PROCEDURE dbo.MySearch
(
    @parameter1 NVARCHAR(30) = NULL
    , @parameter2 NVARCHAR(30) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @where NVARCHAR(100);
    DECLARE @params NVARCHAR(100);
    SET @sql = 'SELECT t.Column1
    , t.Column2
    , t.Column3
FROM dbo.SomeTable t
';
    IF @parameter1 IS NULL
    BEGIN
        SET @params = '@p2 NVARCHAR(30)';
        SET @where = 'WHERE t.Column2 = @p2';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p2 = @parameter2;
    END
    IF @parameter2 IS NULL
    BEGIN
        SET @params = '@p1 NVARCHAR(30)';
        SET @where = 'WHERE t.Column1 = @p1';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p1 = @parameter1;
    END
END;
GO

If you pass in values for both @parameter1 and @parameter2, the stored procedure will return no rows.  You may want to add a third IF statement to the stored procedure to handle that situation, something like:

    IF @parameter1 IS NOT NULL AND @parameter2 IS NOT NULL
    BEGIN
        SET @params = '@p1 NVARCHAR(30), @p2 NVARCHAR(30)';
        SET @where = 'WHERE t.Column1 = @p1 AND t.Column2 = @p2';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p1 = @parameter1, @p2 = @parameter2;
    END

Without knowing your exact requirements, it's difficult to say if you need that.

Two samples that show the usage of the stored proc, along with the execution plans:

EXEC dbo.MySearch @parameter1 = 'col1_value';

EXEC dbo.MySearch @parameter2 = 'col2_value';

Using a stored procedure in combination with dynamic SQL allows the query designer (us) to help SQL Server consistently choose the best plan for the job by providing two independent cachable plans that are optimized for each scenario.  Depending on the cardinality of each column involved, the complexity of the actual query design, along with the presence of desirable indexes, SQL Server may decide to create vastly different plans for the two queries, which can be a Very Good Thing™.

Just for fun, using the "simple" WHERE clause I show at the top of my answer like this:

DECLARE @parameter1 NVARCHAR(30) = 'col1_value';
DECLARE @parameter2 NVARCHAR(30);
SELECT t.Column1
    , t.Column2
    , t.Column3
FROM dbo.SomeTable t
WHERE (@parameter1 IS NULL AND t.Column2 = @parameter2)
    OR (t.Column1 = @parameter1);

Results in a plan like this:

This clearly shows SQL Server searching both indexes for Column1 and Column2.  This is rather obviously not optimal, and would certainly not be the preferred route for larger sets of data.


Could the creation of an index break table dependencies?

When implementing filtered indexes, certain connection settings must be correct:

Otherwise

  The Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.

So if any of the dependent code does not have the above SET options correctly configured, adding a filtered index will break them. I unfortunately have had to learn the hard way to double check this when working on old systems with questionable stored procedure code :)


Stored procedure - problem with merge - Insert

How about simplifying greatly:

UPDATE d SET
  [CreatedDate] = [Source].[t_CreatedDate],
  [DbDate]= [Source].[t_DbDate],
  [ModifiedDate] = [Source].[t_ModifiedDate],
  [SubGUID] = [Source].[t_SubGUID],
  [eType] = [Source].[t_eType]
FROM dbo.NO_table AS d
INNER JOIN @track AS [Source]
ON d.JobID = [Source].t_JobID
WHERE d.[ModifiedDate] < [Source].[t_ModifiedDate];

INSERT dbo.NO_Table(...cols...)
  SELECT ...cols...
  FROM @track AS t
  WHERE NOT EXISTS (SELECT 1 FROM dbo.NO_Table
    WHERE JobID = t.t_JobID
    AND [ModifiedDate] >= [Source].[t_ModifiedDate]);

Now you have two set-based operations instead of over a million singletons, and you avoid doing silly things like counting rows when you only care if it the result is zero or non-zero. And you avoid the overkill of MERGE for this simple scenario (and a bunch of other things that come with it).


How Can I properly connect a replica set to split write and read with ApsaraDB for MongoDB

To correctly connect to a replica set, you must first understand the connection string URI of MongoDB. All official drivers support connection to MongoDB using a connection string.

A connection string includes the following content:

mongodb://[username:password@]host1[:port1][,host2[:port2],...[,hostN[:portN]]][/[database][?options]]

mongodb:// is a prefix indicating a connection string.

username:password@ indicates the user name and password that must be specified if authentication is enabled.

hostX:portX indicates the IP address and port number of each member in the replica set. Multiple members are separated by commas (,).

/database indicates the database to which the user account belongs during authentication.

?options indicates additional connection options.

To implement read/write splitting, add readPreference=secondaryPreferred to options so that read requests are prioritized to secondary nodes. For more information about read options.

Connections limit: Add maxPoolSize=xx to options to limit the client connection pool to a specified number (xx) of connections.

To confirm writes with the client only after data is successfully written to a majority of nodes, add w= majority to options. For more information about write options.

Connection to ApsaraDB for MongoDB is used as an example here. When you buy an ApsaraDB for MongoDB replica set, you can obtain the name of the replica set and the addresses of replica set members.

For more detailed Answer Please Click Here


Join multiple tables for aggregate or create a single table with combined attribes

If you need to have multiple values (like one ART having several AUTHORs), you need this kind of linking. If that is not needed, just have all the ids in ART table.

I'd recommend you move to ART all attribute ids where a single value is enough and leave only the multivalued ones using the more complex kind of linking via external link table.


how to use is_srvrolemember to list all server roles of a user

The code you saw in profiler has nothing to do with the sp cited.

You can find explicit server roles of mycompany\myuser using following code:

select sp.name
from sys.server_role_members rm join sys.server_principals sp
        on rm.role_principal_id = sp.principal_id
where suser_name(member_principal_id) ='mycompany\myuser';

What I mean by explicit roles: these are roles to which the principal mycompany\myuser was added explicitely: exec sys.sp_addsrvrolemember 'mycompany\myuser', 'some_server_role'.

But your principal is Windows principal, this means he can inherit server role membership from different Windows groups of which he is member.

To find out all the roles he is member you should impersonate the principal and explore his login tokens:

execute as login = 'mycompany\myuser';

select *
from sys.login_token
where type = 'SERVER ROLE';

This will give you all the server roles mycompany\myuser is member of, those inherited from Windows groups included.


Storing all data in a single db versus multiple dbs in Mongodb

Well, 2.2 will add database level concurrency (this is available for testing in the 2.1 development branch as I write this answer), so if you are concerned about lock contention between the two, then separate databases would be a good idea.

You don't really mention what your criteria are here to make the decision, so the new 2.2 feature is the only relevant one I can think of - feel free to comment or expand and I'd be happy to answer further.


Oracle returns different results when executing a sql with bind variable or with a fix value

The results would not be guarenteed to be the same if the execution plans are different. Without an explicit sub-query ordering the rownum will be based on the order in which Oracle selected the data from the tables / joined tables. If the plans differ on how the tables are joined and in which order, the first 12 values read will be different.

To make this more deterministic, use some kind of explicit ordering on a subquery and then rownum that on the outer query. It would be normal to want the first X records based on some explicit ordering, instead of 12 random ones.


4 CPUs of 20 (NUMA node 0) Pegged at 100%

So you've got 5 'stacked' instances on a single Windows server. You haven't said exactly how many sockets/CPUs are available and how much memory though. I like setting affinity for each instance in such cases, even if I decide to have CPUs overlapping in the struggle to balance the overall CPU load (depends on each instance's load).

Any instance with more than 4 CPUs could use an explicit DOP setting in my experience - rarely over '4' in stacked cases like yours. Don't forget to set 'Cost threshold of Parallelism' for each instance to something reasonable (50?) to avoid excessive parallelism  - in your case this is even more important.

Remember that the memory left "for the OS" should be more now, since you have to account for the footprint of each instance (on top of SSIS etc). Check in SQL Config Mgr if SSAS is also running and adjust its 'max memory' accordingly, by default it goes for 80% of the whole server memory (!)

Also maybe worth taking away and 'Lock pages in memory' rights of the SQL Service account(s) so that the OS can breath and do its job better (if it pages,
everyone suffers!). Also good practice is to set some reasonable 'min memory' for each instance.

I think running sp_blitz and sp_blitz_first on each instance would give you some quick pointers on more pressing issues.

You may also want to monitor some windows permon counters like 'available memory' and 'working set' for each of the processes running there in case you find particular times of the day/night when the server is suffering.


Migrating SQL Server 2000 to 2012, getting old versions of Views (edit: offender SQL Server 2008 R2)

So I finally found the error, and I'll just have to cross my fingers that this was a lone occurance.

The view that kept reverting, after a sp_refreshview in SQL2008 r2, was named "qryCreateSnapshot". Then there was another view, a backup of sorts, named "qryCreateSnapshot_Old". The problem was that in the old view, the body was still "CREATE VIEW dbo.qryCreateSnapshot". I have no idea why this even compiled, but there you have it.

So when I refreshed in 2008, I guess the new view got overwritten with the old. This did not happen in 2000.

Now to compare hundreds of view names with their respective body name... ;-)

For anyone in a smiliar situation, here is a small query to find mismatches between header name and body name of a view:

SELECT
  O.name AS HeaderViewName,
  RTRIM(SUBSTRING(C.text, 17, LEN(O.name))) AS BodyViewName,
  CASE
     WHEN RTRIM(SUBSTRING(C.text, 17, LEN(O.name))) = O.name THEN 'Yes'
     ELSE 'Nope, fix body'
  END AS Match,
  C.text AS SourceText
FROM sysobjects O
INNER JOIN syscomments C
   ON O.id = C.id
WHERE O.type = 'V'
AND   LEFT(C.text, 12) = 'CREATE VIEW ';

I know it's not perfect, but it worked for my data and I found another ~15 mismatches.


Change query to improve operator estimates

The plan was compiled on a SQL Server 2008 R2 RTM instance (build 10.50.1600). You should install Service Pack 3 (build 10.50.6000), followed by the latest patches to bring it up to the (current) lastest build 10.50.6542. This is important for a number of reasons, including security, bug fixes, and new features.

The Parameter Embedding Optimization

Relevant to the present question, SQL Server 2008 R2 RTM did not support the Parameter Embedding Optimization (PEO) for OPTION (RECOMPILE). Right now, you are paying the cost of recompiles without realizing one of the main benefits.

When PEO is available, SQL Server can use the literal values stored in local variables and parameters directly in the query plan. This can lead to dramatic simplifications and performance increases. There is more information about that in my article, Parameter Sniffing, Embedding, and the RECOMPILE Options.

Hash, Sort and Exchange Spills

These are only displayed in execution plans when the query was compiled on SQL Server 2012 or later. In earlier versions, we had to monitor for spills while the query was executing using Profiler or Extended Events. Spills always result in physical I/O to (and from) the persistent storage backing tempdb, which can have important performance consequences, especially if the spill is large, or the I/O path is under pressure.

In your execution plan, there are two Hash Match (Aggregate) operators. The memory reserved for the hash table is based on the estimate for output rows (in other words, it is proportional to the number of groups found at runtime). The memory granted is fixed just before execution begins, and cannot grow during execution, regardless of how much free memory the instance has. In the supplied plan, both Hash Match (Aggregate) operators produce more rows than the optimizer expected, and so may be experiencing a spill to tempdb at runtime.

There is also a Hash Match (Inner Join) operator in the plan. The memory reserved for the hash table is based on the estimate for probe side input rows. The probe input estimates 847,399 rows, but 1,223,636 are encountered at run time. This excess may also be causing a hash spill.

Redundant Aggregate

The Hash Match (Aggregate) at node 8 performs a grouping operation on (Assortment_Id, CustomAttrID), but the input rows are equal to the output rows:

This suggests the column combination is a key (so the grouping is semantically unnecessary). The cost of performing the redundant aggregate is increased by the need to pass the 1.4 million rows twice across hash partitioning exchanges (the Parallelism operators on either side).

Given that the columns involved come from different tables, it is more difficult than usual to communicate this uniqueness information to the optimizer, so it can avoid the redundant grouping operation and unnecessary exchanges.

Inefficient thread distribution

As noted in Joe Obbish's answer, the exchange at node 14 uses hash partitioning to distribute rows among threads. Unfortunately, the small number of rows and available schedulers means all three rows end up on a single thread. The apparently-parallel plan runs serially (with parallel overhead) as far as the exchange at node 9.

You can address this (to obtain round-robin or broadcast partitioning) by eliminating the Distinct Sort at node 13. The easiest way to do that is to create a clustered primary key on the #temp table, and perform the distinct operation when loading the table:

CREATE TABLE #Temp
(
    id integer NOT NULL PRIMARY KEY CLUSTERED
);

INSERT #Temp
(
    id
)
SELECT DISTINCT
    CAV.id
FROM @customAttrValIds AS CAV
WHERE
    CAV.id IS NOT NULL;

Temporary table statistics caching

Despite the use of OPTION (RECOMPILE), SQL Server can still cache the temporary table object and its associated statistics between procedure calls. This is generally a welcome performance optimization, but if the temporary table is populated with a similar amount of data on adjacent procedure calls, the recompiled plan may be based on incorrect statistics (cached from a previous execution). This is detailed in my articles, Temporary Tables in Stored Procedures and Temporary Table Caching Explained.

To avoid this, use OPTION (RECOMPILE) together with an explicit UPDATE STATISTICS #TempTable after the temporary table is populated, and before it is referenced in a query.

Query rewrite

This part assumes the changes to the creation of the #Temp table have been made already.

Given the costs of possible hash spills and the redundant aggregate (and surrounding exchanges), it may pay to materialize the set at node 10:

CREATE TABLE #Temp2
(
    CustomAttrID integer NOT NULL,
    Assortment_Id integer NOT NULL,
);

INSERT #Temp2
(
    Assortment_Id,
    CustomAttrID
)
SELECT
    ACAV.Assortment_Id,
    CAV.CustomAttrID
FROM #temp AS T
JOIN dbo.CustomAttributeValues AS CAV
    ON CAV.Id = T.id
JOIN dbo.AssortmentCustomAttributeValues AS ACAV
    ON T.id = ACAV.CustomAttributeValue_Id;

ALTER TABLE #Temp2
ADD CONSTRAINT PK_#Temp2_Assortment_Id_CustomAttrID
PRIMARY KEY CLUSTERED (Assortment_Id, CustomAttrID);

The PRIMARY KEY is added in a separate step to ensure the index build has accurate cardinality information, and to avoid the temporary table statistics caching issue.

This materialization is quite likely to occur in memory (avoiding tempdb I/O) if the instance has enough memory available. This is even more likely once you upgrade to SQL Server 2012 (SP1 CU10/SP2 CU1 or later), which has improved Eager Write behaviour.

This action gives the optimizer accurate cardinality information on the intermediate set, allows it to create statistics, and allows us to declare (Assortment_Id, CustomAttrID) as a key.

The plan for the population of #Temp2 should look like this (note the clustered index scan of #Temp, no Distinct Sort, and the exchange now uses round-robin row partitioning):

With that set available, the final query becomes:

SELECT
    A.Id,
    A.AssortmentId
FROM
(
    SELECT
        T.Assortment_Id
    FROM #Temp2 AS T
    GROUP BY
        T.Assortment_Id
    HAVING
        COUNT_BIG(DISTINCT T.CustomAttrID) = @dist_ca_id
) AS DT
JOIN dbo.Assortments AS A
    ON A.Id = DT.Assortment_Id
WHERE
    A.AssortmentType = @asType
OPTION (RECOMPILE);

We could manually rewrite the COUNT_BIG(DISTINCT... as a simple COUNT_BIG(*), but with the new key information, the optimizer does that for us:

The final plan may use a loop/hash/merge join depending on statistical information about the data that I do not have access to. One other small note: I assumed that an index like CREATE [UNIQUE?] NONCLUSTERED INDEX IX_ ON dbo.Assortments (AssortmentType, Id, AssortmentId); exists.

Anyway, the important thing about the final plans is that the estimates should be much better, and the complex sequence of grouping operations has been reduced to a single Stream Aggregate (which does not require memory and therefore cannot spill to disk).

It is hard to say that performance will actually be better in this case with the extra temporary table, but the estimates and plan choices will be much more resilient to changes in data volume and distribution over time. That may be more valuable over the long term than a small performance increase today. In any case, you now have much more information on which to base your final decision.


SQL Server CTE Bottom to Top Recursive with Where clause

Swapping the columns in the inner join in the recursive part is a way to go about this.

Join columns changed from t.[EmployeeId]=s.managerId to s.[EmployeeId]=t.managerId

I deleted some parts that did not seem like they were needed.

DECLARE @employeeId int = 3

;WITH StaffTree AS
(
    SELECT
        c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
        FROM @EmployeeTable c
        WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND     c.managerId IS NULL)
    UNION ALL
        SELECT
            s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
        FROM StaffTree t
        INNER JOIN @EmployeeTable s ON s.[EmployeeId]=t.managerId
)
SELECT EmployeeId,
       name,
       managerId
FROM StaffTree
ORDER BY managerId asc;

Result

EmployeeId  name    managerId
1           Jerome  NULL
2           Joe     1
3           Paul    2


How to grant Connect & Create Privilege on current Database in PostgreSQL?

You could use a DO block with dynamic SQL:

do
$$
begin
  execute format('grant create, connect on database %I to %I', current_database(), 'some_user');
end;
$$;


A PERSISTED COMPUTED column can't use Window functions like ROW_NUMBER(). It's one of the limitations because ROW_NUMBER() is not a deterministic function and only deterministic functions can be used with PERSISTED COMPUTED columns.
Paul White does a great write up on how to properly use PERSISTED COMPUTED columns in his article here.
Specifically he mentions how PERSISTED was added only for deterministic columns:

Persisted computed columns were added to the product specifically to
allow indexes to be built on deterministic but "imprecise" (floating
point) columns

If you could add a few more details on what your end goal is, I could try to suggest an alternative solution to solve the problem (e.g. what is the purpose of your UDF)?


Tables in db1 that don't exist in db2:

SELECT name FROM db1.sys.tables
EXCEPT
SELECT name FROM db2.sys.tables
ORDER BY name;

For more elaborate comparisons (like columns, indexes, and data types), don't re-invent the wheel, as there are already tools that solve this problem.* For more info on this in general, see this blog post from ages ago.

Disclaimer: I work for SentryOne.


SQL Server Reporting services migration (2008 to 2012)

Best is to use  Reporting Services Scripter

  Reporting Services Scripter is a .NET Windows Forms application that enables the scripting and transfer of groups of SSRS catalog items from one server to another or from one SSRS folder to another on the same server. Reporting Services Scripter is flexible and lets you transfer catalog item properties, such as serverside report parameters, Subscriptions (both normal and data-driven), Descriptions, History options, and Execution options (including both report-specific schedule and shared schedule execution options) to other SQL Server instances.

Kevin Kline highlights the functionality here and there is a good article by Dale Kelly for migrating reports using RSScripter at MSSQLTips.

Another way is to use the power of PowerShell. There are many script available like here and here.

There is other utility (which I have not used) called reportsync


What's the best way to order a list of tables based on their foreign key constraints

One problem is that you could have circular references. You may prefer to disable the constraints, do your ETL, and reenable them.

This could also be a matter of timing, such that it may be impossible to populate some data because of when you got the data from the source. For example, you get data from table P and then from table C. But by the time you’re getting the data from table C, you have a row that refers to a row in C that refers to a row in P that wasn’t there a few milliseconds earlier. Or if you do it in the other order, the row in P is deleted before you grab it.

So by all means use a clever script. But you might find you still have problems.

My recommended solution is to question whether you actually want FKs here, or whether you should do some more complex lookups with inferred members instead.


Oracle SQL- special characters delimited strings

select Col1, sum(Col3)
from
    (
    select
        regexp_substr(column_value, '\w+', 1, 1) as Col1,
        regexp_substr(column_value, '\w+', 1, 2) as Col2,
        regexp_substr(column_value, '\w+', 1, 3) as Col3
    from split('APPLE- GREEN- 40%; BANANA- YELLOW- 15%; APPLE- RED - 25%; GRAPES- GREEN - 20%',';')
    )
group by Col1

create or replace type split_tbl as table of varchar2(32767);
/

create or replace function split
(
    p_list varchar2,
    p_del varchar2 := ','
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    l_list := replace( l_list, ' ',  '' );
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row( substr( l_list, 1, l_idx-1) );
            l_list := substr( l_list, l_idx + length(p_del) );

        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;


PostgreSQL Group by to create a new group whenever column value changes

This is more complicated, you need to find consecutive rows with the same reserved value first and assign a common group number to it. There are several ways to achieve this, the following is the easest one to understand by submitting the Derived Tables individually:

SELECT min(dt) AS from_date, max(dt) AS to_date, reserved
FROM
 (
   select dt, reserved,
      -- assign a number to al rows with the same value
      sum(flag) over (order by dt rows unbounded preceding) as grp
   from
     ( SELECT dt, reserved,
          -- assigns 1 whenever reserved changes
          case when lag(reserved) over (order by dt) = reserved
               then 0 -- value of the previous same as current
               else 1 -- different value
          end as flag
       from reservations
     ) as dt
  ) as dt
GROUP BY reserved, grp