Firebird total column I have a query that I am struggling with. The problem I have is that I would like it to just display a total of column variancevalue as a result.

SELECT
  DEPARTMENT.DESCRIPTION AS DepartmentName,
  SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
  SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
  sum(STOCKTAKELIST.QTYONHAND) Onhand,
  sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
  sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
  LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBDEPARTMENT ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBSUBDEPARTMENT ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
  STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete'
GROUP BY
  1,2,3
ORDER BY DEPARTMENT.DESCRIPTION, SUBDEPARTMENT.DESCRIPTION, SUBSUBDEPARTMENT.DESCRIPTION

Can this be done and if so how would I do that?

If you want to replace the entire output of your current query with just one single-column row holding the total across the VarianceValue column of the original output, then just take the current query and remove

the GROUP BY clause,
the ORDER BY clause,
the left joins,
and all SELECT terms but one (the last one):

SELECT
  DEPARTMENT.DESCRIPTION AS DepartmentName,
  SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
  SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
  sum(STOCKTAKELIST.QTYONHAND) Onhand,
  sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
  sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
  LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBDEPARTMENT ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBSUBDEPARTMENT ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
  STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete'
GROUP BY
  1,2,3
ORDER BY DEPARTMENT.DESCRIPTION, SUBDEPARTMENT.DESCRIPTION, SUBSUBDEPARTMENT.DESCRIPTION

That is, without the parts struck through the query should look like this (just to make it absolutely unambiguous and copy-paste-friendly):

SELECT
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
WHERE
  STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete';

It is quite possible that the inner joins are unnecessary too. They probably need to stay if they can affect the result. And they can affect the result if either of these is true:

STOCKTAKELIST.INTRECNO_STOCK is nullable and/or may contain values not found in STOCK.INTRECNO;
STOCK.INTRECNO_LOCATION is nullable and/or may contain values not found in LOCATION.INTRECNO.

If you have foreign key relationships in place, then what matters is just the nullability of STOCKTAKELIST.INTRECNO_STOCK and STOCK.INTRECNO_LOCATION. Now if both columns are not nullable and the referential integrity is guaranteed, then you should be able to get the same result by reducing the query to just this:

SELECT
  sum((STOCKCOUNT - QTYONHAND) * CostPrice) VarianceValue
FROM
  STOCKTAKELIST
WHERE
  ListCreate >= '01 Jun 2018' AND
  ListCreate <= '10 Jun 2018 23:59:59' AND
  IncludeInCount = 'Yes' AND
  STOCKCOUNT <> QTYONHAND AND
  Status = 'Complete';


When should startup parameter -E be used? We are starting to build a data warehouse system using SQL Server 2008 R2. One of the senior DBAs told us to consider using a startup parameter -E for the SQL Server service. I read from Books Online that:

  -E: Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse applications that have a limited number of users running index or data scans. It should not be used in other applications because it might adversely affect performance. This option is not supported in 32-bit releases of SQL Server.

My question is how does this parameter help index and data scans? Should we use this parameter if there is a possibility that there are other databases (possibly in operative role) in the same instance?

The SQL Server Customer Advisory Team wrote a blog post that has some information on this setting here.

The –E startup flag
The SQL Server startup flag –E forces SQL Server to allocate 4 extents at a time to each file, essentially quadrupling the stripe size. In heavy insert scenarios, this drives larger block sizes to the disk. Also, your pages allocation will be more sequential with the same data file, allowing better sequential I/O for range and table scan operations (which are common in OLAP workloads).
This startup flag provides most, but not all, of the above mentioned benefits to I/O system – without the overhead of managing multiple filegroups. Be aware that this flag is supported only in 64-bit environments. You can find information about the –E startup flag in File allocation extension in SQL Server 2000 (64-bit) and SQL Server 2005 (KB329526).


Row level security and physical user creation is it an overhead The database that I am currently working has RLS implemented as suggested in Microsoft blogs and a few user blogs.

Example: creating a physical user and EXEC AS USER and selecting from tables for the user selected and REVERT

CREATE USER User1 WITHOUT LOGIN;
CREATE USER User2 WITHOUT LOGIN;
CREATE USER User3 WITHOUT LOGIN;

What if we exhaust the limit for physical users (I think it is server_prinicipal_id whose limit is int), what if your application supports millions of users whose data needs to be restricted based on what they can see?

Is creating physical user for every logical user not an overhead?
Is that a best practice?
Is there a performance overhead?

If you suspect that you'll be creating millions of database users, you should probably not do that.  I don't know what the maximum number of users for a single database is, but see Michael Swart's excellent article Swart’s Ten Percent Rule for why you don't want to come even close to that maximum.

You have a couple of other options rather than creating database users for each connecting user.  See Limiting access to data using Row-Level Security on Microsoft's SQL Server Blog.

  Can I limit access based on AD group memberships?

  Yes, you can use the IS_MEMBER() function in your predicate to check SQL role or AD group memberships. For an example, see the RLS Hospital Demo script.

  What if my application uses connection pooling with a single login for all users?

  No problem, your application can use the new SESSION_CONTEXT feature to get and set session-scoped key-value pairs to identify users for RLS, while still enabling efficient connection pooling.

The IS_MEMBER() approach is similar to the common USER_NAME() approach.

The SESSION_CONTEXT approach has a more detailed example in the docs here:

Scenario for users who connect to the database through a middle-tier application

The filter function pulls an application-set user id out of the SESSION_CONTEXT:

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

Which means the application needs to be modified to set that value whenever it connects to SQL Server to execute queries:

EXEC sp_set_session_context @key=N'UserId', @value=1;


Help restoring a mongodump 3.4 backup into a different instance I am working on backup recovery for MongoDB 3.4. We have an instance that is running and being used. I set up a instance for doing testing of the backup and recovery process. In order to get data I did mongodump for each database and copied the files to my test server. Each backup write files to a directory that has the same name as the database. I used the gzip option to make it easier to move the files around. I am having issues trying to do the mongorestore. Can someone give me the syntax for mongorestore?

Here is the dump command

mongodump --port 27017 --authenticationDatabase admin -u my_admin_user
          -p password -o /backup_dir --gzip -d test_db

(split to multiple lines for readability)

These are the directories with the contents of my_test_db

root@my_vm:~/mongodb_backup_full_data_dump > ls -l
total 20
drwxr-xr-x. 2 root root 4096 Aug 24 13:30 admin
drwxr-xr-x. 2 root root 4096 Aug 24 13:51 data1
drwxr-xr-x. 2 root root 4096 Aug 24 14:20 data2
drwxr-xr-x. 2 root root 4096 Aug 24 14:20 data3
drwxr-xr-x. 2 root root 4096 Aug 24 14:20 my_test_db
root@my_vm:~/mongodb_backup_full_data_dump > ls -l ./my_test_db/*
-rw-r--r--. 1 root root    24263 Aug 24 14:20 ./my_test_db/journal_entries.bson.gz
-rw-r--r--. 1 root root      132 Aug 24 14:20 ./my_test_db/journal_entries.metadata.json.gz
-rw-r--r--. 1 root root      481 Aug 24 14:20 ./my_test_db/journal_users.bson.gz
-rw-r--r--. 1 root root      107 Aug 24 14:20 ./my_test_db/journal_users.metadata.json.gz
-rw-r--r--. 1 root root 14438174 Aug 24 14:20 ./my_test_db/test_collection.bson.gz
-rw-r--r--. 1 root root      104 Aug 24 14:20 ./my_test_db/test_collection.metadata.json.gz

and, this is what happens when I try to do the restore:

mongorestore --port 27017 --authenticationDatabase admin -u my_admin_user -p password -o /backup_dir \
--gzip -d test_db /root/mongodb_backup_full_data_dump/test_db/journal_entries.bson.gz

2017-08-25T15:24:05.487-0400    the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead
2017-08-25T15:24:05.487-0400    checking for collection data in /root/mongodb_backup_full_data_dump/mediax
2017-08-25T15:24:05.487-0400    Failed: error scanning filesystem: file /root/mongodb_backup_full_data_dump/mediax is a directory, not a bson file

Answer is

mongorestore --port 27017 --authenticationDatabase admin -u my_admin_user -p password --gzip /root/mongodb_backup_full_data_dump/

So, you just tell the directory (basedir) where "all" backups are. mongorestore will "restore" those files (in those sub-directories) to databases (come from directory name).

SO, if you change that "test_db" directory name (mv test_db other_test_db) to "other_test_db", it will restore those files to "other_test_db"


Really poor performance when using subquery to get one value Hi I have a table defined like below (Its actually lots of partition tables)
CREATE TABLE sensor_values
(
  ts             TIMESTAMPTZ(35, 6)                    NOT NULL,
  value          FLOAT8(17, 17) DEFAULT 'NaN' :: REAL  NOT NULL,
  sensor_id      INT4(10)                              NOT NULL,
  status         TRIDIUM_STATUS(max) DEFAULT 'unknown' NOT NULL,
  original_value FLOAT8(17, 17)
);
CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index
  ON sensor_values (sensor_id, ts);
CREATE INDEX sensor_values_sensor_id_timestamp_value_index
  ON sensor_values (sensor_id, ts, value);

I am try to get the last days values grouped by sensor_id and day
SELECT sensor_id, ts::date, MIN(ts), MAX(ts), MIN(value), MAX(value) FROM sensor_values
  WHERE ts >= '2021-03-22 00:00:00+00:00' GROUP BY 1, 2;

Works acceptable. I was hoping for better . I am guessing I should have a partial index on the day to speed things up. The explain analyze is shown at
https://explain.depesz.com/s/jYsM
If I add a sub query to get the day to start selecting the time is much worst
SELECT sensor_id, ts::date, MIN(ts), MAX(ts), MIN(value), MAX(value) FROM sensor_values
  WHERE ts >= (SELECT MAX(day) + INTERVAL '1 days' FROM sensor_values_days_private LIMIT 1) GROUP BY 1, 2;

https://explain.depesz.com/s/1o4I
Is the way I can make postgres understand that the subquery returns an unchanging constant and
make it equal in time taken to the first query ?
Am I right in thinking I need a partial index for the datetime column ?
Thanks

CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index
  ON sensor_values (sensor_id, ts);
CREATE INDEX sensor_values_sensor_id_timestamp_value_index
  ON sensor_values (sensor_id, ts, value);

You can do instead:
CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index
  ON sensor_values (sensor_id, ts) INCLUDE (value);

This will create your unique constraint index, and store "value" in it too, since I assume you want to use that as a covering index. Drop the second index, which is now useless. You can use the reclaimed disk space to create an index on (ts) which will help your query for the last day's records.

Am I right in thinking I need a partial index for the datetime column ?

You can't create a partial index with a variable where clause like "current day".
But you can create it only on the last partition only.


What methodology is being used to poll SQL instances when using sqlcmd with the -L switch? I've discovered that issuing sqlcmd -L from the console of a SQL host in my network only reveals other instances on the same subnet. It used to reveal all instances on the network so I was wondering if an O/S or network firewall rule is blocking polling attempts outside the subnet. What firewall rule can I asked my network engineers to establish to allow polling for instances beyond the immediate subnet?

SQL Server discovery is attempted using UDP port 1434.

You'd need to get UDP traffic destined for 255.255.255.255 on port 1434 opened in your local router to see servers from other subnets.

See http://www.mssqltips.com/sqlservertip/2057/why-listing-all-of-your-sql-servers-does-not-always-work/ for more info.


Shrink database is not shrinking as much as expected on a 1.5 TB database. I'm working in an Enterprise system with a 1.5 terabyte database that is on a single filegroup, with the log in Simple Recovery Mode.  Since we have many teams working on different projects in parallel, we have about 20 copies of this database in different environments as well as the main DEV, QA, Staging and Production copies.

For DEV, QA and other environments we do not need the full database so we developed a script to remove old data from the top 20 tables.  This reclaims about 1 terabyte of free space.  Across all environments we will save at least 15 terabytes of disk space on the SAN.  I know this shrinking process causes index fragmentation - but don't mind rebuilding all indexes after the database has been shrunk.

We have two problems with Shrink database:

It seems to take forever; more than 48 hours.
It does not shrink the database as much as expected. The shrink process completes without error but leaves 100's of MB's of free space in the database.

I have tried:

DBCC SHRINKFILE ( 'XXXX_Data', NOTRUNCATE )
GO
DBCC SHRINKFILE ( 'XXXX_Data', TRUNCATEONLY )
GO
DBCC SHRINKFILE ( 'XXXX_Log' ) GO

I have also tried creating a new filegroup and moving data to it - with no result.

Has anybody had to do this type of thing before on a similar sized database?  Any recommendations on how best to do this?  Or does anybody know why the database shrink does not work?

There will always be some free space in the database, stored within pages that are too full for other data. Shrinking databases is an incredibly intensive operation, and will take a lot of time as it moves almost every record to somewhere new. A terabyte is a lot of data. Interestingly, you may have more luck creating space by applying compression such as the Hyperbac stuff from Red Gate. That can help remove the free space and compress the rest, but potentially doing fewer writes (and certainly not having to do lots of log activity) in the process. Your mileage may vary though - I don't want to try to speak authoritatively on how Hyperbac compression of your data will work without knowing much about your data. Hope this helps though...


Is there anybody using the SQL Server feature to create groups of stored procedures differentiated by number? The question refers to the number parameter in this msdn documentation

If you don't you can create multiple stored procedures in SQL-Server differentiated by number and drop them with a single drop.

create procedure dbo.stored_proc1 as select 1
go
create procedure dbo.stored_proc1;2 as select 2
go
exec stored_proc1
-- returns 1
go
exec stored_proc1;2
-- returns 2
go
drop stored_proc1
-- drops both
go

I wonder if this feature is used by anybody for something useful or if it is just a historic curiosity.

Numbered stored procedures are deprecated.

  msdn
  Numbered procedures are deprecated.
  Use of numbered procedures is
  discouraged. A
  DEPRECATION_ANNOUNCEMENT event is
  fired when a query that uses this
  catalog view is compiled.

My team encountered this in a maintenance project. We couldn't figure it out at first. Then, we did some research and found out that it is deprecated. We had to rebuild it to normal stored procs.

Numbered stored procedures won't show up in SSMS's Object Explorer tree.

Numbered Stored Procedures


How can I insert records 'into null' in Oracle? I'm looking for a way to insert records into a 'black hole'; i.e. execute a query for a certain table successfully, but make the records disappear. Something like INSERT INTO NULL, if you will. MySQL has the BLACKHOLE engine, can Oracle do something similar?

The only thing I could come up with was:

creating a view with SELECT NULL FROM DUAL
creating a trigger with INSTEAD OF INSERT and BEGIN NULL; END;

Is this okay?

Obviously, the optimal way is not to execute the insert statement, but I have no control over this - I have to fix this in the DB.

What's the best way to handle this? (the solution with the least impact on performance)

One way you could do something like this on Oracle is to use a temporary table with ON COMMIT DELETE ROWS. The rows will "disappear" at the end of the transaction. If you're using a host environment such as JDBC with autocommit, this will make it look like what you describe. If performance is important, this approach will be much faster than the "discarding trigger" approach.

Syntax:

CREATE GLOBAL TEMPORARY TABLE my_table (
  column1 INTEGER,
  column2 VARCHAR2(100),
  ...
)
ON COMMIT DELETE ROWS;

Documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#ADMIN11633


Is it possible to upgrade mongodb cluster without upgrading arbiters? Is it possible to have running real nodes with version 2.4 and arbiter nodes with 2.0 or 1.8 in the replica set?

Upgrade guides for version 2.2 and 2.4, did not tell anything about upgrading arbiters.

The general recommendation is to have all members of a cluster or replica set running the same version, inclusing arbiters.  Hence, generally you should upgrade arbiters when you upgrade everything else.  In fact I would say to upgrade them first since if you have things configured correctly there will be no interruption in terms of operations from an arbiter upgrade (barring an outage elsewhere while you work).

They will probably work just fine with mixed versions, but since you would be then running in an untested (for long term use) configuration, if you run into any issues at all the first advice is going to be: upgrade.


ERROR: could not find array type for datatype information_schema.sql_identifier I am trying to run the below sql command:

SELECT ARRAY(
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name ='gis_field_configuration_stage'
);

and I get the below error:

ERROR:  could not find array type for datatype information_schema.sql_identifier

Just cast column_name to text to bypass the error:

SELECT ARRAY(
    SELECT column_name::text
    FROM information_schema.columns
    WHERE table_name ='gis_field_configuration_stage'
);

Its original type is information_schema.sql_identifier and its happens that an array of that type is not provided in the predefined types.


Oracle 10g - How to prevent one user from hogging all resources? I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run non optimized queries.

There is a built-in utility called Resource Manager exactly for that purpose:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776


What is Utility_DSN? I'm not exactly sure when it happened, but I've started noticing that some of the hosts in my Enterprise Manager grid have a target named UTILITY_DSN. Does anyone know what this is? The one I noticed today appeared after reinstalling the agent. It shows as a datatbase instance, but with a non-existent Oracle Home.

I know this is a very late answer, but I've only just seen the question. The name of the target and the circumstances under which it appears suggest to me that you are using heterogenous services (or one of the database specific gateway products) in order to connect to a non-oracle database. The configuration of these links requires the creation of a service in the listener.ora on the database server (amongst other things). When an agent attempts to discover databases on a host it follows the following rules.

parse /etc/oratab for databases listed there
parse $ORACLE_HOME/network/admin/listener.ora for statically registered databases
query the listener via lsnrctl to obtain service names.

Stage 2 of this process will pick up any non-oracle targets defined in listener.ora. You probably want to delete them.


SQL Server 2008 R2 Performance Degradation - High Lazy writes/sec and free list stalls/sec I am supporting a customer of ours who has an enterprise edition of our web application living on virtualized servers at their location. They have 3 web servers and 1 DB server. All 4 servers are virtualized with vCenter and have Windows Server 2008 R2 installed.

The DB server is running SQL Server 2008 R2 with 36 GB of RAM, and separate drives for the DB, logs, backups, etc. (7 virtual in total) on a SAS setup, though the backups occur onto a SSD drive.

The web servers are 4 GB RAM each and load balanced. We have a few processes that import data from third parties into our application for end-users, but recently one of these processes have bottle-necked on the server and caused issues. A process that should take hours takes days, and often times fails out, so I have to manually queue up the process in SQL and let it run.

After much investigation, I'm at a loss as to what the issue it. I've confirmed all of the Web Application settings are the same as our hosted environment, which houses customers much larger than this and our DB server is more robust, but the overall load onto the server is great.

Some metrics that I have included cause me to worry about the processes that are actually occurring on the server. I've included these below but I am specifically worried about the Lazy writes/sec and free list stalls/sec, as to me this is indicating that the process is overloading the server in memory and dumping the page to hard disk, which would drive down performance. Is this correct? Can anyone give me ideas on where to go from here? The customer definitely is not liking the idea of adding more resources to the DB server, so I want to be able to prove, definitively, where the issue is at.

Since this is in a virtualized environment, could it also be that the resources just happen to be shared, driving down the performance of the VM and causing this to be flushed to the drive? Any help would be greatly appreciated. My hardware experience has met its limits and I am not a DBA, so I am trying to wrap my head around what all actually goes on in the background with SQL Server.

Thanks!

-Buffer cache hit ratio           1363657/sec
-Buffer cache hit ratio base      1363687/sec
-Page lookups/sec                 28043473454/sec
-Free list stalls/sec             621/sec
-Free pages                       1438/sec
-Total pages                      3932160/sec
-Target pages                     3932160/sec
-Database pages                   3846600/sec
-Reserved pages                   0/sec
-Stolen pages                     84122/sec
-Lazy writes/sec                  77354/sec
-Readahead pages/sec              15305687/sec
-Page reads/sec                   16859120/sec
-Page writes/sec                  7751703/sec
-Checkpoint pages/sec             5408194/sec
-AWE lookup maps/sec              0/sec
-AWE stolen maps/sec              0/sec
-AWE write maps/sec               0/sec
-AWE unmap calls/sec              0/sec
-AWE unmap pages/sec              0/sec
-Page life expectancy             16434/sec

This sounds like VMware might be using the balloon driver to remove memory from the SQL Server VM.

Ensure the VM is configured with locked memory and a CPU reservation.

Typically, for a SQL Server, you want reserved memory to match the amount of memory assigned to the VM.  You'd want to ensure SQL Server is configured with a valid max server memory setting.

Check "Architecting SQL Server on vSphere for some great best-practices from VMware.

Once you've disabled the balloon driver, if you keep seeing a high ratio of stolen pages, check to see what memory clerks are using more than 16MB memory with this query:

SELECT MemoryClerkName = domc.name
    , MemoryClerkType = domc.type
    , Pages = SUM(domc.pages_kb / (domc.page_size_in_bytes / 1024))
    , MB = SUM(domc.pages_kb * 1024) / 1048576
FROM sys.dm_os_memory_clerks domc
WHERE domc.pages_kb > 16384 /* clerks using more than 16MB */
GROUP BY domc.type
    , domc.name
ORDER BY SUM(domc.pages_kb) DESC;

The "Default / MEMORYCLERK\SQLBUFFERPOOL" should typically be the main consumer of memory.

This query will show how much memory SQL Server has allocated, how much it wants to be able to allocate, and how much physical memory is present in the machine:

SELECT TargetMB = dosi.committed_target_kb * 1024 / 1048576
    , CommittedMB = dosi.committed_kb
    , PhysicalMB = dosi.physical_memory_kb * 1024 / 1048576
FROM sys.dm_os_sys_info dosi

This will show you what Windows is reporting for memory state:

SELECT dosm.system_memory_state_desc
FROM sys.dm_os_sys_memory dosm

If there is lots of free memory in your system, you'll see "Available physical memory is high" reported.  If you see "Available physical memory is low", you'd probably benefit from adding memory to the server.


In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption? As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature.

I would just like to know what the specific differences will be between Always Encrypted & the currently available Transparent Data Encryption in SQL Server 2016 so that we can make the correct decision for future projects.

Downsides of Transparent Data Encryption compared to Always Encrypted:

Only protects data at rest - backups and data files are "safe" but data in motion or in memory is vulnerable
Whole database only
All data is encrypted the same way
Backup compression can take longer and be counter-productive

Well, actually, there are some improvements here in SQL Server 2016 that defy what we've typically known about trying to compress encrypted data - it's much better than previous versions, but presumably still worse than only encrypting a handful of columns (untested)

tempdb also inherits encryption – stays even after disabling TDE
Requires Enterprise Edition
Data always accessible to sysadmin

Always Encrypted addresses all of these issues in part or in full:

Data is protected at rest, in motion, and in memory - much more control over certs, keys, and exactly who can decrypt data
Can be just a single column
Encryption type is a choice:

Can use deterministic encryption to support indexes and point lookups (say, SSN)
Can use random encryption for higher protection (say, credit card number)

Since it's not database-wide, backup compression isn't necessarily affected - of course the more columns you encrypt, the worse luck you'll have
tempdb is uninvolved
As of SQL Server 2016 Service Pack 1, Always Encrypted now works in all editions
Data can be protected from sysadmin (but not sysadmin AND Windows security/cert/key admins, in other words you can separate responsibility as long as those two groups don't collude)

There is a limitation, though, and that is that not all drivers and applications can deal with the encrypted data directly, so in some cases this will require updating/changing drivers and/or modifying code.


PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd? I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability )

CREATE TABLE scale_data (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL, -- unique values simulating ID or Timestamp
   id2     NUMERIC NOT NULL -- a kind of Type
);

Populate it with:

INSERT INTO scale_data
SELECT sections.sections, sections.sections*10000 + gen.gen
     , CEIL(RANDOM()*100)
  FROM GENERATE_SERIES(1, 300)     sections,
       GENERATE_SERIES(1, 90000) gen
 WHERE gen <= sections * 300;

It generated 13545000 records.

Composite index on it:

CREATE INDEX id1_id2_idx
  ON public.scale_data
  USING btree
  (id1, id2);

And select#1:

select id2 from scale_data
where id2 in (50)
order by id1 desc
limit 500

Explain analyze:

"Limit  (cost=0.56..1177.67 rows=500 width=11) (actual time=0.046..5.124 rows=500 loops=1)"
"  ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..311588.74 rows=132353 width=11) (actual time=0.045..5.060 rows=500 loops=1)"
"        Index Cond: (id2 = '50'::numeric)"
"        Heap Fetches: 0"
"Planning time: 0.103 ms"
"Execution time: 5.177 ms"

Select#2 --more values in IN - plan has changed

select id2 from scale_data
where id2 in (50, 52)
order by id1 desc
limit 500

Explain analyze#2:

"Limit  (cost=0.56..857.20 rows=500 width=11) (actual time=0.061..8.703 rows=500 loops=1)"
"  ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..445780.74 rows=260190 width=11) (actual time=0.059..8.648 rows=500 loops=1)"
"        Filter: (id2 = ANY ('{50,52}'::numeric[]))"
"        Rows Removed by Filter: 25030"
"        Heap Fetches: 0"
"Planning time: 0.153 ms"
"Execution time: 8.771 ms"

Why plan differs?
Why in #1 it does show like Index condition, but in #2 Filter and number of index scanned cells.
Doesn't sql#1 traverse index in the same way like explain for sql#2 shows?

On real/production DB #2 works much slower, even if search by 2 keys separately is fast

PG 9.5

I wouldn't let this bug you. FILTER in that context, I believe just means more than one conditional statement on the index (which is how IN and array operations  get translated to, afaik). In either one, they're all executed under Index Only Scan Backward. It works the same way with OR

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1219.95 rows=500 width=11) (actual time=0.061..16.159 rows=500 loops=1)
   ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..679161.56 rows=278484 width=11) (actual time=0.060..16.086 rows=500 loops=1)
         Filter: ((id2 = '50'::numeric) OR (id2 = '52'::numeric))
         Rows Removed by Filter: 24673
         Heap Fetches: 25173
 Planning time: 0.206 ms
 Execution time: 16.235 ms
(7 rows)

test=# EXPlAIN ANALYZE select id2 from scale_data
where id2 in (50, 52)
order by id1 desc
limit 500
;
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1153.17 rows=500 width=11) (actual time=0.072..18.604 rows=500 loops=1)
   ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..645299.05 rows=279930 width=11) (actual time=0.070..18.506 rows=500 loops=1)
         Filter: (id2 = ANY ('{50,52}'::numeric[]))
         Rows Removed by Filter: 24673
         Heap Fetches: 25173
 Planning time: 0.187 ms
 Execution time: 18.695 ms
(7 rows)


PL/SQL group orders by date and sum the total I need to group orders by date and sum the total,

My table looks like this :

TEST_DATE
TEST_ID - NUMBER
SUCC_ID - NUMBER

I need to group by date and sum the total, this is my query:

SELECT COUNT(*), TEST_ID, SUCC_ID
FROM MYTABS
GROUP BY TEST_DATE;

How can I?

thank you.

You can think of aggregate queries as two parts:

columns that are included in GROUP BY, and
columns that are included in aggregate functions, like SUM(), MAX(), COUNT(), etc.

To put things very simply, if the column is in the SELECT clause, it has to either appear in the GROUP BY or inside an aggregate function.

In your case, you're probably looking for this:

SELECT TEST_DATE,
       COUNT(TEST_ID) AS numberOfTestID,
       SUM(SUCC_ID) AS sumOfSuccID
FROM MYTABS
GROUP BY TEST_DATE;


sp_WhoIsActive: Are Stats Cumulative for Session or Batch? In the output of sp_WhoIsActive, are columns such as reads, writes, CPU, and wait_info cumulative for the session, or just cumulative for the batch?  We have a query that is blocking a lot and showing millions of reads, but when I run the eight queries in the batch from SSMS, the reads are in the single digits, so I'm trying to understand whether something different is occurring with the way the application is running it to cause it to read so much more.

Or could it be something different?  I notice that the session has 2 open transactions.  Could those stats represent all of the transactions that session currently has active?

sp_WhoIsActive reports reads, writes, and CPU at the batch level as opposed to the session level. This is something that can be tested.

Session ID 57 has the following code in just a single batch:

SELECT MAX(t1.high + t2.high)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

WAITFOR DELAY '00:01:00';

Session ID 56 has the same code but split into two batches:

SELECT MAX(t1.high + t2.high)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

GO

WAITFOR DELAY '00:01:00';

Here's a screenshot of sp_whoisactive after both sessions have been running for a few seconds:

As you can see, those columns reset in session ID 56 because GO defines a new batch.

Waits aren't at the batch or the session level. It's a real time snapshot of what's happening with the query. It's not uncommon to see the wait_info column change wait types or durations while a query is executing.

Your final question is a bit too general for me to attempt an answer, but you could try reading through this resource.


Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of data size? I have two different production systems where the total time for an ETL (Extract, Transform, Load) reporting process (Power BI Report Server) takes around 47 to 60 minutes whether the extract phase pulls data for 15 minutes, 1 hour, 12 hours, or 24 hours. Yes, there are vastly different amounts of rows for those ranges.
Is this normal?
I had expected that the total time for ETL when there is NO data would be .... well... a small amount of total time.  But I must confess that I know nothing about the internals of the T and L phases within a report server.
Can someone shed light on whether the T and L phases are typically fixed in duration (up to a point)? I suspect that this process has not been examined or tuned.

UPDATE
In regards to the T&L phases, I have a theory but no proof.
In the extract phase for these two systems, the total time for the queries remains about the same. 6 minutes regardless of how much data is being pulled. Why? Because a handful of the queries use clustered index scans. I've documented this problem in other questions.
My theory/fear for the T&L phases - is that something similar is happening; a clustered index scan such that it doesn't matter (to a point) how much data is being processed.
But as I mentioned, I have no knowledge (yet) of the inner workings of a Power BI report server's Transform and Load phase. I am making simple observations and curious why the total time does not change when no data is pulled.

There's nothing specifically quantifiable about the abstract concepts of Transform and Load, only their concrete implementations are measurable. To be able to comment on your case, we'd need to know specifically what your Transformation and Loading processes are actually doing. Obviously some Transformations can take multitudes longer than others.
But generally speaking, the amount of data being processed definitely should affect the overall runtime of an ETL process. If there's a significant difference in the amount of data between a 24 hour timeframe vs a 15 minute timeframe, yet your ETL process is executing at roughly the same average runtime for both cases, then something is definitely suspect, and that's not normal.
Even if an index scan is occuring in both cases, if there's a significant difference in the amount of data, the total runtime should certainly reflect it. The runtime of an index scan is linear (generally speaking) based on the number of rows in the index.


Query multiple tables with foreign key filled or is null If I have two table like:

table1 with id, field1 and id_table2 and
table2 with id and field2;

Can I retrieve all rows on table1 also if the foreign key is null without using union?
If I write:

select table1.id, table1.field1, table2.field2 from table1, table2
where  table1.id_table2=table2.id

it will return only the rows that contains id_table2!

if I write:

select table1.id, table1.field1, table2.field2
from   table1, table2
where  table1.id_table2 = table2.id
union
select table1.id, table1.field1, '' as field2
from   table1
where  table1.id_table2 is null

It should work correctly but It seems too much expensive to manage this simply situation! There is something better in mysql5+?

This is what outer joins do. Use a LEFT (outer) JOIN:

select t1.id, t1.field1, t2.field2
from       table1 as t1
       left join
           table2 as t2
       on
           t1.id_table2 = t2.id ;

If you want the empty string (and not NULL) in the result when there is no match, replace t2.field2 in the select list with COALESCE(t2.field2, '').


There is already an object error after database is dropped Started learning SQL Server and stuck with this simple code:

drop database if exists dbo;

create database dbo;

create table dbo.a_table (a_table_a_field   int not null);

Running it twice in SSMS gives: "There is already an object named 'a_table' in the database."

Seems that I do it in completely wrong way. Please, help.

You’ve created a database called dbo but you’re creating the table in the current database. Try adding this before the create table statement:

USE dbo;

I might choose a different database name, too. dbo has specific meaning in SQ Server.


Insert a group of consistent item with foreign key but colliding with existing items Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database.

For example, given a table A:

id  primary key,
name

and a table B:

 id primary key,
 name,
 id_a'  -- foreign key on A

I want to insert:

INSERT INTO A(id, name) VALUES (1, "a");
INSERT INTO B(id, name, id_a) VALUES (1, "b", 1);

But the keys A.id or B.id could be taken already. Is there a way to insert my elements with auto setting key and foreign keys in a consistent way without colliding with existing elements?

In reference to your comment, CTEs are scalable that way. You should do everything in a single transaction anyway. The related question @ypercube suggested should fit your case exactly:
How do I insert a row which contains a foreign key?

Your other option would be to employ lastval() and curval() to identify  values most recently obtained from sequences. Not providing code, your question is too vague. These related questions on SO may be helpful:
Reference value of serial column in another column during same INSERT
PostgreSQL next value of the sequences?


Function that returns table with an additional column I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)?

For example, consider the following function:

CREATE FUNCTION get_users_with_most_videos_since_time(ts TIMESTAMPTZ)
  RETURNS SETOF "user" AS $$
    SELECT
      u.*,
      count(v.id) AS vids_since

    FROM "user" AS u
    INNER JOIN "video" AS v ON v.creator_id = u.id
    WHERE v.created_at > ts

    GROUP BY u.id
    ORDER BY vids_since DESC;
$$ LANGUAGE SQL;

This fails with the error:

ERROR:  return type mismatch in function declared to return "user"
DETAIL:  Final statement returns too many columns.

Fair enough, we're including that vids_since column, which doesn't exist in the "user" table.

So to fix this I'd want to change it to something like:

CREATE FUNCTION get_users_with_most_videos_since_time(ts TIMESTAMPTZ)
  RETURNS
    TABLE (<<< all columns from table "user" >>>, vids_since BIGINT)
  AS $$
    ...

Is there a way to do this without having to re-copy the entire schema of the "user" table here?

AIUI, your wish is to shorten the RETURNS clause of the function. Not sure if you want to establish a dependency on the row type of the table at the same time, but that would make sense here, too.

The form RETURNS SETOFrettype relies on the used type to be stored in the system catalogs. The manual:

  The return type can be a base, composite, or domain type, or can
  reference the type of a table column.

Therefore, just register the row type you desire in the system (once). You can explicitly create a composite type with CREATE TYPE. Or you can do it implicitly by creating another table, view or materialized view. Even just a temporary view or table for a temporary function (dying at the end of the session). Like @Abelisto commented:

CREATE VIEW user_plus AS
SELECT *, null::bigint AS vids_since
FROM   "user"
WHERE  false;

However, SELECT * is resolved to the list of columns at creation time of the view ("early binding"). If you later add a column to the underlying table, the view and its row type are not updated and you get another type mismatch when executing the function. Trying to delete a column form the underlying table will complain about the column in the view that depends on it, though. And you have to change the view - and the function.

Your function can be simplified and faster, btw:

CREATE FUNCTION get_users_with_most_videos_since_time(_ts timestamptz)
  RETURNS SETOF user_plus AS
$func$
   SELECT *   -- effectively the same as: u.*, v.vids_since
   FROM   "user" AS u
   JOIN  (    -- aggregate *before* you join
      SELECT creator_id AS id, count(*) AS vids_since  -- note the column alias
      FROM   video v
      WHERE  created_at > _ts
      GROUP  BY 1
      ) v USING (id)  -- USING only retains one id column
   ORDER  BY v.vids_since DESC;
$func$  LANGUAGE SQL;

It actually makes sense to have this function. You want to filter by a column that's not in the result. Not possible with a plain VIEW.

Aside: I would discourage using reserved words like user as identifiers. That's a loaded footgun.


Have DB users share tables I want to have 2 users of the DB (user_app and user_migration), one for executing migrations (which will have more privileges, like creating/dropping tables + basic usages) and one that the overlaying application will use to interact with the DB (which will have select, insert, update, delete privileges only).

The problem right now, that I'm facing, is that when I create a table from user_migration, the other user doesn't have the permissions to access that table.

This is the SQL code I've got for creating the users:

-- create the role
DO
$body$
BEGIN
  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_roles
      WHERE rolname = 'app_users')
  THEN

    CREATE ROLE "app_users";
  END IF;
END
$body$;

GRANT CONNECT ON DATABASE :dbname TO "app_users";
GRANT USAGE ON SCHEMA "public" TO "app_users";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_users";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO "app_users";

-- create the users and grant them role privileges
DO
$body$
BEGIN
  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_user
      WHERE usename = 'user_app')
  THEN

    CREATE USER "user_app" WITH PASSWORD 'somepswd';
    GRANT app_users TO user_app;
  END IF;

  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_user
      WHERE usename = 'user_migration')
  THEN

    CREATE USER "user_migration" WITH PASSWORD 'somepswd';
    GRANT app_users TO user_migration;
  END IF;
END
$body$;

-- here, assign additional privileges to user_migration to create/delete entities

How can I set up the roles/users in order to achieve the described scenario above?

I suspect that you didn't run this script as user_migration, but as a different user.

Then the ALTER DEFAULT PRIVILEGES statements won't have the desired effect.

You should write:

ALTER DEFAULT PRIVILEGES FOR ROLE user_migration IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_users";
ALTER DEFAULT PRIVILEGES FOR ROLE user_migration IN SCHEMA public
   GRANT USAGE, SELECT ON SEQUENCES TO "app_users";

Without the FOR ROLE user_migration, it will only affect tables created by the user that executed the statement.


Oracle expdp command executed in a command file not honoring %U instead using _U without a sequence I have generated a bunch of expdp commands and stored them in a .cmd file.  When I execute one of the commands by copy and paste into a command prompt, it works fine.  If I try to execute the whole .cmd file, it also works great.

I need to use PARALLEL and %U, so I have added that to the commands.  Again, I run it manually in the command prompt and it works great and honors the %U by creating multiple .dmp files with a sequence number.  But when executing the whole .cmd file with the PARALLEL and %U, it does not honor the %U and just creates one .dmp file with _U.  How can I fix this so it will create the multiple dmp files?

Here is an example of one of my commands that executes fine when copied and pasted into a command prompt but does not work right when executed as a file.

expdp TEST/PWD@ORCL schemas=TEST directory=EXPORT_DIR parallel=4 REUSE_DUMPFILES=Y dumpfile=TEST_20190906_%U.dmp logfile=TEST_20190906_expdp.log

You can escape the % sign to stop Windows swallowing it before Oracle gets a chance to see it:

... dumpfile=TEST_20190906_%%U.dmp ...

As a quick test, in a .cmd file:

@echo expdp ... dumpfile=TEST_20190906_%U.dmp logfile=TEST_20190906_expdp.log
@echo expdp ... dumpfile=TEST_20190906_%%U.dmp logfile=TEST_20190906_expdp.log

then running that from a command prompt shows:

expdp ... dumpfile=TEST_20190906_U.dmp logfile=TEST_20190906_expdp.log
expdp ... dumpfile=TEST_20190906_%U.dmp logfile=TEST_20190906_expdp.log


Crashing CLR on SQL Server 2014 (windows 2012R2) I have this small CLR that does a RegEX function on a string in columns.

When running on SQL Server 2014 (12.0.2000) on Windows Server 2012R2 the process crashes with

  Msg 0, Level 11, State 0, Line 0 A severe error occurred on the
  current command.  The results, if any, should be discarded.

and gives a stack dump if I do

select count (*) from table where (CLRREGEX,'Regex')

but when I do

select * from table where (CLRREGEX,'Regex')

it returns the rows.

Works perfectly on same SQL Server build running on Windows 8.1 .

Any ideas?

-- Edit
It is as simple as it can be

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;           //SqlString, SqlInt32, SqlBoolean
using System.Text.RegularExpressions; //Match, Regex
using Microsoft.SqlServer.Server;     //SqlFunctionAttribute
public partial class UserDefinedFunctions
{
    public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline;
    [SqlFunction]
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
    {
        if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
            return SqlBoolean.False;
    return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
    }
}

So by little changes this works now: Main lesson in C# seems to be the same as in TSQL beware of implicit data conversion.

using System;
using System.Text;
using System.Data.SqlTypes;           //SqlString, SqlInt32, SqlBoolean
using System.Text.RegularExpressions; //Match, Regex
using Microsoft.SqlServer.Server;     //SqlFunctionAttribute
public partial class UserDefinedFunctions
{
public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.CultureInvariant;

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.Read)]
    public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
{
    if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
        return SqlBoolean.False;
    string sqldata = input.ToString();
    string regex = pattern.ToString();
    return Regex.IsMatch(sqldata, regex);
 }

The problem is a locale conflict between the Windows OS and SQL Server (specifically the database where the Assembly is loaded). You can run the following query to see what they are both set to:

SELECT os_language_version,
       DATABASEPROPERTYEX(N'{name of DB where Assembly exists}', 'LCID') AS 'DatabaseLCID'
FROM   sys.dm_os_windows_info;

If they are different then you can definitely get some "odd" behavior, such as what you are seeing. The issue is that:

SqlString includes more than just the text itself: it includes the default collation of the database in which the assembly exists. The collation is comprised of two pieces of information: the locale info (i.e. LCID), and the comparison options (i.e. SqlCompareOptions) which detail the sensitivity to case, accents, kana, width, or everything (binary and binary2).
String operations in .NET, unless explicitly given a locale, use the locale info of the current thread, which is set in Windows (i.e. the Operating System / OS).

The conflict usually occurs when referencing a SqlString parameter without using .Value or .ToString() such that it does an implicit conversion to SqlString. In that case it would cause an exception saying that the LCIDs do not match.

There are apparently other scenarios, such as performing (some / all?) string comparisons, including when using Regex as this case shows (though so far I have not been able to reproduce this).

Some ideas for fixes:

Ideal (expectations will always be met regarding how the comparisons work):

Change either the Windows or SQL Server LCID (default language) so that both match

Less than ideal (the behavior of the Windows locale might not be the same rules for equality and sorting and so there could be unexpected results):

Use the .ToString method or .Value property, which both return the string without the SQL Server LCID so the operations will all be using the OS LCID.

Might help:

Maybe use SqlChars instead of SqlString as it does not bring along the LCID and collation info from SQL Server
Specify that culture doesn't matter via StringComparison.InvariantCulture:

String.Compare(string, string, StringComparison.InvariantCulture) or String.Compare(string, string, StringComparison.InvariantCultureIgnoreCase)
For Regex, specify RegexOptions.CultureInvariant


SQL: SELECT All columns except some Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.

Something like:

SELECT * -the_geom FROM segments;

I once heard that this functionality was deliberately excluded from the SQL standard because changing adding columns to the table will alter the query results.  Is this true? Is the argument valid?
Is there a workaround, especially in PostgreSQL?

Such a feature exists in neither Postgres nor the SQL Standard (AFAIK). I think this is a quite interesting question so I googled a little bit and came across an interesting article on postgresonline.com.

They show an approach that selects the columns directly from the schema:

SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = 'officepark'
            AND  c.column_name NOT IN('officeparkid', 'contractor')
    ), ',') || ' FROM officepark As o' As sqlstmt

You could create a function that does something like that. Such topics were also discussed on the mailing lists, but the overall consensus was pretty much the same: query the schema.

I'm sure that there are other solutions but I think they will all involve some kind of magic schema-queriying-foo.

BTW: Be carefull with SELECT * ... as this can have performance penalties


Unexpected Row of Data when Joining two tables I want to join both tables, and its giving me a much harder time and it should be, at least it doesn't look that complicated. Would be easier if table b would have a value for ID: 0,  but it doesn't.
So how can I get rid of the double rows in Table D

Table a

Article | ID  | Misc |
----------------------
x1      |0    |a     |
x2      |2    |b     |
x3      |1    |c     |

Table b

Article | ID  | Misc2|
----------------------
x2      | 1   | b-1      |
x2      | 2   | b-2      |
x3      | 1   | c-1      |
x3      | 2   | c-2      |

What I want is Table C

Article | ID  | Misc | Misc2 |
------------------------------
x1      | 0   | a    |       |
x2      | 1   | b    | b-1   |
x2      | 2   | b    | b-2   |
x3      | 1   | c    | c-1   |
x3      | 2   | c    | c-2   |

What I get is Table D

Article | ID  | Misc | Misc2 |
------------------------------
x1      | 0   | a    |       | correct
x2      | 0   | b    | b-1   | ID false
x2      | 1   | b    | b-1   | correct
x2      | 0   | b    | b-2   | ID false
x2      | 2   | b    | b-2   | correct
x3      | 0   | c    | c-1   | ID false
x3      | 1   | c    | c-2   | correct
x3      | 0   | c    | c-1   | ID false
x3      | 2   | c    | c-2   | correct

My query:

Select
  a.Article,
  a.ID,
  a.Misc,
  b.Misc2
From
  [table] a
    Inner Join
        [table] b
    On
        b.article =  a.article
        And (
            a.ID = 0
        OR  a.ID = b.ID

You need to use LEFT (outer) join. Try this:

SELECT
  a.Article,
  COALESCE(b.ID, 0) AS ID,
  a.Misc,
  b.Misc2
FROM
  [table] a
    LEFT JOIN
        [table] b
    ON
        b.article =  a.article ;

You may want to use COALESCE(b.ID, a.ID) instead of COALESCE(b.ID, 0), depending on what you want to show (a.ID or always 0) for articles that appear in a but not b.

Tested in: dbfiddle.uk
(test kindly provided by @McNets)


ORA-04020 on Oracle SE1 (11.2.0.1) We've been experiencing some deadlock issues on our Oracle SE1 (11.2.0.1) database server (Red Hat 5/64bit)

Seeing ORA-04020 errors in the logs.

Is going to 11.2.0.2 the only way to resolve the deadlock issue?

Thanks for your assistance.

Simon,

it would be worth looking at the following

are there any invalid objects in the database; if so recompile
have you got this situation if so packaged procedures are the way to go.
have you got user managed locking going on, if so maybe reconsider?


ERD Design for Campground and Campsite I am designing erd for campground and campsite.
In general, a campground has many campsites in it. that lead to one to many relationships. but sometimes campsite has no campground, it appears as the single entity with no parent. then what relationship should be used for these?

This is quite normal in database design. If you look at this guide about cardinality notations you will see that one option is zero to one (optional) (one campsite has zero to one campgrounds).

In SQL you do it by making a FOREIGN KEY constraint from campsite to campground and not declaring the foreign key id in campsite as NOT NULL.


MS Access Crash on DAO connection Can someone take a look at this code and provide a hint as to what it is I am doing wrong. This code taken almost literally from a reference book, as an example on how to use VBA ADO object. Here is the code:
Sub TestMyADODB()
Dim mySQL As String
Dim myConnection As DAO.Connection
Dim myRecordSet As DAO.Recordset
Set myConnection = CurrentProject.Connection ' Crash here
mySQL = mySQL & "UPDATE Amplifier "
mySQL = mySQL & "SET Amplifier.lngCategory = 405, Amplifier.lngSubType = 3 "
mySQL = mySQL & "WHERE (((Amplifier.lngPartIndex)=9))"
    myRecordSet.OpenRecordset mySQL
Debug.Print "DONE"
    myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

Furthermore, I tried using the ADODB object, result, the compiler will not permit the code. I found I needed to add a reference to the MS dll, which I did, still the same result. Then I started getting an error from VBA stating that I cannot use DAO which is now " Outdated " and that I must use ADO.
The rest is 10 hours of lost precious time trying to demystify the ADO DAO issue from multiple articles on tne web. Bottom line, my Access 2007 will literally crash if I use DAO and the compiler will refuse to use ADO.
I am loosing my hairs over this issue, and I need help!  Appreciate
PS: The mySQL string has been tested in an Access query and functional.

You say the code is an example of how to use ADO, but it references DAO. DAO and ADO are two completely different data access libraries from Microsoft. ADO is the newer one. Please don't mix them up.

Try the following:

In your Access Visual Basic IDE, click on Tools | References. Make sure that Microsoft ActiveX Data Objects 2.8 is selected. Unselect any other references mentioning DAO, Data Access Objects, ADO or ActiveX Data Objects. If you don't have version 2.8 installed, try the latest version you've got.

Now, modify your sub to look like this:

Sub TestMyADODB()
    Dim myConnection As ADODB.Connection
    Set myConnection = CurrentProject.Connection

    Dim mySQL As String
    mySQL = mySQL & "UPDATE Amplifier "
    mySQL = mySQL & "SET Amplifier.lngCategory = 405, Amplifier.lngSubType = 3 "
    mySQL = mySQL & "WHERE Amplifier.lngPartIndex=9"

    myConnection.Execute mySQL

    Debug.Print "DONE"
End Sub

I changed the connection type to ADODB.Connection to match the selected library. I also removed the recordset object since that's only used for SELECT queries. Queries that don't return rows can be executed directly on the connection.

This works on my machine at least.


Optimize Large Query Performance I have this query that I can't seem to speed up at all. My running theory is that I need to fix some indexes or something.

I am running the database through the Azure and that does auto indexes, so I haven't had a whole lot of experience with indexes.

When I try to take stuff away, to see if it goes any faster, I get a little bit of speed. But when I take anything important that would probably speed everything up, I need to take out part of the WHERE statement and then I end up with a crap ton more rows and the speed is way worse.

So, here is my Paste The Plan

If anyone has any suggestions I would love to hear them.

Thanks in advance.

Since only the estimated plan is present, these are going to be some guesses.

This part

WHERE j.Status >= 60 AND (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

Creates a filter far in the execution plan:

on this part:

 (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

You could try adding a union to split them up, but due to the amount of joins I would try using a temp table to split some logic in two pieces.

I don't know your datatypes so I am going to use a SELECT ... INTO. Consider changing this to a INSERT ... SELECT .

Temp table insert:

  SELECT  j.JobId,
        j.OrderId,
        j.OrderType,
        j.ShopName,
        j.ShipDate,
        j.CompletionDate,
        j.InstallDate,
        j.DayPriority,
        j.ShipDateConfirmed,
        j.IsAddon,
        j.CashOnDelivery,
        j.FinalMeasure,
        ds.ShopLabel,
        ds.DesignSetId,
        --dsp.DesignSetProcessId,
        j.PaintBottleneck,
        j.DoorBottleNeck,
        j.AssemblyBottleNeck,
        --Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --      dsp.DatetimeStarted,
        --dsp.DatetimeComplete,
        --dsp.BatchNumber,
        --p.Name AS ProcessName,
        --p.ProcessId,
        --p.ZoneId,
        --X.CabinetCount,
        j.InstallScheduled,
        j.RedTicket,
        j.InstallDateRange,
        --V.VitalItems,
        --NV.NonVitalItems,
        --Pj.ParentJobInstalled,
        Schedule.WorkdaysByDate(j.ShipDate, j.InstallDate) AS ShipInstallDiff,
        dss.CatalogId AS SpeciesId,
        dsld.CatalogId AS LowerDoorId,
        dsupd.CatalogId AS UpperDoorId,
        dsf.CatalogId AS FrontId,
        dsfs.CatalogId AS FinishId,
        dsg.CatalogId AS GlazeId,
        dsfo.CatalogId AS FinishOptionId,
        dsds.CatalogId AS DistressId,
        dssh.CatalogId AS SheenId,
        dsi.CatalogId AS InteriorId,
        dsh.CatalogId AS HingeId,
        dsd.CatalogId AS DrawerId,
        dssl.CatalogId AS SlideId,
        j.ParentJobId --added for outer apply
INTO #temp
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
JOIN Species dss ON ds.DesignSetId = dss.DesignSetId
JOIN LowerDoors dsld ON ds.DesignSetId = dsld.DesignSetId
JOIN UpperDoors dsupd ON ds.DesignSetId = dsupd.DesignSetId
JOIN Fronts dsf ON ds.DesignSetId = dsf.DesignSetId
JOIN Finishes dsfs ON ds.DesignSetId = dsfs.DesignSetId
JOIN Glazes dsg ON ds.DesignSetId = dsg.DesignSetId
JOIN FinishOptions dsfo ON ds.DesignSetId = dsfo.DesignSetId
JOIN Distresses dsds ON ds.DesignSetId = dsds.DesignSetId
JOIN Sheens dssh ON ds.DesignSetId = dssh.DesignSetId
JOIN Interiors dsi ON ds.DesignSetId = dsi.DesignSetId
JOIN Hinges dsh ON ds.DesignSetId = dsh.DesignSetId
JOIN Drawers dsd ON ds.DesignSetId = dsd.DesignSetId
JOIN Slides dssl ON ds.DesignSetId = dssl.DesignSetId
WHERE j.Status >= 60;

You could then change the query to this

SELECT  temp.JobId,
        temp.OrderId,
        temp.OrderType,
        temp.ShopName,
        temp.ShipDate,
        temp.CompletionDate,
        temp.InstallDate,
        temp.DayPriority,
        temp.ShipDateConfirmed,
        temp.IsAddon,
        temp.CashOnDelivery,
        temp.FinalMeasure,
        temp.ShopLabel,
        temp.DesignSetId,
        dsp.DesignSetProcessId,
        temp.PaintBottleneck,
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted,
        dsp.DatetimeComplete,
        dsp.BatchNumber,
        p.Name AS ProcessName,
        p.ProcessId,
        p.ZoneId,
        X.CabinetCount,
        temp.InstallScheduled,
        temp.RedTicket,
        temp.InstallDateRange,
        V.VitalItems,
        NV.NonVitalItems,
        Pj.ParentJobInstalled,
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId,
        temp.CatalogId AS LowerDoorId,
        temp.CatalogId AS UpperDoorId,
        temp.CatalogId AS FrontId,
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId,
        temp.CatalogId AS FinishOptionId,
        temp.CatalogId AS DistressId,
        temp.CatalogId AS SheenId,
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId,
        temp.CatalogId AS DrawerId,
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj
WHERE  (j.Status <= 70 OR (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))

Apart from this your functions are also going to hurt, depending on how much data is returned.

You could add this index:

CREATE INDEX IX_Calendar_DateId_filtered
ON Schedule.Calendar(DateId)
INCLUDE(IsWorkDay)
WHERE IsWorkDay = 1;

The rest of the indexing is hard to say with an estimated plan, there are index posbilities such as:

CREATE INDEX IX_Jobs_Status_JobID
ON Jobs(Status,JobId)
INCLUDE(
        OrderId,
        OrderType,
        ShopName,
        ShipDate,
        CompletionDate,
        InstallDate,
        DayPriority,
        ShipDateConfirmed,
        IsAddon,
        CashOnDelivery,
        FinalMeasure,
        PaintBottleneck,
        DoorBottleNeck,
        AssemblyBottleNeck,
        InstallScheduled,
        RedTicket,
        InstallDateRange,
        ParentJobId
        );

CREATE INDEX IX_DesignSets
ON DesignSets(JobId,DesignSetId)
INCLUDE(ShopLabel);

But I think that getting the query to apply predicates earlier and/or splitting up the work will have bigger results at the moment.

EDIT

You could try changing the scalar functions to APPLY's. Since you have many apply's already you could add the first function as two apply's on the temp table insert:

SELECT  j.JobId,
        j.OrderId,
        j.OrderType,
        j.ShopName,
        j.ShipDate,
        j.CompletionDate,
        j.InstallDate,
        j.DayPriority,
        j.ShipDateConfirmed,
        j.IsAddon,
        j.CashOnDelivery,
        j.FinalMeasure,
        ds.ShopLabel,
        ds.DesignSetId,
        --dsp.DesignSetProcessId,
        j.PaintBottleneck,
        j.DoorBottleNeck,
        j.AssemblyBottleNeck,
        --Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --      dsp.DatetimeStarted,
        --dsp.DatetimeComplete,
        --dsp.BatchNumber,
        --p.Name AS ProcessName,
        --p.ProcessId,
        --p.ZoneId,
        --X.CabinetCount,
        j.InstallScheduled,
        j.RedTicket,
        j.InstallDateRange,
        --V.VitalItems,
        --NV.NonVitalItems,
        --Pj.ParentJobInstalled,
        ISNULL([WorkdaysByDate1],[WorkdaysByDate2])
        dss.CatalogId AS SpeciesId,
        dsld.CatalogId AS LowerDoorId,
        dsupd.CatalogId AS UpperDoorId,
        dsf.CatalogId AS FrontId,
        dsfs.CatalogId AS FinishId,
        dsg.CatalogId AS GlazeId,
        dsfo.CatalogId AS FinishOptionId,
        dsds.CatalogId AS DistressId,
        dssh.CatalogId AS SheenId,
        dsi.CatalogId AS InteriorId,
        dsh.CatalogId AS HingeId,
        dsd.CatalogId AS DrawerId,
        dssl.CatalogId AS SlideId,
        j.ParentJobId --added for outer apply
INTO #temp
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
JOIN Species dss ON ds.DesignSetId = dss.DesignSetId
JOIN LowerDoors dsld ON ds.DesignSetId = dsld.DesignSetId
JOIN UpperDoors dsupd ON ds.DesignSetId = dsupd.DesignSetId
JOIN Fronts dsf ON ds.DesignSetId = dsf.DesignSetId
JOIN Finishes dsfs ON ds.DesignSetId = dsfs.DesignSetId
JOIN Glazes dsg ON ds.DesignSetId = dsg.DesignSetId
JOIN FinishOptions dsfo ON ds.DesignSetId = dsfo.DesignSetId
JOIN Distresses dsds ON ds.DesignSetId = dsds.DesignSetId
JOIN Sheens dssh ON ds.DesignSetId = dssh.DesignSetId
JOIN Interiors dsi ON ds.DesignSetId = dsi.DesignSetId
JOIN Hinges dsh ON ds.DesignSetId = dsh.DesignSetId
JOIN Drawers dsd ON ds.DesignSetId = dsd.DesignSetId
JOIN Slides dssl ON ds.DesignSetId = dssl.DesignSetId
OUTER APPLY (SELECT SUM(IsWorkDay) AS Workdays
                    From Schedule.Calendar
                    Where DateId >= j.ShipDate AND DateId <= j.InstallDate) as [WorkdaysByDate1]
OUTER APPLY (Select SUM(IsWorkDay) AS Workdays
                            From Schedule.Calendar
                            Where DateId <= j.ShipDate AND DateId >= j.InstallDate) as [WorkdaysByDate2]
WHERE j.Status >= 60;

EDIT 2

  So, when I remove the scalar functions, on my SELECT INTO query my
  temp table execution time is CPU time = 281 ms, elapsed time = 293 ms.
  and my query execution time is CPU time = 1969 ms, elapsed time = 3694
  ms. On my INSERT INTO query my temp table execution time is CPU time
  282 ms, elapsed time = 442 ms. and my query execution time is CPU time
  1984 ms, elapsed time = 3883 ms.

Most of the execution time is on the second part, what happens when you write the second part of the query like this?

SELECT  temp.JobId,
        temp.OrderId,
        temp.OrderType,
        temp.ShopName,
        temp.ShipDate,
        temp.CompletionDate,
        temp.InstallDate,
        temp.DayPriority,
        temp.ShipDateConfirmed,
        temp.IsAddon,
        temp.CashOnDelivery,
        temp.FinalMeasure,
        temp.ShopLabel,
        temp.DesignSetId,
        dsp.DesignSetProcessId,
        temp.PaintBottleneck,
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted,
        dsp.DatetimeComplete,
        dsp.BatchNumber,
        p.Name AS ProcessName,
        p.ProcessId,
        p.ZoneId,
        X.CabinetCount,
        temp.InstallScheduled,
        temp.RedTicket,
        temp.InstallDateRange,
        V.VitalItems,
        NV.NonVitalItems,
        Pj.ParentJobInstalled,
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId,
        temp.CatalogId AS LowerDoorId,
        temp.CatalogId AS UpperDoorId,
        temp.CatalogId AS FrontId,
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId,
        temp.CatalogId AS FinishOptionId,
        temp.CatalogId AS DistressId,
        temp.CatalogId AS SheenId,
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId,
        temp.CatalogId AS DrawerId,
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj
WHERE (dsp.BatchNumber > 1 AND x.CabinetCount IS NULL))
UNION
SELECT  temp.JobId,
        temp.OrderId,
        temp.OrderType,
        temp.ShopName,
        temp.ShipDate,
        temp.CompletionDate,
        temp.InstallDate,
        temp.DayPriority,
        temp.ShipDateConfirmed,
        temp.IsAddon,
        temp.CashOnDelivery,
        temp.FinalMeasure,
        temp.ShopLabel,
        temp.DesignSetId,
        dsp.DesignSetProcessId,
        temp.PaintBottleneck,
        temp.DoorBottleNeck,
        temp.AssemblyBottleNeck,
        Schedule.Workday(j.CompletionDate, -p.DaysDueBeforeCompleteDate) AS DesignSetProcessDueDate,
  --    dsp.DatetimeStarted,
        dsp.DatetimeComplete,
        dsp.BatchNumber,
        p.Name AS ProcessName,
        p.ProcessId,
        p.ZoneId,
        X.CabinetCount,
        temp.InstallScheduled,
        temp.RedTicket,
        temp.InstallDateRange,
        V.VitalItems,
        NV.NonVitalItems,
        Pj.ParentJobInstalled,
        temp.ShipInstallDiff,
        temp.CatalogId AS SpeciesId,
        temp.CatalogId AS LowerDoorId,
        temp.CatalogId AS UpperDoorId,
        temp.CatalogId AS FrontId,
        temp.CatalogId AS FinishId,
        temp.CatalogId AS GlazeId,
        temp.CatalogId AS FinishOptionId,
        temp.CatalogId AS DistressId,
        temp.CatalogId AS SheenId,
        temp.CatalogId AS InteriorId,
        temp.CatalogId AS HingeId,
        temp.CatalogId AS DrawerId,
        temp.CatalogId AS SlideId
FROM #temp temp
LEFT OUTER JOIN Schedule.DesignSetProcesses dsp ON temp.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount From Products pr Where temp.DesignSetId = pr.DesignSetId AND
                                            (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
OUTER APPLY(SELECT COUNT(pur.Vital) AS 'VitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 1
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS V
OUTER APPLY(SELECT COUNT(pur.ItemId) AS 'NonVitalItems'
                FROM Orders ord JOIN PurchaseItems pur ON ord.ItemId = pur.ItemId AND pur.Vital = 0
                WHERE ord.JobId = j.JobId AND ord.Received = 0) AS NV
OUTER APPLY(SELECT Schedule.WorkdaysByDate(job.InstallDate, Schedule.GetUtahDate()) AS 'ParentJobInstalled'
                FROM Jobs job
                WHERE job.JobId = temp.ParentJobId) AS Pj

WHERE  (j.Status <= 70)


Reducing Errorlog file size We had a user change a password on an account that was on an automation equipment that pinged the sql server many times a second (for no apparent reason - the pinging).

In the end my errorlog ballooned out to >6gb, and my hard drive isn't large enough to accomodate that.

I've cycled it, but it's still at the 6gb.

Is there a way to reduce that?

Thanks

I am not sure of the exact number, but SQL Server will hang on to the previous 6 errorlog files.

Try the following at least 7 times:

EXEC sp_cycle_errorlog;
GO

Source: sp_cycle_errorlog (Transact-SQL)


Help undoing an accidental restore on SQL Server 2014 Yesterday I made a serious mistake¹: Restoring the wrong database.

Background (you may skip this section):

I right-clicked the Test database and proceeded to the "Restore Database" section as usual. Then, when selecting the media (file) for the Source, I browsed for my latest production database backup (performed at 01:00 AM). This was not a mistake; I did need to have up-to-date information on the Test database in order to... test.

However, as soon as I hit OK on the "Add media" dialog, SSMS detected that my backup was made from the Production DB and "silently" changed the "Destination" field to reflect that. I didn't realize this until it was too late. All database changes from 01AM to 06:30PM were lost.

After a few hours struggling on how I could approach my manager and deliver these unhappy news, I decided I would try my best to repair the damage in any way possible, and for a certain extent, I luckily succeed.

Now, the steps taken:

First and foremost, I made sure that today's backup had been performed, since it contained DB entries between 06:30PM to 10:00PM which could not be lost.
Went to the "Restore" dialog for the Production DB, and then clicked the Timeline option hoping to perform a point-in-time restore at precisely 06:29PM.
Unzipped the previous day backup (same one I accidentally restored previously) to my default backup location, and made sure a suitable "log backup" was present on my system.
Set up the desired point in time, clicked "Verify Backup Media" and proceeded to backup.

Now, I'm relieved that it worked, but as expected, all of my tables contains records up to 6:29PM and everything else is missing. It's a large database with more than 100 tables, and hand-checking and inserting everything would take days.

Question: is it possible to "restore" only the missing records which are present on my latest backup, but not in my current state DB? If so, what are the steps necessary?

¹ "Not as serious as yours, I fear" - Bane

Edit in response to James Anderson concerns:

About the backup chain: Actually I do have a backup file with the changes from 06:30PM till 10PM. It was created by a Job I've set up. In fact I even restored this backup to a new temporary Database just to be sure.
About getting more people involved to help: My manager is travelling for next couple of weeks, my team is really small (3 people) and I'm the one in charge right now. Which is to say, I'll inform them, but I'm responsible for handling this alone. Even if manually. If there are any tools that could help please suggest. Thank you everyone.

First off...   I think it is best to come clean to your manager on such issues.  The business will notice the missing data and he will not appreciate being blindsided and not having all the facts at hand.  In that event,  things will be even worse for you.  If you have no formal background in DBAing, you have no business doing DBA tasks and/or the real production DBAs ( if they even exist ) should have ensured that the business is protected against such human errors.

Simply put there is no way to restore individual rows auto-magically. There are tool set available ( Red Gate Data Compare springs to mind ) that allow you to compare data sets and build scripts of the differences, but it is upto you to decide which changes are good.


Firebird database recovery and protection I'm sure many companies are running critical Firebird database servers in production 24/7. Other than regular backups, what backup/recovery/protection techniques are available? I am also interested in backup policies for high availability databases.

Incremental backups - using nback utility in FB 2.x

Back-up is very fast (just dumping of changing pages), can use cascading (database -> monthly large snapshots -> daily deltas from last monthly -> hourls delta form last daily).

However it does not optimize (by recreating) the database. And it does not detect database errors other than top-level, errors of pages allocation (like "orphane page")

OTOH the pages snapshotted are mostly intact, so in case of partially corrupt database they might still have a manually salvageable data. If the corruption would be noticed quickly.

In a sense, that amounts to safe and incremental copying of he database file(s), wit hall the cons and pros.

Usual backup via "gbak"

Reads data as a usual SNAPSHOT transaction, thus db errors would effect it. Some db erors would manifest it in read errors (like if DBA changed column type in a way incompatible with data), but other might result in some data being "invisible" and skipped.

Backup file is stripped of fast-access metadata and geta a lot smaller, which is good for archives (example: 1.1GB of raw database -> 380 MB FBK -> ~700 MB after restore).

In FB 2.x GBak is known to work considerably slower via TCP/IP than via Firebird Service Manager connection. It is told to be amended in forecoming FB 3.

Restore is basically recreating database, so it is slow. But it optimizes the database internal layout and saves some space (no more half-empty pages in the middle of the file).

Due to Firebird being very liberal in online (during active operations of users) scheme change (the safe approach was enforced in 2.0 but undone in 2.1 after uproar), the traditional backup might be "unrestorable", so the attempt at restoring a FBK file into a spare free disk is a must. Until you proven you can restore that backup, you may consider you don't have it.

Tangential idea is Garbage Collection. Usually database have "peak hours" for example heavily used in day and almost unused at night. Sometimes DBA turn off garbage collection and reserve a lot of free space so during the day the database might grow almost uncontrollably. Then at night gbak is run not only to make a copy, but to enforce garbage collection at last.
There also is a built-in redundancy: SHADOW SERVERS implemented back in Borland days. It does not protect from malicious users, virus, program or server bugs, etc. But in case one server crashes like OS or hardware fault, another one is ready to take his mission instantly.

OTOH is the main and shadow servers were in different networks (offices, cities, countries) and the link disappears between them, then one of the networks would see it as crash of the main server and another would see it as a crash of shadow server. When the link would be repaired, the databases would have new different conflicting data entered by users.

This moves us to yet another idea of database replication. IOW the system in general might be designed to the idea tat there is more than single database/server, but they still have the same scheme and should periodically exchange data changes. That is a whole different topic, but it might be to some extent seen as a somewhat partial substitute to backups or crash defense.

PS. additionally you have to learn the difference between SuperServer (targeted at small installations) and Classic/SuperClassic servers. For running 24/7 the second options would be preferable, since frequently the server instances would be shut down after user disconnects. So while "server" as a concept keeps running 24/7, the actual executable programs of it get closed and restarted, easening at potential problems like memory leaks in server or UDFs. OTOH Classic server is more vulnerable to cache synchronization issues like in case of crash during Garbage Collection or attempts at metadata (scheme) changes while users are working.

In FB 3.x they promise to integrate those two approaches to make it a kind of sliding scale options in firebird.conf


Where's my Network Alias list in Oracle? I can't select any Network Alias from the Network Alias Drop-down.

My TNSNames.ora file is in C:\Oracle

My other machine that is a fresh install doesn't have this problem.

I get an error when I click Test on the connection window.

Status :Failure - Test failed: no ocijdbc11 in java.library.path

I'm guessing somehow it's not pointing to the TNSNames.ora file.

Where is that configured?

tnsnames.ora should be located in TNS_ADMIN, which is defined as ORACLE_HOME/network/admin/.

Go to a command prompt (assuming Windows from your mention of "c:\") and execute the following:

echo %TNS_ADMIN%

echo %ORACLE_HOME%

Verify the directories returned, make sure that your tnsnames.ora file is in there.


nested if expressions for string in excel source file in derived column expression I have a column named Product which contains strings.

Everything starting with "ZYT" should be shown: "ZYTOL"
and "AZ" --> "AZAB"
    "UXO" --> "UXOL"
    "ADX" --> "ADXIZN"

How could I write the expression in SSIS transforming process?

Your expression will probably look something like this, in a derived column transformation.

(LEFT(Product, 3) == "ZYT" ? "ZYTOL" :
    (LEFT(Product, 2) == "AZ" ? "AZAB" :
        (LEFT(Product, 2) == "UXO" ? "UXOL" :
            (LEFT(Product, 3) == "ADX" ? "ADXIZN" : Product))))

Please note that depending upon your data type, you may need to cast some of your strings to Unicode/non-Unicode types to get everything to work smoothly.

The syntax uses the ternary operator https://msdn.microsoft.com/en-us/library/ms141680.aspx


Issue shrinking dev sql server DB's log file I have a development db that's running on sql 2014 in 2012 compatibility that I'm having an issue with trying to shrink the log file.    The mdf is 60+gb and the log is 80+gb but since it's dev like to shrink this down.    I've tried shrinking the log after backing up the db from both the gui and command line but doesn't work.  I switched the db to simple recovery model, backing up and trying to shrink multiple times but still nothing. I've also tried dbcc shrinkfile to either truncate the file or set it to a specific size but nothing..  when I run dbcc loginfo I do see entries with the status '2' but backing up the db/log in various attempts doesn't seem clear this out like normal.    Since its dev I even tried deattaching and reattaching without the log and it throws an error, if I try to create the db (that's still unattached) using 'for attach' or 'for attach_rebuild_log' it will either fail or create the log file with the same size and issues as before.   I checked the log_reuse_wiat_desc and it shows 'XTP_CHECKPOINT' which I believe is an in memory setting so wondering if the developer was testing any in memory objects if it could have caused this issue?

Sure appreciate any thoughts on this!  again since it's a dev db, that's backed up, its not a show stopping issue but like to figure this out in case it happens in prod.

Thanks,
Jeff

Is the database in FULL or SIMPLE recovery mode?

One thing that you could try doing is putting the database into SINGLE_USER mode and then shrinking the log file.

I have had success with troublesome systems before by taking two log backups in quick succession and then shrinking the file.  Put all the commands in one script window and execute them to get the commands to execute in rapid succession.

If the database is in FULL mode, then make sure you are taking regular LOG backups; this will keep the file smallish.


How to take partitions or filegroups out of service I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition.

I've inherited a large customer metrics database that grows by about 10 GB per day. This database currently consists of one large data file in one filegroup (PRIMARY). All tables have a datetime column called InsertedDate. I want to horizontally partition the data by InsertedDate, using a separate data file for each calendar week.

In a test environment I added the required additional filegroups and data files to this database, put a clustered index on InsertedDate in each table, and set up the partition function and partition scheme. By querying sys.partitions and other system tables, I've confirmed that the data is now physically residing in the correct partitions and data files.

Among others, the goals are:

Decrease backup time by only backing up the PRIMARY file group and the file group for the current date range (I'm currently running nightly full backups). Once a date range is in the past, that partition will never be written to again, so I'd like to set the filegroup to read-only and back it up one final time.
Be able to eventually take a partition "out of service". After 3 months there's no longer a need to keep older data online, so I'd like to take take that data offline (but be able to bring that data back online again if necessary).

Questions:

1) How to I perform backups on this partitioned database without having to back up the entire database? I can back up an individual filegroup, but to restore it requires the rest of the database, which defeats the purpose of using multiple smaller data files.

2) How to I take a partition out of service? I've read about switching, but it seems that only works if you want to move data between partitions within the same database. I want to be able to simply and safely take a range of data offline (and bring it back online of necessary).

I can't give you an answer to question 1).
But to answer question 2):
What you are requesting is known as the "sliding window scenario".
If you want to eliminate a partition containing data that is no longer needed you have to perform following steps:

Create a table on the same filegroup as the partition you want to delete (typically, this table is referred to as a staging table).
This table has to have exact the same structure as the partitioned table, including indexes.
Switch the partition to the staging table
You have to remove the reference to the filegroup from the partition function vie MERGE RANGE ...
Now you can truncate the staging table and drop it. What is left is an empty filegroup you can remove, too.

Some more details on this at http://technet.microsoft.com/en-us/library/aa964122.aspx and the white paper on partitioning, available under http://msdn.microsoft.com/en-us/library/dd578580%28v=sql.100%29.aspx.


Counting Rows based on values in other columns I'm trying to count the rows of an entire table (approx 3000 rows) based on the content of a column. In the column in question, there can be a single text value or a concatenation of text values separated by commas.

To use the student/class analogy, the table could be a list of students, with the column containing one or more classes to which they are assigned.

Thus "Student1" may belong to "class1, class2, class3" (etc..), while "Student2" may only belong to "Class2".

I simply want the student counts of all classes, thus:

Classes Students
Class1    nn
Class2       nn

and so on.

Please note that this is how the data is received (as a csv file) and I don;t really want to manipulate it unless I have to.  I would prefer to simply execute SQL to produce the results, if possible.

Can anyone help?

Thanks,
Geoff

It will look something like this:

SELECT StudentID, SUM(CASE WHEN INSTR(Classes,:Class) THEN 1 ELSE 0 END)
FROM Classes
GROUP BY StudentID;

This is Oracle syntax, so you will have to translate for Access.


Oracle 10.2g Character Set Migration I am in the process of changing the character set for our database. I have a couple questions with regard to the in-place migration using the CSALTER script and performing the full import/export. According to the Oracle documentation, the use of the CSALTER script requires that

  it can be used only if all of the schema data is a strict subset of the new character set
  Source:Changing the Database Character Set of an Existing Databasee

The current character set is WE8ISO8859P1. However, I want to change it to AL32UTF8. Is WE8ISO8859P1 a subset of AL32UTF8?  And judging by experience, is it preferable to perform the migration using the CSALTER script as opposed to doing a full export/import?

Yes, WE8ISO8859P1 is a subset of AL32UTF8, though a small bit of conversion might be needed (which CSALTER will deal with & CSSCAN will inform you of when you do a preliminary scan).

A must-read for Oracle 10.2 is here.

Doing a full export/import will be more time-consuming than using csscan/csalter.

Another good, albeit old, read is the Oracle Character Migration Best Practices white paper.


Equivalent to json_strip_nulls for Postgres 9.4 The json_strip_nulls function was introduced in 9.5. What could give an equivalent result for 9.4?

I have a json column that is essentially just a text column, but since the contents are JSON, we made it a json column for semantic purposes. Now there are a lot of keys mapped to null, and it's wasting space and causing clutter on the screen.

Casting to jsonb and back doesn't remove the nulls, and 9.4 doesn't give a lot of tools to work with json(b).

I think this can be done using an custom aggregate and existing jsonb functionality:

The following aggregate will "merge" two jsonb values into one. Essentially the same as a || b (where a and b are jsonb values) but as an aggregate for multiple rows.

create aggregate jsonb_merge(jsonb)
(
    sfunc = jsonb_concat(jsonb, jsonb),
    stype = jsonb
);

With that aggregate it's possible to write a recursive function that removes keys with null values:

create function strip_nulls(p_input jsonb)
  returns jsonb
as
$$
  select jsonb_aggregate(o)
  from (
    select jsonb_build_object(t.k, case jsonb_typeof(t.v) when 'object' then strip_nulls(t.v) else t.v end) o
    from jsonb_each(p_input) as t(k,v)
    where t.v::text <> 'null'
  ) t;
$$
language sql;

The inner select turns a jsonb value into a set of key/value pairs and removes the null values recursively. Then the resulting values are aggregated back into a jsonb object.

select strip_nulls('{"one": null,
                     "two": 2,
                     "three": {"four": 1, "five": null,
                               "six": {"seven": 7, "eight": null}
                              }
                    }'::jsonb)

returns:

strip_nulls
-----------------------------------------------------
{"two": 2, "three": {"six": {"seven": 7}, "four": 1}}

The result can be cast back to json

I don't have a Postgres 9.4 installation around for testing, but I think I only used functions available in 9.4.


PostgreSQL Change owner of all tables under a specific schema I am trying to change the owner of all tables under the same schema in one command line. i.e: alter table schema_name.* owner to newowner. Is there a way to accomplish that?

Reassigned Owned

There is a specific privilege command that does just this, RESASSIGN OWNED. This reassigns all objects, not just ones in a specific schema.

Schema-specific

You can generate the ALTER TABLE commands with the following,

SELECT format(
  'ALTER TABLE %I.%I.%I OWNER TO %I;',
  table_catalog,
  table_schema,
  table_name,
  current_user  -- or another just put it in quotes
)
FROM information_schema.tables
WHERE table_schema = 'mySchema';

In psql, you can run them by following it immediately with \gexec