Difference in database dump file size Actually I am bit confused with my database export, while i exporting it shows "Total estimation using BLOCKS method: 20.83 GB" But my DB export dump is only 6 GB. Few days back we deleted some unwanted files and data from our database. But I am not getting idea about this huge difference. Also, my export dump file is not showing any kind of error messages in the log files.

After importing this dump on an server and check DBA segemt using below cmd

"SELECT OWNER, SUM(BYTES)/1024/1024 SIZE_MB FROM DBA_SEGMENTS
group  by owner; "

data seems to be fine but the size shows a large difference.

Please advice if any body have idea on this?

Data pump uses BLOCKS method to estimate the size of the dump by default during the export. The estimate is calculated by multiplying the number of database blocks used by the source objects, multiply the block sizes.

If the tables are fragmented then actual size may vary.

In your case, as you have said, you had heavy deletion in the past which didn't shrink down the High Water Mark leading data pump to count empty blocks as well during the estimation.

Dump file only contains index DDLs during the export and these DDLs runs when we import it resulting in bigger database size.

Demonstration:

For demonstration  I have created one table selecting from all_objects view.

SQL> create table test_tbl as select * from sys.all_objects;

Table created.

The newly created table has 1664 blocks.((1664*8)/1024=13). Which is 13MB.

SQL> select blocks from user_segments where segment_name='TEST_TBL';

    BLOCKS
----------
      1664

Now lets estimate the dump size of this table.

[oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:16:20 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                               13 MB
Total estimation using BLOCKS method: 13 MB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:16:28 2017 elapsed 0 00:00:05

Its exactly 13MB.

Next, lets delete all the records from the table and try estimate the dump size of table.

SQL> delete from test_tbl;

89517 rows deleted.
SQL> commit;

Commit complete.

Lets run expdp-

oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:24:52 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                               13 MB
Total estimation using BLOCKS method: 13 MB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:25:03 2017 elapsed 0 00:00:09

Its still 13 MB.

So such fragmentation may exist in tables that we try to export and the estimated size can be misleading.

Better way to estimate size of the dump file would be using ESTIMATE=STATISTICS in expdp command. But you should gather table statistics to gain accuracy as shown below.

SQL> exec dbms_stats.gather_table_stats('JAY','TEST_TBL');

PL/SQL procedure successfully completed.

Again, lets try to estimate the dump size.

[oracle@ora12c Desktop]$ expdp jay/password@orapdb1 tables=test_tbl  directory=dp_dump estimate_only=y estimate=statistics

Export: Release 12.1.0.2.0 - Production on Sat Jan 28 13:45:02 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "JAY"."SYS_EXPORT_TABLE_01":  jay/password@orapdb1 tables=test_tbl directory=dp_dump estimate_only=y estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "JAY"."TEST_TBL"                            11.43 KB
Total estimation using STATISTICS method: 11.43 KB
Job "JAY"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 28 13:45:07 2017 elapsed 0 00:00:03

Now it's showing accurate result, its 11.43 KB which contains meta data only.

References:
Data Pump Export
DBMS_STATS


Error when installing Oracle 11.2.0.3 in Windows Server 2008 R2 64 bits I downloaded Oracle 11.2.0.3 installer:

p10404530_112030_MSWIN-x86-64_1of7.zip
p10404530_112030_MSWIN-x86-64_2of7.zip
p10404530_112030_MSWIN-x86-64_3of7.zip
p10404530_112030_MSWIN-x86-64_4of7.zip
p10404530_112030_MSWIN-x86-64_5of7.zip
p10404530_112030_MSWIN-x86-64_6of7.zip
p10404530_112030_MSWIN-x86-64_7of7.zip

Then I unzipped them in same server on path:
C:\Software\Oracle 11.2.0.3 64 bits\oracle\

As an administrator user and without UAC in Windows, when trying to install Oracle software (not database) I get error which is attached in image file.

Previously I installed Oracle 11.2.0.1 software which I uninstalled before.

Does anyone got a similar error once? What to do in order to install Oracle 11.2.0.3 software?

Did you uncompressed each zip file into its own folder in C:\Software\Oracle 11.2.0.3 64 bits\oracle\ or into the same folder there?  Almost every reference I can find indicates that this problem is caused by the files not being extracted into the same folder.

http://shethedba.wordpress.com/2009/02/02/11g-installation-error-message-file-not-found-wfmlrsvcappear/
http://www.oraerp.com/Thread-WFMLRSVCApp-ear-file-missing-11g-Installation
https://forums.oracle.com/forums/thread.jspa?threadID=937887
https://stackoverflow.com/questions/11547101/wfmlrsvcapp-ear-file-not-found
http://lucatarrini.net/?p=230

If you have access to My Oracle Support, there is an article covering this topic - 468771.1.


Get the deleted value using while looping through the columns I wrote a trigger to do auditing.  To capture all the changes, I loop through the list of columns.  The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get back the actual field name. I want to get the value, so I don't have to explictly name each field (like I did when I set the NewValue - this works perfectly). Trying to avoid this because, just like most databases, we have tables with several fields. I'm stuck with the syntax on this.  Would like to do something like:

@OldValue = (Select 'd.' + @fieldname from deleted)

Here's my code:

SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

SELECT  @TableName = N'TESTAUDITTABLE'
SELECT  @PKCols =  column_name
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                    INNER JOIN
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
                    ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                    TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
                    and ku.table_name= @TableName
                    ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

SELECT  @UserName = SYSTEM_USER
SELECT  @UpdateDate = {fn now()}

SELECT  @field = 0
SELECT  @maxfield = MAX(ORDINAL_POSITION)
        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

WHILE   @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION)
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName
               AND ORDINAL_POSITION > @field
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D')
       BEGIN
               SELECT @fieldname = COLUMN_NAME
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE TABLE_NAME = @TableName
                       AND ORDINAL_POSITION = @field

                SELECT @ID = coalesce( i.ORIGREC , d.ORIGREC ) ,
                         @Type = case
                                       when i.ORIGREC is not null and d.ORIGREC is     null then 'Insert' -- insert
                                       when i.ORIGREC is not null and d.ORIGREC is not null then 'Update' -- update
                                       when i.ORIGREC is     null and d.ORIGREC is not null then 'Delete' -- delete
                                     end,
                 @OldValue = (Select @fieldname from deleted),
                 @NewValue = (Select i.NAME from #ins)

                FROM inserted i
                FULL join deleted d on d.ORIGREC = i.ORIGREC

               SELECT @sql = '
                    INSERT INTO [dbo].[LE_AUDIT] (
                        DB_USER,
                        TableName,
                        ID,
                        FieldName,
                        OldValue,
                        NewValue,
                        CHANGEDATE,
                        TRIG_TYPE)
                    VALUES (''' + SYSTEM_USER + ''',''' + @TableName +
                    ''',''' + @ID + ''',''' + @fieldname + ''',''' +
                    @OldValue + ''',''' + @NewValue + ''',''' +  @UpdateDate  + ''',''' + @Type + ''')'
               EXEC (@sql)

               print @sql
       END
END

The trigger works fine.  Here is the original source for the link.  https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

I had to modify it to force it to work in conjunction with another app.  Just need to modify the one line to capture the value without specifying the actual field name in the while loop.

I'm short on time so I wasn't able review your code, but I'm basing this answer off your question: How to get the deleted values/capturing changes?

Just yesterday I was reading from CH. 8 of Itzik Ben-Gan's "Microsoft SQL Server 2012 T-SQL Fundamentals" and he had a good section on creating audit tables with the use of the OUTPUT clause. To me this was eye opening and a really cool and simple way of tracking changes such as UPDATES or DELETES.

Assume we have an Orders table with 4 columns: orderid, custid, empid, and orderdate. If we wanted to create an audit table that tracks delete statements the code would be:

DELETE FROM dbo.Orders
OUTPUT
    deleted.orderid,
    deleted.orderdate,
    deleted.empid,
    deleted.custid
Into dbo.OrdersAudit
WHERE orderid = '5';

This code will run your delete statement, and store the all the values into your audit table. The OUTPUT clause can also be used to track your inserts and updates. From what I can tell, the downside is you have to incorporate the OUTPUT clause into all of your procedures. But I wasn't even aware of this clause, and it's super simple to learn the syntax. This probably wasn't what you were looking for, but I was really excited when I read about this clause, and looks like an easy way to track data changes for audits.


Is DEFAULT NULL and 'nullable value without default' completely the same? ALTER TABLE test_table ADD COLUMN a DEFAULT NULL;

vs.

ALTER TABLE test_table ADD COLUMN a;

Both columns will set NULL if column a is not specified.

As far as I know, if I add a column into a table with a default value in production database, it could lead to trouble rewriting all rows with default value.

Is DEFAULT NULL the same?

Not providing any default value or explicitly providing NULL is exactly the same thing for base data types.

Adding a column with a NULL default value, does not require rewriting the entire table. It is essentially only a metadata update for the table.

Quote from the 9.6 manual

  If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead

Note that this has changed with Postgres 11.

When you add a column with a static default (e.g. default 42) the table won't be re-written either. Only if you provide a "volatile" default (e.g. current_timestamp) the table will be re-written.


How to select all data from specific tables for a given schema/prefix in MySQL Database I have a muliti-tenant database structure where each tenant resides in a separate database.
So what I had wanted is to create a procedure that spools data of particular tables from all these databases having the same prefix.
Below is the code i have but seems to result to what i want.
SELECT CONCAT('SELECT * FROM ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'm_appuser'
AND TABLE_SCHEMA LIKE 'mifostenant%'

Result of the above is as below;

Your kind help is highly appreciated.

I think you actually want to use GROUP_CONCAT as per this StackOverflow answer.
This will allow you to prepare all rows into one statement that you can execute.


Setting Up Named SQL Server Instance Network Connection I have successfully installed a second and named instance of MS SQL Server (14) on a Windows Server 16 system. The first instance is the default instance, and it was set up with the correct IP address and port 1433.
I have also looked at this link and followed its instructions where applicable. Under the 2017 SQL Server Configuration Manager, I found my named instance, and saw that its IP address was something outside our firewall, so I modified that to be the server's address. The port was a dynamic port. I went into the SQL server database, and allow incoming connections was checked. The rest of the protocol settings were identical to the default instance, shared memory and tcp/ip enabled and named pipes disabled.
I am using an older SQL Manager to connect, but I know it works to connect to the default instance.
The Windows firewall has the dynamic port added to the 1433 port which is in the all SQL inbound rule. I have restarted the named services, and I still cannot connect. What else should I try?
Edit:
I am adding the error message text; and the browser is running, and was restarted.

TITLE: Connect to Server

Cannot connect to patriot\building_permits.

ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:
OK

As Ronaldo mentioned - SQL Server Browser is needed when using dynamic port.  There are numerous reasons that it may not work correctly, though, too - for example if UDP port 1434 is blocked. (More about Browser here).  Because you said that the IP address was "originally something outside of our firewall" - presumably not the home IP 127.0.0.1 - I'm guessing that you've checked to ensure that TCP is enabled via Configuration Manager->Network Configuration->Protocols for instance. If not, check that:

SQL Server will report the IP and port it's using in its log as well.  Here's an example from my test VM:

Then should be able to connect using either servername,port or IP,port.  You might want to test both methods, both on the local VM and remote boxes to isolate issue.  For example:
.
If connecting locally always works, but remotely doesn't, check firewall.
If connecting with IP always works, but instance name never does, check Browser.
If connecting with IP always works, but servername doesn't remotely, check DNS.
Of course there are numerous other possibilities, but this is where I'd start.


Database design for bookmark system I am creating a bookmark system where people can save their bookmarks. Each user has its own personal page with bookmarks. A bookmark should have atleast one tag and max five. Each user can create unlimited bookmarks / tags.

I created the following tables:

User
----
Id
Email

Bookmark
--------
Id
UserId
Title
Url

Tag
---
Id
UserId
Title
Description

TagBookmark
-----------
TagId
BookmarkId

Is this normalized? Or should I do this differently? Will this perform well with a million bookmarks?

I am also not sure if I should reuse already existing bookmarks / tags.

Example 1:

User1 and User2 both create the bookmark "http://google.com". Should I save this as one bookmark? Or save them for each user individually?

Example 2:

User1 and User2 both create the tag "search-engine". Should I save this as one tag? Or save them for each user individually?

If I do not reuse bookmarks and tags between users these tables may grow quite large.

Designing for maximum performance for an anticipated large user-base, I would suggest ensuring bookmarks and tags are not duplicated unnecessarily.  Using my suggested schema below, http://google.com/ would become a single bookmark row.  Users who want a bookmark to Google would have a row in UserBookmarksTags referencing the Bookmark row and the associated tag row.

I would tend to abstract the UserID away from the Bookmarks table, and use a cross-reference table to determine which users have bookmarks, and what they've tagged each bookmark with.

Using SQL Server, I would do it like this:

USE tempdb;
GO
CREATE TABLE Users
(
    UserID INT NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserName varchar(255)
);
CREATE TABLE Bookmarks
(
    BookmarkID INT NOT NULL
        CONSTRAINT PK_Bookmarks
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , BookmarkName varchar(255)
    , BookmarkURL varchar(255)
);
CREATE TABLE Tags
(
    TagID INT NOT NULL
        CONSTRAINT PK_Tags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , TagName varchar(255)
);

If every entry in your system requires both a bookmark and a tag, you could use the following table for maximum performance:

CREATE TABLE UsersBookmarksTags
(
    UserID INT NOT NULL
        CONSTRAINT FK_UsersBookmarksTags_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NOT NULL
        CONSTRAINT FK_UsersBookmarksTags_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , BookmarkID INT NOT NULL
        CONSTRAINT FK_UsersBooksmarksTags_BookmarkID
        FOREIGN KEY REFERENCES Bookmarks(BookmarkID)
    , CONSTRAINT PK_UsersBookmarksTags
        PRIMARY KEY CLUSTERED
        (UserID, TagID, BookmarkID)
);

The above version of the table UsersBookmarksTags allows you to quickly return a list of bookmarks for a specific user that matches any given tag, or all bookmarks for that user.

Alternately, you could create the cross reference table that allows null entries for both Tags and Bookmarks, as in:

CREATE TABLE UsersBookmarksTags
(
    UsersTagsBookmarksID INT NOT NULL
        CONSTRAINT PK_UsersBookmarksTags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserID INT NOT NULL
        CONSTRAINT FK_UserBookmarks_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NULL
        CONSTRAINT FK_UserBookmarks_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , BookmarkID INT NULL
        CONSTRAINT FK_UserBooksmarks_BookmarkID
        FOREIGN KEY REFERENCES Bookmarks(BookmarkID)
);

This allows rows that have a bookmark without a tag, and also allows tags to be associated to a user without a bookmark.

If there is a requirement to know all the tags a user has added, without those tags necessarily having an associated bookmark, I would consider adding another table for that purpose, as:

CREATE TABLE UserTags
(
    UserID INT NOT NULL
        CONSTRAINT FK_UserTags_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NOT NULL
        CONSTRAINT FK_UserTags_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , CONSTRAINT PK_UserTags
        PRIMARY KEY CLUSTERED
        (UserID, TagID)
);


Query taking long on table with low record count I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs.

Could this be because the table is still so big (~800MB) that it is taking so long?

There are no indexes.

EDIT

I found out from the person that cleared the table that they did a delete instead of a truncate

If the query plan involves a full table scan, Oracle has to read every block from the table up to its high water mark (HWM).  If there are 800 MB of blocks below the HWM, it would make perfect sense that it would take 30 seconds to read all that data.  The number of blocks that actually have data in them, in this case, is irrelevant.

If you truncated the table, however, you should have reset the HWM unless you specified REUSE STORAGE which is not the default.  The fact that the table is still 800 MB implies that you either explicitly had a REUSE STORAGE in your TRUNCATE command or that you didn't actually truncate the table-- perhaps you just did a DELETE to remove all the data, for example.  A DELETE will not reset the HWM.


Error: The user specified as a definer ('Admin'@'%') does not exist Error de SQL (1449): The user specified as a definer ('Admin'@'%') does not exist

I have this error when I'm trying to update a table. I don't know what I must do, because this error only happens with one table, but I don't want to damage other tables and the database.

I've searched how to fix it and I've executed this query:

  Transact-SQL:  show procedure status

But it doesn't show me the definer of my database.

What do I have to do without damaging the database?

Thanks, I executed this query and it worked. We need change the word 'someuser' for 'Admin' in my case.

GRANT ALL ON *.* TO 'someuser'@'%' IDENTIFIED BY 'complex-password';
FLUSH PRIVILEGES


Using Resource Governor to limit query CPU usage for same user I have a remote MS SQL Server and I have several types of queries.

I run some queries that are part of a low-latency production application, and need to ensure they get domne in a timely manner.

But I also have "research" queries that tend to be CPU heavy.

How can I set the classifier such that different queries (from the same user) run with different resource pools so that my research queries don't hog all the CPU?

I am stuck on how to do this because I can't differentiate by user.

The bad news

The only way to throttle the sessions you want is to uniquely identify them. If you cannot do this, then you cannot selectively throttle resources using the Resource Governor.

The good news

There are loads of ways to stratify connections into different resource pools. You may need the cooperation of the person(s) managing the application to segregate the incoming connections, but the options for classifying incoming connections for the purposes of resource pool assignment are functionally infinite. I recommend sticking to connection properties that you set on the application connection string. My favorites are...

host_name()
original_login()
program_name()

...but you can use any arbitrary logic that will uniquely (hopefully) apply to the incoming sessions you want to throttle. If you're feeling dangerous, you can use a lookup table and change the output of your classifier function on the fly (although the docs recommend against this).

For the copy-pasta hungry...

Demo Script

use [master]
go
create resource pool demo_pool;
create workload group demo_group using demo_pool;
create login my_bad_user with password='Plz_Halp_Me_StackExchange';
go
create or alter function dbo.GetResourceGroup()
returns sysname
with schemabinding
as
begin;
    declare @rg sysname = 'default';
    if original_login() = 'my_bad_user' set @rg = 'demo_group';
    if host_name() = 'MYDOMAIN\APP_HOST_1' set @rg = 'demo_group';
    if program_name() = 'My Custom Data Science App' set @rg = 'demo_group';
    return @rg;
end;
go
alter resource pool demo_pool with (
     max_cpu_percent = 25
    ,cap_cpu_percent = 25
);
go
alter resource governor with (classifier_function = dbo.GetResourceGroup);
alter resource governor reconfigure;
go

Demo Cleanup

use [master]
go

alter resource governor with (classifier_function = null)
alter resource governor disable;
drop workload group demo_group;
drop resource pool demo_pool;
drop login my_bad_user;
drop function if exists dbo.GetResourceGroup;
go


Can SQL Server 2014 schema be defined using ODBC? I am trying to use the ODBC driver for SQL Server 2014 to export data from an Autodesk Revit 2015 model to a database.

However, I would like to export to a default database (e.g. db1), creating a new schema for each data export.

Is it possible?

No, ODBC cannot set the schema.

ODBC is (in Wikipedia-speak) "a standard programming language middleware API for accessing database management systems (DBMS).  The designers of ODBC aimed to make it independent of database systems and operating systems."

If you want an array of schemas, they need to be created in the database before they are used.

If you manage your import closely, then is should be possible to change the schema of objects after the import.  E.g. from the SQL Server documentation:
https://msdn.microsoft.com/en-us/library/ms173423.aspx

USE AdventureWorks2012;
GO
ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

This transfers the Address table from the Person schema to the HumanResources schema.

Additionally the CREATE SCHEMA command can be used to create objects within the schema. (Again, this is at the database level.)
https://msdn.microsoft.com/en-us/library/ms189462(v=sql.105).aspx

CREATE SCHEMA Sprockets AUTHORIZATION Annik
  CREATE TABLE NineProngs (source int, cost int, partnumber int)
  GRANT SELECT ON SCHEMA::Sprockets TO Mandar
  DENY SELECT ON SCHEMA::Sprockets TO Prasanna;


Does SQL Server 2008 R2 always fail with a severity level 20 error with certain large text manipulation? When we run this on our SQL Server 2008 R2 instances, it causes a severity 20 error every time. It does not have that issue on our SQL Server 2016 instances.
Query:
DECLARE @v varchar(MAX) = REPLICATE(CONVERT(varchar(max),'a'),524289);
SELECT @v = @v FROM(SELECT 1 AS a) AS b;

Result:
Location:    tmpilb.cpp:3256
Expression:  fNoReaderWriterConflict
SPID:        90
Process ID:  1576
Location:    tmpilb.cpp:3306
Expression:  fNoReaderWriterConflict
SPID:        90
Process ID:  1576
Msg 3624, Level 20, State 1, Line 4
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

SELECT @@VERSION:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
    Aug 19 2014 12:21:34
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

SQLDump0002.txt (from non-production server):
Computer type is Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz.
Bios Version is DELL   - 1
Phoenix ROM BIOS PLUS Version 1.10 2.5.0
8 X64 level 8664, 2 Mhz processor (s).
Windows NT 6.1 Build 7601 CSD Service Pack 1.

Memory
MemoryLoad = 87%
Total Physical = 32762 MB
Available Physical = 4058 MB
Total Page File = 65524 MB
Available Page File = 27332 MB
Total Virtual = 8388607 MB
Available Virtual = 8355194 MB
**Dump thread - spid = 0, EC = 0x00000003EF7E5F00

Is this a bug in this version of SQL Server 2008 R2?
Updates:

This does produce a set of crash dump related files. The layperson-readable sections of them basically repeat the error result above.
We recognize SQL Server 2008 R2 is old and out of support. We will be upgrading.
We have already worked around this issue by setting the large text variable outside of a SELECT statement.
We were curious if this happened in any other environments and we wanted this publicly documented in case it happens to someone else.

There's really no database involved. You can see it hits no tables. But yes we've run this "in" several databases and run CHECKDB against them just to be sure.

The database involved is tempdb (database ID 2).  That's where the value of LOB variables gets written.  You can see that by looking in the sys.dm_db_task_space_usage DMV (if your query weren't failing with an error, that is):

My data database context for that query was "master," but notice that the database_id in the query results in tempdb.
So you could check tempdb for corruption.  This database gets recreated each time the server is restarted, so I'd be surprised if you have persistent corruption there.  Unless you're having disk issues, in which case you should consistently see errors in the Windows "System" Event log that indicate corruption.

All that said, it's also very possible this is a bug in SQL Server 2008 R2 SP3.  There is at least one non-security-related hotfix to that SP (KB3033860).  The errors mentioned there don't look like they match your situation, but could be related (they are assertion failures).
Unfortunately, since SQL Server 2008 R2 is out of support, this won't be fixed even if it is a bug.  Your best bet is to try and work around the problem, or upgrade.


How to I get a list of non subscribed users from this database? I have 2 tables.

1) subscription_types

 | type |
  'type1'
  'type2'

2) subscriptions

| user | sub_type | subscribed |
  'U1'   'type1'    'Y'
  'U1'   'type2'    'Y'
  'U2'   'type1'    'Y'

From this ERD you can see that user 'U2' does not have entry for subscription 'type2'. I'd like to have a report of users who do not have subscription record.

So far I have:

 select user, count(1)
 from subscriptions
 group by user
 having count(1) != (select count(1) from subscription_types);

Which gives me a list of users that do not have all subscriptions. But not which ones they are missing.

I've tried many different variations of join queries where the subscription_type is null, no luck.

I was hoping the following query would solve it but alas it did not

 select user, sub_type
 from subscriptions
 group by user, sub_type
 having sub_type not in (select type from subscription_types);

It seems to me that you want the cartesian product between users and subscription_types, minus the subscriptions:

select distinct s.user, st.subscription_types
from subscriptions s
cross join subscription_types st
except
select user, sub_type
from subscriptions

This can, of course, be expressed in other ways, but this is probably the most obvious one.


Database staging environment for non-IT personnel I'm in the process of proposing a database staging environment to my IT department. The idea is that a non-IT person like me (public works data analyst) would have a place to test solutions, and then either implement them in the live environment myself, or ask IT to implement them if needed. There are a few reasons/scenarios where this environment would be beneficial:

I have some basic database privileges in our live database environment (create table, create view, etc.). I make schema changes about once a week, but it seems insane for me to test and implement these changes in a live environment. There are countless dependencies on the database, so if something goes wrong, it could be disastrous. I'd much rather test things out ahead of time in a separate environment.
I don't have some of the more advanced privileges like create trigger or create function in the live database. This is fine, but I do have a few problems that could be solved by triggers and/or functions. I plan on proposing that I be granted these permissions in the staging environment so that I can develop and test some ideas, and if they work, propose that IT implement them in the live environment.
In general, my IT department doesn't have the time or resources to develop solutions for me. It's really that simple. So if I can do the legwork myself, then my problems are much more likely to be solved.

The 'staging environment for non-IT personnel' seems like a sound enough approach to me, but to be honest, I just made the idea up. I have no idea how this is typically done in the IT/database world.

Is there any sort of established IT/Database practice that would fit this scenario? (Am I on the right track when proposing a database staging environment for non-IT personnel?)

Usually a decent environment would be made of at least DEV -> TEST -> PRE-PROD -> PROD. Development would normally have access to develop in DEV, acceptance testing in TEST and IT to release in PRE and PROD. If you use source control you will avoid problems around editing the same piece of code by different devs at the same time.

Technically you only need schema to be the same as in prod and not need production data below pre-prod but if you are ok with having prod data outside prod environment t you could have an automated restores into dev/test. I have done similar work with Visual Cron and it works wonders.

Likely in order to stay compliant IT personnel  will have to release changes into pre and prod so to make this easier and more resilient you could go down the route of automated deployments.


Is it possible in Firebird to create a single index with ascending and descending columns? For instance, something equivalent to

CREATE INDEX Listing_Date_Idx ON ACTIVITY(Listing,Date_ DESC,Time_ DESC);

The syntax for index creation does not allow for this:

CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
   ON tablename
   { (<col> [, <col> ...]) | COMPUTED BY (expression) }

<col>  ::=  a column not of type ARRAY, BLOB or COMPUTED BY

As you must set a single order for the entire index, not for each individual column.

However,

  It is quite valid to define both an ascending and a descending index
  on the same column or key set.


MySQL : DROP TABLE Starting with a Prefix EXAMPLE : I have more than 30 tables starting with a prefix "dp_" and about 12 starting with "ex_".

QUESTION : How can I drop all tables starting "dp_" in one query ?

Here is a stored procedure that accepts the Database and Prefix String as Parameters:

DELIMITER $$

DROP PROCEDURE DropTablesWithPrefix $$
CREATE PROCEDURE DropTablesWithPrefix(db VARCHAR(64),prfx VARCHAR(20))
StoredProcedure:BEGIN
    DECLARE ndx,maxidx INT;
    DECLARE giventable,SQLSTMT VARCHAR(500);

    CREATE TABLE TableZapList
    (
        droptablesql VARCHAR(512),
        idx INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ) ENGINE=MyISAM;

    INSERT INTO TableZapList (droptablesql)
    SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name)
    FROM information_schema.tables
    WHERE table_schema = db AND SUBSTR(table_name,LENGTH(prfx)) = prfx;
    SELECT COUNT(1) INTO maxid FROM TableZapList;

    IF maxid = 0 THEN
        LEAVE StoredProcedure;
    END IF;

    SET ndx = 0;
    WHILE ndx < maxid DO
        SET ndx = ndx + 1;
        SELECT droptablesql INTO SQLSTMT FROM TableZapList WHERE idx = ndx;
        SET @sql = SQLSTMT;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END WHILE;

    SELECT droptablesql FROM TableZapList;
    DROP TABLE TableZapList;
END;

DELIMITER ;

UPDATE 2011-07-12 14:55 EDT

I just thought of a cleaner, simplistic way. Instead of using a stored procedure, simply use the GROUP_CONCAT function to gather all the tables to zap. Then compose into a single query:

Here is a query to drop all tables that start with wp_pol in the current database:

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
FROM information_schema.tables
WHERE table_schema=database()
AND table_name like 'wp_pol%';

Next thing to do is store the result of it in

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
INTO @dropcmd
FROM information_schema.tables
WHERE table_schema=database()
AND table_name like 'wp_pol%';

Last thing is to execute the dynamic SQL using these three(3) commands:

PREPARE s1 FROM @dropcmd;
EXECUTE s1;
DEALLOCATE PREPARE s1;

Here is a demonstration using MySQL 5.5.12 in Windows that works :

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use garbage
Database changed
MySQL (Current garbage) :: show tables;
+------------------------------+
| Tables_in_garbage            |
+------------------------------+
| datas                        |
| rolando                      |
| wp_commentmeta               |
| wp_comments                  |
| wp_contact_form_7            |
| wp_links                     |
| wp_most_read_hits            |
| wp_options                   |
| wp_pollsa                    |
| wp_pollsip                   |
| wp_pollsq                    |
| wp_postmeta                  |
| wp_posts                     |
| wp_posts_idtracker           |
| wp_tantan_wordpress_s3_cache |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
| wp_w3tc_cdn_queue            |
+------------------------------+
21 rows in set (0.00 sec)

MySQL (Current garbage) :: SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema=database() AND table_name like 'wp_pol%';
Query OK, 1 row affected (0.00 sec)

MySQL (Current garbage) :: SELECT @dropcmd;
+--------------------------------------------------------------------+
| @dropcmd                                                           |
+--------------------------------------------------------------------+
| DROP TABLE garbage.wp_pollsa,garbage.wp_pollsip,garbage.wp_pollsq; |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL (Current garbage) :: PREPARE s1 FROM @dropcmd; EXECUTE s1; DEALLOCATE PREPARE s1;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL (Current garbage) :: show tables;
+------------------------------+
| Tables_in_garbage            |
+------------------------------+
| datas                        |
| rolando                      |
| wp_commentmeta               |
| wp_comments                  |
| wp_contact_form_7            |
| wp_links                     |
| wp_most_read_hits            |
| wp_options                   |
| wp_postmeta                  |
| wp_posts                     |
| wp_posts_idtracker           |
| wp_tantan_wordpress_s3_cache |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
| wp_w3tc_cdn_queue            |
+------------------------------+
18 rows in set (0.00 sec)

MySQL (Current garbage) ::

Give it a Try !!!


Database as a service (DbaaS) over dimensions--performance, scalability, & reliability? MySql Database as a service:

Amazon
Xeround

Another eg. PostgreSQL Database-as-a-Service.
You can get the list of similar products here.

Does anyone have details on their performance, reliability, and scalability of these DbaaS? Reading literature on these products make them sound too good to be true. And my inner cynical sense tells me to question the claims.


0xdbe doesn't display tables I am trying to use 0xDBE and connect it to my testing MS SQL Server database.

When I connect to it 0xDBE doesn't display schemas names but shows only users in visual tree.

There is a view in 0xDBE:

And the same database in Microsoft SQL Server Management Studio:

There are no errors in the console.

As of 11/4/15, there is an update to the SQL Server provider that clears up creating the data source. You may need to refresh the data source to actually list the tables.


User defined function performance disadvantages I have a database with a lot of UDFs that are called by a long running process involving lots of data manipulation and calculations.

My thinking in using UDFs is to separate out logical units of information from the tables underlying.  For example, if i am trying to get information about a car i might have several tables like Color, Model, Year, etc that i would have to join each time to get a Car.  Instead, I would have a function like fnCar() to get a denormalized view of the data.

I call these functions a lot during my long running process and I'm wondering if it would be better if instead I had a denormalized working table,view, or temp table to do my data manipulation and calculations.  Is there some disadvantage to using UDFs in general that I should be aware of in terms of performance?

For example, I make some calculations using a UDF.  I then unpivot that data and store in a table.  Whenever i need to use that data again, I call a UDF to pivot the data back out.  The reason we do it this way is to keep our calculations flexible.  We don't want to change the data model if we add/remove/change the calculations.

--Calculate some values in a function

declare @location table
(
    id int,
    lattitude float,
    longitude float
)

insert into @location select  1, 40.7, 74
insert into @location select  2, 42, 73
insert into @location select  3, 61, 149
insert into @location select  4, 41, 87

declare @myLattitude float
declare @myLongitude float
set @myLattitude =43
set @myLongitude = 116

declare @distance table
(
    id int,
    distance float
)

insert into @distance
select id, sqrt(power(lattitude-@mylattitude,2)+power(longitude-@mylongitude,2))
from @location

--Store unpivoted data in a table
declare @unpivot table
(
    id int,
    attribute varchar(100),
    attributeValue float
)

insert into @unpivot
(
    id,
    attribute,
    attributeValue
)
select id
    ,attribute
    ,attributevalue
from
(
    select
        L.id,
        L.Lattitude,
        L.Longitude,
        D.Distance
    from @location L
        inner join @distance D
        on L.id=D.id
) a
unpivot
(
    attributeValue for attribute in
    (lattitude, longitude, distance)
) x

--retrive data from store via pivoting function for reporting

select *
from @unpivot
pivot
(
    max(attributeValue) for Attribute in (lattitude, longitude, distance)

) x

There comes a time when you have to decide what is more important to you, maintenance of the code or the speed at which it runs? The reason it is running slow is because UDF's get processed on a row-by-row basis - SQL Server performs best using set-based operations. There is no reason why you cant keep your UDF's as there may be time where it is more practical to use them than to expand out the query.

My advice is this: If your data set is small feel free to use the UDF's; but if you are working with a large data set then take the time to write, test and optimize the query to obtain the best results - it will benefit you in the long run when you don't have users complaining their system is slow.


Reseeded Identity not showing in GUI I am reseeding my Identity Seed with the following T-SQL:

USE DATABASENAME;
GO
DBCC CHECKIDENT ('dbo.DOCUMENT', RESEED, 400000000);
GO

I am subsequently checking it went through:

USE DATABASENAME;
GO
DBCC CHECKIDENT ('dbo.DOCUMENT', NORESEED);
GO

But when I use the GUI it doesn't reflect my changes:

It leaves me to believe that I have mixed up definitions and terms here and that I'm not actually changing what I think I'm changing.

Incidentally when I add a new row to the table DOCUMENT then the PK column shows a value starting at 400000000.

Can someone explain what I am missing or is this a bug?

Thanks!

The GUI is showing the result of looking at the table design, which shows IDENTITY(1,1) for the column in question.

Run the following code, then check the definition of the identity value by using the GUI; you will see the GUI reflects the design of the table, which is independent from the actual next value of the IDENTITY column.

USE tempdb;

CREATE TABLE dbo.IdentTest
(
    ID BIGINT NOT NULL IDENTITY(1,1)
);

INSERT INTO dbo.IdentTest DEFAULT VALUES;
SELECT * FROM dbo.IdentTest;

DBCC CHECKIDENT('IdentTest', RESEED, 400000000);

INSERT INTO dbo.IdentTest DEFAULT VALUES;
SELECT * FROM dbo.IdentTest;

After running this, if you look at the GUI, you see:

If you "script" the design of the table; you get the following:

USE [tempdb]
GO

/****** Object:  Table [dbo].[IdentTest]    Script Date: 2015-10-27 4:54:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[IdentTest](
    [ID] [bigint] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

Note the scripted table definition still shows IDENTITY(1,1), since that is the definition of the table, which is unchanged by using DBCC CHECKIDENT.  The MSDN page on DBCC CHECKIDENT says the following:

  Checks the current identity value for the specified table in SQL Server 2016 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Note this does not mention anything about changing the definition of the table itself.


Architecture of database I am newbie in this field.

I need to design database. Abstract: there is a counter in every apartment, which is monitoring utility bills. Payments for gas and electricity. There are many houses in a city, but there is only one counter in one apartment. I need to create proper database.

I suppose that it looks like 'one-to-many' connection, so:

First table: id_counter (primary key), address
Second table: id_counter, cubicmetre_amounts
Third table: id_counter, consumed_Vt

Do I understand it correct, is it really 'one-to-many' and if so, what should be the primary key in a second and third tables?

Thanks in advance!

You would need a table for the individual bills, too, would you not?

Table 1: individual bills with external key to table 2
Table 2: Apartments (are "houses" the same as apartments?) with external key to Table 3
Table 3: City information

Your keys would be whatever unique identifier you have set up for each item.

If houses can hold multiple apartments, then you will need to have another table inbetween Table 2 and Table 3.

and yes, this really are one-to-many relationships between all mentioned tables.
You cannot have a single bill that can go to multiple apartments, and an apartment cannot be in multiple cities (or houses).


SQL Server Standard 2016 Service Pack and Cumulative Update We are currently using SQL Server 2016 Standard Edition with ALWAYS-ON High Availability. We have two different clusters of SQL servers. We need to update it to the latest version of SQL (As per my research Cumulative Update package 13 (CU13) for SQL Server 2016 SP)

Cluster 1 - Currently on Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64)

Cluster 2 - Currently on Microsoft SQL Server 2016 (SP1-CU12) (KB4464343) - 13.0.4541.0 (X64)

Query 1 - Can we update SP1-CU12 to directly SP2-CU13

Query 2 - Can we update SP2-CU4 to directly SP2-CU13

Query 3 - What steps should be taken before and after upgrading

Query 4 - What should be the roll-back plan, incase of failure

Any other word of advice or caution for someone doing for the first time will be appreciated.

Query 1: You have to look this up in the release notes. Normally you can do that, but you always need to check the release notes regardless. There is a possibility that it is not possible. Please refer to this link,  which will guide you to all the SPs and latestes CUs available, including the release notes.

https://sqlserverbuilds.blogspot.com

Query 2: The same applies as the answer to your query 1.

Query 3: Since you are 'only' (and I say this between quotes, because you always need to plan things very carefully) upgrading to a new service pack/CU combination, not a new version (like from SQL Server 2016 -> SQL Server 2017), it is fairly straight forward. Install the (chain) of service pack(s)and required CU. However:

Make sure you test and document the upgrade path AND the rollback path before you start the upgrade for your production environment.
Since you are upgrading a cluster, make sure the cluster is healthy and you can are sure you can failover without any issues.
It would be advisable to create a baseline prior to the upgrade in order to compare things afterwards. The most easy way to do this is use performance monitor to capture different SQL Server and Windows Server counters over a month or so. This way you can get an insight of your normal workload and compare it with the workload after the upgrade. This can help in determining if the upgrade caused some issues or not. Even when you think there are none, you could be surprised. :-)
Preferably perform the rollout in phases over your development, test, quality and production environment. Do not upgrade all the environments within a short time span, but let every environment run for a week or more, depending on your requirements, to filter out any possible issues. If you do not have a dev, test, quality, production environment, then at least perform the upgrade on a test environment. Maybe you could use Distributed Replay to simulate a production workload to see what the effects are.
Determine if you want to upgrade to the latest service pack. You are asked not give advice based on personal opinion here, but evaluate for yourself if you want to be on a Service Pack that was brought out yesterday and not all issues/ bugs are known. ;-) There were updates that brought SQL Server to it's knees or even... cause corruption... Check how old it is and also check if other people have had issues with it after they deployed it. Just do some plain 'ol Google searching. :-)
Make sure you thoroughly read all the release notes to check if there are any changes that could have a high impact on your environment. For example, discontinued features, new features, etc.
Just take the extra full copy-only backup before you start and put it somewhere safe. Just in case the 3rd party backup software fails the restore... You don't want to explain that to your boss.
Perform a health check prior to upgrading, so you don't upgrade a broken server. Check the logs, check for performance issues, check the backups, Windows Event Viewer, make sure you check if your DBCC CHECKDB ran recently. Although, you should do this by default. You could also use sp_Blitz from Brent Ozar.
Personally I always perform a DBCC CHECKDB directly after the upgrade AND an extra copy-only backup, just to be sure the upgrade didn't cause corruption and you always have the starting point from directly after the upgrade for possible referencing later on.
Don't fool around with VM snapshots, especially on a cluster. They are not backups and could potentially lead to problems with the snapshot/storage/ io performance. It is more an extra risk than a benefit IMHO.
Check disk space prior to the upgrade
Disable backups and SQL Server Agent jobs during the upgrade
Since you are upgrading a cluster, you can perform a rolling upgrade; https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-ver15#perform-a-rolling-upgrade-or-update. My strategy is to failover to the second node, to make sure it works, then upgrade the first node, fail back to the first node and upgrade the second node. Be aware that this could mean downtime, since the SQL Server service is stopped on one node and started on the one you fail over to. Some applications can handle this, some not.
Verify the version afterwards to make sure you are indeed on the expected version.

I think there are a lot of checklists out there on the internet and you can actually easily find them. Have you done some more research but posting this question?

Query 4: The rollback plan is uninstall the SP/ CU, but that is only the SQL Server part of it. Make sure that you test the whole scenario.

Is this info usefull?


Configure sp_send_dbmail to send emails ONLY to certain recipients For our environment test we need to configure sp_send_dbmail to send emails only to certain recipients. Let's say a stored procedure executes the following code:

EXEC msdb.dbo.sp_send_dbmail
    @recipients='developer@myorganization.com'; 'realuser@myorganization.com',
    @body = @myBody,
    @subject = @mySubject,
    @profile_name =@myProfileName

I need the email to be sent only to developer@myorganization.com, and not to realuser@myorganization.com. Is there a way that i can configure this?
Changing realusers email address won't do it for me.

There is nothing built in that you could use already.

I would suggest creating a "wrapper" stored procedure for sp_send_dbmail, that accepts the exact same parameters and passes them through, with additional logic in place to check the @ recipients parameter that is passed against your exclusion list.


SQL Query :Is it possible to count / group based on date with a sum over the group I have the following question:
To determine the progress of a production process the following records are stored:

+-------+-------------------------+-------+-------+
| Order | Date                    | Count | State |
+-------+-------------------------+-------+-------+
| X     | 2018-02-10 00:00:00.000 | 10    | 0     |
| X     | 2018-02-11 00:00:00.000 | 4     | 1     |
| X     | 2018-02-12 00:00:00.000 | 2     | 2     |
| X     | 2018-02-13 00:00:00.000 | 3     | 1     |
| X     | 2018-02-14 00:00:00.000 | 1     | 3     |
+-------+-------------------------+-------+-------+

The result of the query should be:

+-------+-------+-------+
| Order | Count | State |
+-------+-------+-------+
| X     | 3     | 0     |
| X     | 5     | 1     |
| X     | 1     | 2     |
| X     | 1     | 3     |
+-------+-------+-------+

10 items are initially booked on state 0
On 2018-02-11 4 items are progressed to state 1, resulting in 6 remaining items on state 0 and 4 items on state 1.
On 2018-02-12 2 items are progressed from state 1 to state 2, resulting in 6 items on state 0, 2 items on state 1 and 2 items on state 2.
On 2018-02-13 3 items are progressed from state 0 to state 1, resulting in 3 items remaining on state 0, 5 items on state 1 and 2 items on state 2.
On 2018-02-14 1 items progressed from state 2 to 3, resulting in 3 items remaining on state 0, 5 items on state 1 and 1 item on state 2 and 1 item on state 3.

It is not possible to skip a state.

Sample script:

USE [tempdb]
GO

CREATE TABLE [dbo].[OrderItemState](
    [Gid] uniqueidentifier not null
            constraint PK_OrderItemState primary key nonclustered
            constraint DC_OrderItemState default newid(),
    [Order] nchar(16) not null,
    [Date] datetime not null,
    [Count] int not null,
    [State] int not null
)
GO

insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-10', 10, 0)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-11', 4, 1)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-12', 2, 2)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-13', 3, 1)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-14', 1, 3)
GO

In my opinion window functions will be the cleanest way to accomplish this (you didn't specify your version, so I'm assuming you're using 2012 or later).

WITH Progress AS
(
SELECT [Order], State,
  SUM(Count) AS ItemsProgressedToThisState,
  LEAD(SUM(Count), 1, 0) OVER (PARTITION BY [Order] ORDER BY State) AS ItemsProgressedPastThisState
FROM OrderItemState
GROUP BY [Order], State
)
SELECT [Order], State, ItemsProgressedToThisState - ItemsProgressedPastThisState AS [Count]
FROM Progress
ORDER BY [Order], State

The CTE isn't really necessary, you can subtract the SUM and LEAD directly. I just wanted to make the logic clear: count the number of items that have reached the current state, and then subtract the number of items that have reached the next state.


Upgrading to SQL Server 2017 from SQL Server 2012 Upgrade or New Build? Any suggestions?
Does anyone have any gotchas or war stories to share about this process?

We have a 2012 instance in Prod that has both OLTP and OLAP databases. We are creating two instances, one to be dedicated to each of those tasks. The OLAP instance is a new build, however to minimize end-user down time we are considering an upgrade for the instance that will house the OLAP databases.

I'm looking for general recommendations that go beyond what I'm reading in the Microsoft docs.

Unless it's a CU or SP, I'd always stick to a side-by-side upgrade over an in-place one.  It allows you to rigorously test and UAT the new version and ensure that all your queries/processes still run efficiently and aren't plagued by new Execution Plans or query engine "features" that can turn out to be a serious hindrance.

If you can't spout the license, try upgrading to 2017 Developer Edition, which is still a full version.  As long as you don't use the Dev edition for production, you can do all the dev/testing/staging you want on it.

Here's my favorite post on the topic.
The e-book is available in the FRK. (I seem to remember this post being longer but I guess not).
https://www.brentozar.com/archive/2015/10/sql-server-migration-and-upgrade-checklist-video-and-ebook/


SQL Server 2008 R2 Readable Mirror I know that SQL Server 2012 and 2014 offer AlwaysOn Availability Groups which provide this functionality, but I'm still stuck on SQL Server 2008 R2 for a while.

I recently came across this AWS whitepaper: http://media.amazonwebservices.com/AWS_RDBMS_MS_SQLServer.pdf

I was confused by this chart on page 11:
which seems to indicate that the mirror database can be used for reading (checks in the 'Readable Copy' feature column), just as it can when using Log Shipping, because it's been my experience that the mirror database cannot be used at all--it's only purpose is for failover.

The answer to this question: Database mirroring is limited to an original database seems to confirm my suspicions that the mirror database is NOT readable, though only in passing.

Is this whitepaper wrong, or are SQL Server 2008 R2 mirror databases indeed readable?

If they are, what does one need to do to do so, as attempting to connect in SSMS results in a window connected to the master database, and running USE [database] gives the following error:

Msg 954, Level 14, State 1, Line 1
The database "database" cannot be opened. It is acting as a mirror database.

UPDATE: I understand that there are ways to kind-of workaround this issue and get something derived from the mirror and time-delayed that is readable, and I suppose one could make an argument that a snapshot is a "Readable Copy", but that's very different from the type of readable copy you'd get through log shipping or transaction replication, as both of those would be automatically updated, even if slightly out-of-date (maybe snapshots can be too--I'm stuck on Standard Edition, so I'm not as familiar with the Enterprise Edition feature set).  In addition to that, a truly readable synchronous mirror (like using AlwaysOn Availability Groups in 2012+) would provide a perfectly synchronized readable version--which is FAR more useful because the mirror could be used to distribute read-query load and avoid all the issues with out-of-date data being returned.  My primary purpose (as stated in the question above) was really to get a definitive answer about the readability of the mirror itself (not a snapshot or a replica of it).  While the provided answer agrees with my own experience, the only links to definitive sources provided are for possible ways to achieve similar results--neither of them definitively state that mirrors are not readable.  I will accept the first answer that provides such a link.

A SQL Server 2008 R2 mirror is not itself readable.

If you are using Enterprise Edition, you can create a named snapshot database from the mirror.  This allows you to create a point-in-time readable database that can be used for pretty much any query that does not do any updates.  (Though you could use another database (tempdb or a workdb) to do auxiliary work that would not fit the read-only snapshot.)

See: https://msdn.microsoft.com/en-us/library/ms175158(v=sql.105).aspx

When the snapshot gets 'old' enough you create a new snapshot database. You will eventually need to drop the older snapshot, but it is possible to support several snapshots. (This depends on the amount of activity in your database and server.)

If you use Standard Edition and have the same need, look into log shipping using RESTORE with STANDBY.  You will need to manage when the next restores get made in order to use the STANDBY feature.

See: https://msdn.microsoft.com/en-us/library/ms178615.aspx

It says of the STANDBY option: "The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores."

Therefore, this method requires you to delay the restore of logs to match the schedule for how long you want to keep this point-in-time in action.  A LOG Restore will end the STANDBY and restore to the following point, which could be restored with a new STANDBY period.

Depending on your needs one of these approaches can give you a readable point-in-time  database. No updates become visible in the database for the period of (a) the snapshot, or (b) the period between restoring updates.


Convert two columns to json I have a json column with a json array in the form of

[{key : "key", value : "value"}, {key : "key2", value : "value2"}]

that I wish to convert to a new form

{"key" : "value", "key2" : "value2"}.

I can use the following query to get rows with key column and value column

SELECT json_array_elements(somejson)->'key', json_array_elements(somejson)->'value' FROM temp;

However I can't seem to come up with a way to convert that to a nice key-value JSON object. Since I'm on 9.3, I can't use json_object or json_build_object either.

Well, it would have been a lot faster and easier with 9.4, but it's doable with 9.3 in simple situations.

SELECT
('{' ||       -- Opening brace for the JSON object
string_agg(   -- Get all the key/value pairs together in the same string
concat_ws(':', '"' || a || '"', b || ''), ',') || '}')::json -- Concatenate values to "key" : "value" format

FROM (
-- Extract the key and value from each array element
SELECT json_array_elements(myjson)->'key' a,
json_array_elements(myjson)->'value' b FROM temp) AS FOO;

Not fun, but thankfully this is a one-time thing.

This can't be used to update a table, but you can create a temporary table and insert the values there first.


What type of locks are needed when creating a Postgres index concurrently? I know that when an index is being created concurrently, only a SHARE ShareUpdateExclusiveLock lock is needed.
But is there a more strict type of lock needed at the very start of the process for a small amount of time? Or is only a SHARE ShareUpdateExclusiveLock lock needed for the entire operation?
I'm asking because I'm wondering what types of timeouts are advantageous to set when creating an index concurrently.

Well, when documentation is not enough it is possible to looking directly in code. I will give links to already released PostgreSQL 13.1 (REL_13_1 tag) in order to avoid possible line number changes in future.
I probably will not describe the entire code path of the command from the client. By grammar we know that we need IndexStmt command node - same grammar for both concurrently and non-concurrently create index. Everything useful will start in ProcessUtilitySlow
And here, in comment to case T_IndexStmt, can be found direct answer for question:
/*
 * .... To avoid lock upgrade hazards, it's
 * important that we take the strongest lock that will
 * eventually be needed here, so the lockmode calculation
 * needs to match what DefineIndex() does.
 */
lockmode = stmt->concurrent ? ShareUpdateExclusiveLock
    : ShareLock;

In general PostgreSQL will do not upgrade lock during command execution. And will take the strongest lock that will eventually be needed. For CREATE INDEX CONCURRENTLY it's ShareUpdateExclusiveLock. Not a SHARE lock as mentioned in question.
But let's continue reading:

in case of partitioned table find_all_inheritors will acquire same lockmode on inheritors.
in transformIndexStmt can be found some locking modes, but NoLock and AccessShareLock - they are weaker than ShareUpdateExclusiveLock
in DefineIndex we see another reminder:

     * To avoid lock upgrade hazards, that lock should be at least
     * as strong as the one we take here.

And decide to take againg ShareUpdateExclusiveLock for concurrently build. This part of the code is noticeably long (still not as large as, for example, planner), but readable.

I am missing something? Probably. So let's build PostgreSQL with -DLOCK_DEBUG option to debug locking behavior and see that happens:
./configure  --prefix=/home/melkij/tmp/pgdev/inst --enable-cassert --enable-debug CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG" --enable-tap-tests
make -sj 4
make install
initdb ...
pg_ctl start ...
# then psql with follow commands
create table foo as select generate_series(1,1000) as id;
set trace_locks = true;
create index concurrently on foo (id);
select oid, relname, relkind from pg_class where oid in (16387,16393);
#  oid  |  relname   | relkind
#-------+------------+---------
# 16387 | foo        | r
# 16393 | foo_id_idx | i

trace_locks will log many things, but we are interested in:
[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] AccessExclusiveLock
[vxid:3/62 txid:495] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/63 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/63 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] RowExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] RowExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] ExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] ExclusiveLock

lock [12664,16387] here means database OID = 12664, pg_class OID = 16387 (such rows are logged from LockAcquireExtended).
So, we actually acquire only ShareUpdateExclusiveLock on table itself. OID=16393 with several other locks is the index that this command built. Although heavy locking levels are mentioned for this object, it will not interfere with other queries. Create index concurrently command is specially designed to work safely while running normal application queries.


UPDATE table with another table data I want to update the table car_check field fencing.(total 40 rows).I also use postgis function.
I think it's a little difficult.Any suggestions?

The code is the first carid example.

UPDATE car_check
set fencing=ST_Contains
from (
SELECT ST_Contains(ST_AsText('01030000..'),
'POINT(23.912784 120.99178)')
)as foo
where carid='AD-5487' and cargroupid='1'

Try this untangled version to update all rows of char_check:

UPDATE car_check c
SET    fencing = ST_Contains(u.geom, ST_MakePoint(t.lat::float8, t.lng::float8))
FROM   test t
      ,rule r
JOIN   (
   SELECT DISTINCT ON (polyname)
          polyname, geom
   FROM   unknown_table
   ORDER  BY polyname, version DESC
    ) u USING (polyname)
WHERE  t.id = c.carid
AND    r.cargroupid = c.cargroupid

Major points

Use the simpler ST_MakePoint to construct a point geometry from numeric input.
You do not need ST_AsText() at all, since your column geom is of type geometry already.
Fastest and simplest way to get the geometry for the biggest version per polyname in table unknown_table is to use DISTINCT ON, which is a Postgres extension of the SQL standard. More details in this related answer:
How do I efficiently get "the most recent corresponding row"?
In reference to the currently accepted but invalid answer: the concatenation operator in Postgres (and standard SQL) is || (not +)


How can I run this SQL query across all of my databases at once? I have a query to let me know basic info on backups. I have several SQL servers that I'd like to be able to query at once. Is it possible to run the following query across several servers at one time?

SELECT
   A.[Server],
   A.database_name,
   A.last_db_backup_date,
   B.backup_type,
   B.backup_start_date,
   B.expiration_date,
   B.backup_size,
   B.logical_device_name,
   B.physical_device_name,
   B.backupset_name,
   B.description
FROM
   (
   SELECT
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name,
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   GROUP BY
       msdb.dbo.backupset.database_name
   ) AS A

   LEFT JOIN

   (
   SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full Database'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
       WHEN 'F' THEN 'File Level'
       WHEN 'G' THEN 'File Level Differential'
       WHEN 'P' THEN 'Partial'
       WHEN 'Q' THEN 'Differential partial'
   END AS backup_type
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
   A.database_name

The easiest way is to create a registered server group in Management Studio, add the server registrations you want, then right-click the group and select "New Query." The query you run will be run against all of the servers (with an additional leading column that indicates which registration it came from):

Of course this relies on all of the servers in the group being alive and reachable.

Some other approaches include Central Management Servers (here are official docs on that) and PowerShell (here's a blog post from Jonathan Kehayias showing how to run a query against all of the servers listed in a text file).


Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost How to do this ?

Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost

The hosts file on a server is typically located at: C:\Windows\System32\drivers\etc. In this location you will see several files, one of which is the Hosts file. Edit with caution.


MySQL - prevent OS cache for query On a high load production server I have many SQL selects which are unique or re-use will be in hours. I use SQL_NO_CACHE but these queries are still cached with OS cache. The server has 64G ram and used ram is still 100%, about 47G is OS cache. If I manually drop OS cache with sync; echo 3 > /proc/sys/vm/drop_caches, then everything run more smoothly. But in few seconds the OS cache grows again to about 47G and uses all free ram. And I know, most of this data will be read only once, so there is no point for caching.

Is there any way to say to OS - "I know, I will not read this data again, don't cache them." ? Would be great, if this can be set for single mysql query or process.

And one more thing, I know about innodb_flush_method=O_DIRECT, but these tables are Aria engine on MariaDB

No. There  is no possibility to pass for operating system request not to cache file reads for IO done for specific query. You can reduce sysctl parameter vm.swappiness to 1 to make cache recovery more aggressive, but that affects all IO caching in the server.


Double Concurrent Restore from one backup for 3 node AG? I have a 400GB database on SQL 2014 Enterprise Edition Instance.  I took a full backup as a single file (not striped), it restores to 8 datafiles and 1 log file.  I put the backup in a shared folder that all instances have access to.

I need to restore without recovery (NORECOVERY) on two instance, then follow through with the other steps to join them in the AG.

Using one SSMS I used 2 query windows to, restore the database on the two different instances (different VM servers in different locations) at the same time.  I used sp_whoisactive to monitor progress as both restored.

Everything worked fine, the final t-log and join with the wizard gave me a stable synchronized database in the AG.

About 30 minutes in I got wondering if, this was counterproductive.  Clearly it worked, the time to completion was similar (or a bit longer) then similar single restores. But there are multiple distractions and ongoing process, so my perception may be wrong.  From watching the Wizard I know it only restores to one instance at at time.

All things being equal is doing a double concurrent restore from one backup to two instances an optimal timing choice? (is it faster or slower then one after the other?)

I found 2 related posts that do not seem to address my question

Would it be possible/wise to execute 2 parallel restores from a single backup?
Restoring two databases to precisely the same time

All things being equal is doing a double concurrent restore from one backup to two instances an optimal timing choice?

It is definitely faster, since the wizard is doing it in sequential order.

You can use sqlcmd or powershell runspace or (possibly coming in dbatools cmdlets) to do parallel restores.

To even speed up that, split the full backup into stripes and enable instant file initialization.

note: make sure that there is no adhoc log backup happening since it can break your lsn and your restore.


Tracking errors from prepared SQL statements I use extended events to store database errors, like this:

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        [severity] >= (11)
    )
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This helps me to easily trace which application is causing them.
I have something similar to track slow queries and other special cases.
However, I noticed from the SQL Server Profiles that a PHP/Laravel application is not sending some statements directly, but using RPC:Completed: they look like prepared statements, receiving the argument at run time.
How do I expand my ErrorCapture to include errors caused by statements executed via RPC:Completed?

The code you have does return errors from prepared statements. The below PowerShell executes a prepared statement against SQL Server that will intentionally fail. This appears in Profiler with RPC:Completed as you've described:

$SqlConn = [System.Data.SqlClient.SqlConnection]::New("Server=localhost;Database=master;Trusted_connection=true;")

$SqlConn.Open()

$SqlCmd = [System.Data.SqlClient.SqlCommand]::New($null, $SqlConn)

$SqlCmd.CommandText = "SELECT @Val/0"

$idParam = [System.Data.SqlClient.SqlParameter]::New("@Val", [System.Data.SqlDbType]::Int, 0);
$idParam.Value = 1;
$SqlCmd.Parameters.Add($idParam);

$SqlCmd.Prepare()
$SqlCmd.ExecuteNonQuery()

$SqlConn.Close()

When I run this with Profiler and your Extended Events session running, I can see the error reported in both tools:

Extended Events:

Profiler:

One issue may be that the messages are below severity 11, and so your EE session is excluding them. If you run a Profiler trace using the TSQL_Replay template and include "User Error Messages" under Errors and Warnings do your errors from prepared statements appear then?

If so, check the error column in Profiler and run the below against SQL Server replacing 8134 with the error number from Profiler:

SELECT * FROM sys.messages WHERE message_id = 8134

Check the severity column in that to identify the severity of the error returned by your prepared statements.


PostgreSQL: Prevent SQL injection for like query with % input We are allowing the user to post the contents on our application, in a case user entered the % in the title of the post and we are running like query to fetch the data from the database.
Here is the snippet from the post table

id |         title           | short_description |   long_description
 1   how many % amounts you       ----------          --------------
     should save                --------------       -----------------

Query we are running:

SELECT id, title from post where post.title LIKE '%string entered by user%'

Now if the user enters amounts then query works fine and returns something like.

SELECT id, title from post where post.title LIKE '%amounts%'

and returning the desired result.

But, when a user enters how many % amounts in the search field, it is returning all the rows which are starting from how many and contains amounts in the upcoming words, which is wrong because the generated query is.

SELECT id, title from post where post.title LIKE '%how many % amounts%'

And another thing is if the user enters only % the query returns all the results.

If we try escaping the % or other special characters and the result is way more different than it should be.

Please suggest a solution if you have any.

Quoting the like expression

The percent or underscore characters in the user-supplied string must be quoted as documented:

  To match a literal underscore or percent sign without matching other
  characters, the respective character in pattern must be preceded by
  the escape character. The default escape character is the backslash
  but a different one can be selected by using the ESCAPE clause. To
  match the escape character itself, write two escape characters.

For instance:

If the user submits ab%, the code must do:

where title like '%ab#%%' escape '#';

Be aware that the user might submit a string with your escape character in it.
For this reason it has to be escaped itself. If for instance the user submits abc%def#, the code must do:

where title like '%abc#%def##%' escape '#';

Using strpos

Another solution is to check the result of strpos instead of using LIKE to find if a substring is in a larger string. strpos does not have wildcards characters and returns a non-zero number if the substring is found.

select strpos('abcdef', 'cd') > 0 ;
 ?column?
----------
 t

strpos also uses the Boyer Moore Horspool  algorithm which be faster in some cases, especially since PostgresQL 12 where it applies it directly to UTF-8 contents without converting them first to wide chars internally.


Users with highest sum up price I have a table with name, email, user_id, and price. On this table, a user can appear more than once depending on how many purchases. I am trying to get select all details of users with the highest sum of price.
id   name  price   email    user_id
--   ----  ----- ---------  -------
1    Mike  100   @mike.com      5
2    John  50    @john.com      6
3    Mike  100   @mike.com      5
4    John  50    @john.com      1
5    Mark  50    @mark.com      3
6    Mike  100   @mike.com      5

As you can see from above Mike has the highest pricing in total I want a query that will get the sum total of column "price" and select all details of the row with the highest total first in an array and follow by the next higher. The result should be like below:
id   name  price   email    user_id
--   ----  ----- ---------  -------
1    Mike  300   @mike.com      5
2    John  100   @john.com      6
3    Mark  50    @mark.com      3

I want the 'id' to reset and start listing 1,2,3,4,5... and also I have other columns on the table I will like to get.
Also in addition I will like to know the count(how many times the users purchase).

Basically you want this.
The problem is that john has 2 user_ids 1 and 6
Else i would put the user_id also in the GROUP BY
AND if you only the highest 3 You have to add also LIMIT 3
A MySQL 5.x Solution to your problem

CREATE TABLE sumprice (
  `id` INTEGER,
  `name` VARCHAR(4),
  `price` INTEGER,
  `email` VARCHAR(9),
  `user_id` INTEGER
);

INSERT INTO sumprice
  (`id`, `name`, `price`, `email`, `user_id`)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5'),
  ('2', 'John', '50', '@john.com', '6'),
  ('3', 'Mike', '100', '@mike.com', '5'),
  ('4', 'John', '50', '@john.com', '1'),
  ('5', 'Mark', '50', '@mark.com', '3'),
  ('6', 'Mike', '100', '@mike.com', '5');

SELECT   @id:= @id +1  `id`, `name`, SUM(`price`) price , `email`, MAX(`user_id`)
FROM sumprice,(SELECT @id:=0) a
GROUP BY `email`, `name`
ORDER BY price DESC

id | name | price | email     | MAX(`user_id`)
-: | :--- | ----: | :-------- | -------------:
 1 | Mike |   300 | @mike.com |              5
 2 | John |   100 | @john.com |              6
 3 | Mark |    50 | @mark.com |              3

db<>fiddle here
In Mysql 8

CREATE TABLE sumprice (
  `id` INTEGER,
  `name` VARCHAR(4),
  `price` INTEGER,
  `email` VARCHAR(9),
  `user_id` INTEGER
);

INSERT INTO sumprice
  (`id`, `name`, `price`, `email`, `user_id`)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5'),
  ('2', 'John', '50', '@john.com', '6'),
  ('3', 'Mike', '100', '@mike.com', '5'),
  ('4', 'John', '50', '@john.com', '1'),
  ('5', 'Mark', '50', '@mark.com', '3'),
  ('6', 'Mike', '100', '@mike.com', '5');

SELECT   ROW_NUMBER() OVER(
        ORDER BY SUM(price) DESC
    )  `id`, `name`, SUM(`price`) price , `email`, MAX(`user_id`)
FROM sumprice
GROUP BY `email`, `name`
ORDER BY price DESC

id | name | price | email     | MAX(`user_id`)
-: | :--- | ----: | :-------- | -------------:
 1 | Mike |   300 | @mike.com |              5
 2 | John |   100 | @john.com |              6
 3 | Mark |    50 | @mark.com |              3

db<>fiddle here
In Oracle it is the same

CREATE TABLE sumprice (
  id NUMBER(5),
  name VARCHAR(4),
  price NUMBER(5),
  email VARCHAR(9),
  user_id NUMBER(5)
);

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES('2', 'John', '50', '@john.com', '6');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('3', 'Mike', '100', '@mike.com', '5');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('4', 'John', '50', '@john.com', '1');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('5', 'Mark', '50', '@mark.com', '3');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('6', 'Mike', '100', '@mike.com', '5');

SELECT     ROW_NUMBER() OVER(
        ORDER BY SUM(price) DESC
    ) id ,name, SUM(price) price , email, MAX(user_id)
FROM sumprice
GROUP BY email, name
ORDER BY price DESC

ID | NAME | PRICE | EMAIL     | MAX(USER_ID)
-: | :--- | ----: | :-------- | -----------:
 1 | Mike |   300 | @mike.com |            5
 2 | John |   100 | @john.com |            6
 3 | Mark |    50 | @mark.com |            3

db<>fiddle here


sp_BlitzIndex - "Cannot Insert the value null into column 'index_sanity_id'..." Hi I'm getting this error message from the latest version of sp_BlitzIndex on one of my test servers:

  database failed to process. Cannot insert the value NULL into column
  'index_sanity_id', table
  'tempdb.dbo.#IndexSanitySize____________________________________________________________________________________________________00000002626F'; column does not allow nulls. INSERT fails.

That typically happens when indexes are being added or dropped at the same time sp_BlitzIndex runs. If you have code that is dynamically adding/dropping tables in a user database, for example, that'll trigger this. The Github issue for that is tracked here.

To test that theory, try running sp_BlitzIndex again at a time with lower end user activity, or restore the same database to another server with no activity. (Granted, that's not going to get you index utilization data, but it'll prove out the theory.)

If you still get the error even with no activity on the server (again, restored to a different server so you can make sure), then we would need to step through the code to understand what kinds of indexes on your database are triggering the error. You can see it by modifying this part of the T-SQL:

    CREATE TABLE #IndexSanity
        (
          [index_sanity_id] INT IDENTITY PRIMARY KEY,

Change that to just:

    CREATE TABLE #IndexSanity
        (
          [index_sanity_id] INT IDENTITY NULL,

And then at the end of the proc:

    SELECT * FROM #IndexSanity WHERE index_sanity_id IS NULL

That way you can see which indexes are triggering the error, and we can figure out how to exclude them from being checked.

I created an issue in Github to track this and fix it in the next build:

sp_BlitzIndex - null error when populating #IndexSanitySize


How to read a bson file using C# I have a bson file dumped by mongodump command.

How can I read the content of this file to memory with C#??

I don't want to use the mongorestore command.

You will need to study the BSON specs to create your own functions, or use one of the available libraries that others made already: http://bsonspec.org/implementations.html


problem query postgresql I have problems when I want to generate a query in posgretsql, I was trying and I do not know how to calculate the following

My table has the following data:

CREATE TABLE "abc-ventas"
AS
  SELECT *
  FROM ( VALUES
    ( '0001', 'sandía' ,  2.000, 3.000 ),
    ( '0002', 'lápiz'  ,  3.000, 5.000 ),
    ( '0003', 'manzana',  1.000, 2.000 ),
    ( '0004', 'naranja',  2.000, 3.000 ),
    ( '0005', 'arroz'  ,  1.000, 5.000 )
  ) AS t(CODIGO, DESCRIPCION, COSTO, NETO);

I need to calculate the following information

PARTIC= NETO / SUM(TOTAL NETO)

CODIGO   DESCRIPCION    COSTO     NETO   PARTIC PARTIC-ACUM
0001     sandía        2.000      3.000   17%
0002     lápiz         3.000      5.000   28%
0003     manzana       1.000      2.000   11%
0004     naranja       2.000      3.000   17%
0005     arroz         1.000      5.000   28%
                                 -------------
                                 18.000   100%

Now to calculate the "partic-acum" order DESC "partic"

PARTIC-ACUM= PARTIC + PREVIOUS PARTIC

CODIGO   DESCRIPCION    COSTO     NETO   PARTIC PARTIC-ACUM CLASIF
0002     lápiz         3.000      5.000   28%          28%
0005     arroz         1.000      5.000   28%          56%
0001     sandía        2.000      3.000   17%          72%
0004     naranja       2.000      3.000   17%          89%
0003     manzana       1.000      2.000   11%          100%

Once generated the result I want to classify it in A B C

If the value is 0 to 80:   A
If the value is 81 to 95:  B
If the value is 95 to 100: C

This is my desired output

CODIGO   DESCRIPCION   COSTO      NETO    PARTIC PARTIC-ACUM CLASIF
0002     lápiz         3.000      5.000   28%          28%    A
0005     arroz         1.000      5.000   28%          56%    A
0001     sandía        2.000      3.000   17%          72%    A
0004     naranja       2.000      3.000   17%          89%    B
0003     manzana       1.000      2.000   11%          100%   C

The query I have is the following

SELECT
  codigo,
  descripcion,
  cantidad,
  costo,
  neto,
  (SELECT sum(neto) FROM "abc-ventas") as total,
  (round(neto /(SELECT sum(neto) FROM "abc-ventas"),4)*100) AS participacion
FROM "abc-ventas"
ORDER BY participacion DESC;

As a first note, you should never quote table names or any identifiers in PostgreSQL. This is a bad practice.

SELECT
  *,
  CASE
    WHEN "partic-acum" > 95 THEN 'C'
    WHEN "partic-acum" > 80 THEN 'B'
    ELSE 'A'
  END AS clasif
FROM (
  SELECT
    codigo,
    descripcion,
    costo,
    neto,
    round(partic) AS partic,
    round(sum(partic) OVER (ORDER BY partic DESC, codigo)) AS "partic-acum"
  FROM (
    SELECT
      codigo,
      descripcion,
      costo,
      neto,
      neto/sum(neto) OVER ()*100 AS partic
    FROM "abc-ventas"
  ) AS t
) AS t;

Outputs

 codigo | descripcion | costo | neto  | partic | partic-acum | clasif
--------+-------------+-------+-------+--------+-------------+--------
 0002   | lápiz       | 3.000 | 5.000 |     28 |          28 | A
 0005   | arroz       | 1.000 | 5.000 |     28 |          56 | A
 0001   | sandía      | 2.000 | 3.000 |     17 |          72 | A
 0004   | naranja     | 2.000 | 3.000 |     17 |          89 | B
 0003   | manzana     | 1.000 | 2.000 |     11 |         100 | C
(5 rows)

Explanation

First we generate partic with a window function. We do not round yet.

SELECT
  codigo,
  descripcion,
  costo,
  neto,
  neto/sum(neto) OVER ()*100 AS partic
FROM "abc-ventas"

This outputs

 codigo | descripcion | costo | neto  |         partic
--------+-------------+-------+-------+-------------------------
 0001   | sandía      | 2.000 | 3.000 | 16.66666666666666666700
 0002   | lápiz       | 3.000 | 5.000 | 27.77777777777777777800
 0003   | manzana     | 1.000 | 2.000 | 11.11111111111111111100
 0004   | naranja     | 2.000 | 3.000 | 16.66666666666666666700
 0005   | arroz       | 1.000 | 5.000 | 27.77777777777777777800
(5 rows)

Then we generate the cumulative partic.

  SELECT
    codigo,
    descripcion,
    costo,
    neto,
    round(partic) AS partic,
    round(sum(partic) OVER (ORDER BY partic DESC, codigo)) AS "partic-acum"
  FROM (
    SELECT
      codigo,
      descripcion,
      costo,
      neto,
      neto/sum(neto) OVER ()*100 AS partic
    FROM "abc-ventas"
  ) AS t;

Generating this. Notice this is where we round.

 codigo | descripcion | costo | neto  | partic | partic-acum
--------+-------------+-------+-------+--------+-------------
 0002   | lápiz       | 3.000 | 5.000 |     28 |          28
 0005   | arroz       | 1.000 | 5.000 |     28 |          56
 0001   | sandía      | 2.000 | 3.000 |     17 |          72
 0004   | naranja     | 2.000 | 3.000 |     17 |          89
 0003   | manzana     | 1.000 | 2.000 |     11 |         100
(5 rows)

Then we run the whole thing in a virtual table to generate the rankings.

This may look like a more complex query, but it's all done in a single table pass -- it's the fastest solution I think you can get out of this.


Question on Oracle rowid column in every table I want to know how to use the object number that extract from rowid column? It's seems that this oracle number is not the same as object_id in dba_objects view. Anyone can help me with it please? Thanks.

sys@ORCL>l
  1  SELECT rowid, DBMS_ROWID.Rowid_object(ROWID)   "OBJECT",
  2     DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
  3     DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
  4     DBMS_ROWID.Rowid_row_number(ROWID)   "ROW",
  5     employee_id, first_name
  6  FROM   hr.employees
  7* WHERE  department_id = 30
sys@ORCL>/

ROWID              OBJECT       FILE      BLOCK    ROW EMPLOYEE_ID FIRST_NAME
------------------ ---------- ---------- ---------- ---------- ----------- --------------------
AAAR5pAAFAAAADPAAO  73321          5    207     14         114 first_name
AAAR5pAAFAAAADPAAP  73321          5    207     15         115 first_name
AAAR5pAAFAAAADPAAQ  73321          5    207     16         116 first_name
AAAR5pAAFAAAADPAAR  73321          5    207     17         117 first_name
AAAR5pAAFAAAADPAAS  73321          5    207     18         118 first_name
AAAR5pAAFAAAADPAAT  73321          5    207     19         119 first_name

6 rows selected.

sys@ORCL>select * from dba_objects where object_id = 73321;

no rows selected

sys@ORCL>

I found the answer of my own question. The so called object number from DBMS_ROWID.Rowid_object corresponds to dba_objects.data_object_id, not object_id.

sys@ORCL>select owner, object_name, object_id, data_object_id from dba_objects where data_object_id = 73321;

OWNER    OBJECT_NAME OBJECT_ID  DATA_OBJECT_ID
-------- ----------- ---------- --------------
HR       EMPLOYEES   73953      73321

sys@ORCL>


DBeaver 5.3.2 reconnect after db change (CREATE TABLE call) SQL scripts work on the active object in DBeaver. If you add a new table to your active database via an SQL statement in a script, DBeaver does not automatically update the navigator entries. Even when chosing to reconnnect to the database, the navigator stays not updated. To see the changes, the user has to disconnect and then connect to the database server again. Additionally, the active database changes in my case everytime I connect to it. I use postgresql with two databases: one custom database I change with scripts and the "postgres" database, which always becomes the active object by default.

Do you have the same obstacles, when creating new tables, or is there a way to work without the need to close and start the database connection?

Is there a way to attach a script to a special database without having to set one database to be the active one by hand?

Did you notice the "Refresh" button, usually at the bottom right of DBeaver screen?

Also accessible with F5 or from right-click menus from the database navigator:


Export roles *with* privileges under PostgreSQL 9.3 New to backups under PostgreSQL. I've been backing up my database with the general commands, and it works well. Except no users or privileges are stored with it. I understand that I need to do that separately.

I've been using the following command to dump the users on the cluster:

pg_dumpall -U backups -r > /tmp/globals.only.dump

Either the -r or the -g return the same thing. What I end up with in the file is just:

CREATE ROLE backups;
ALTER ROLE backups WITH SUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD '0000000000000000000000000000000000';

It seems that all the privileges are missing, but there are 15 of them that I had setup through NaviCat.

Recreating all the privileges by hand is obviously not viable for production. How do you force the privileges to be exported with the role? Subsequently, how might I import them into another PostgreSQL server?

Documentation says: "Database roles are global across a database cluster installation (and not per individual database)." Hovewer, privileges are object based, therefore their definition is stored in databases separately. If you backup your database with a command like; "pg_dump .. --schema-only" you will see the privileges in there.


Is it always bad to have an own table for value objects? I know that value objects should be embedded in the table of the object they belong to but what if the value object is more than a simple object and contains many fields/columns or even sub-objects?

I currently have a table named content which in short stores rich text. Since I don't always parse the content I've added a caching system. The table also has a 1:n-relation to another table which contains a list of all the links in the content table.

content(content_id[PK], text)
content_link(link_id[PK], content_id[FK], object_id)

If I would put this data in the orginal table I would follow the rules of database design but many it would make work harder.

Normalization is a set of techniques to avoid certain problems viz data update anomolies.  In a perfect world you would not store structured content but split it into its constituent parts across however many table are required and re-construct each complex object at runtime (1st normal form).

However .. if pre-parsing the object and storing the interesting / frequently used bits somewhere else resolves a performance issue then do this as long as you do it with your eyes open.  If you, and everyone else who may code against the database, understands that extra work must be done in the application in order to achieve the necessary response times then that's a design choice and necessary in the real world.

Response to comment:

My reading of your question is that content.text contains structured text in which links are embedded.  Your application shreds content.text from time to time to find and use these links.  This is expensive and as a performance optimisation you would like to store the links separately in content_link.link_id.

This is where the update anomalies can creep in.  If content is updated so that content.text has a different set of links, but content_link is not also updated then there is inconsistent data in the database.  It works the other way, too - a row can be deleted from content_link without content.text changing and, again, the data is inconsistent.  This is why the application has to be diligently coded and tested.

There is another rare-but-possible case to consider, too.  Changing the data will require two statements - update content and update content_link.  RDBM systems use pre-emptive multitasking.  This means that at any time the RDBMS may choose to halt one workload and run another instead.  So it could, for example, halt the updating stream after the first update statement and before the second.  If there is another workload which is trying to read content or content_link at that point it may run, and get inconsistent values, if the reading transaction's isolation level allows it to do so.  As I said, rare but possible.

If these problems are acceptably unlikely in your application and the implications of it happening are not severe enough to worry about then denormalisation can serve performance advantages.  If the risk of data inconsistency is too great then you'll have to find other solutions.