MySQL: Indexing by column which is also a part of a compound primary keys and I have a table which has a compound primary key, but I would also like to query the table using one or more of the columns composing this primary key. For example:

CREATE TABLE `user_visit_stats` (
  `hour` datetime NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `page_id` int(10) unsigned NOT NULL,
  `visits` int(10) unsigned NOT NULL,
  `avg_time` float unsigned NOT NULL
  PRIMARY KEY  (`hour`,`user_id`,`page_id`)
)

So the primary key is composed of hour, user_id and page_id. I would like to be able to efficiently query the table on different combinations of these columns - for example to get the total visits for a page in a day or the total pages visited by a user in a week.

Does it make sense to define additional indexes on hour, user_id and page_id or will these indexes be redundant?

visits for a page for a period of time:  INDEX(page_id, hour)

visits for a period of time:  INDEX(hour)

Your primary key, plus these, are not redundant; have them all.

Recommend you keep sum_time, not avg_time, then compute the avg as SUM(sum_time) / SUM(visits).  (The average of averages is not mathematically correct.)

The primary key is always indexed. This is the same for MyISAM and InnoDB, and is generally true for all storage engines that at all supports indices.

Refer link


Is there a way to extract a report of or script all of the primary keys in a database? I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of the schema still has primary keys, and I would like to generate a script that will add those keys to any deployed databases without them.

The best I can hope for is to just do something to script all primary keys, but I will be happy to also just extract the names of the columns that make up these keys and generate my own script.

SELECT *
FROM
 (SELECT obj.name + '.' +col.name as colName, idx.name, idx.is_unique, idx.is_primary_key, idx.is_unique_constraint
  FROM
    sys.indexes idx INNER JOIN
    sys.objects obj ON idx.object_id = obj.object_id INNER JOIN
    sys.index_columns idxcol ON idxcol.index_id = idx.index_id AND idxcol.object_id = idx.object_id INNER JOIN
    sys.columns col ON col.column_id=idxcol.column_id AND col.object_id = idxcol.object_id
  WHERE obj.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')) a
WHERE a.is_primary_key = 1

i did find this resource when looking for how to export keys.

https://www.mssqltips.com/sqlservertip/3443/script-all-primary-keys-unique-constraints-and-foreign-keys-in-a-sql-server-database-using-tsql/


performance of multi field primary key or 'contrived' 'semi artificial' key This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can easily be found and decisions made by whoever would like to search for them.

This question is more about performance of the keys (or lack thereof)

I work as a DB manager, and when I create my tables I attempt to use a 'natural' key for the table. Often times this comes out as being a set of 2,3, or sometimes 4 fields that act as the primary key for the given table. More often than not these fields are Varchar in nature, but short (10 or 15 characters in length at most). Personally I try to keep them shorter!

My question is this.

Imagine I have a table that holds demographic data.
The only way I can ensure that I have uniqueness in each row is to use the fields for
FirstName FamilyName DateOfBirth PlaceOfBirth

(You may wonder why I include 'place of birth', I am aware of another individual (who used to live nearby - same phone number, different dialing code) with whom I shared all these details (I assume that the PlaceOfBirth was different, but I guess I could have used MothersMaidenName ;) )

so now I have an interesting problem.

I could use a much shorter field that is created from concatenating the info in the 4 main fields
example: DateOfBirth first 2 characters of FirstName first 2 characters of FamilyName first 2 character of PlaceOfBirth

My question is this.

At what point would the concatenation of field provide a performance improvement over using the fields directly, ie how many columns.

I know from searching that most DBMS's have a 'thoretical maximum size limit' dependent on the B-Tree that is created. I am assuming that I don't hit this limit in terms of the length/size of the primary key.

My reason for considering using this type of 'contrived' key is:
The information in the concatenated column is most likely sufficient to be able to identify the record without the need to extract all the primary key fields (would this be better for performance or no different compared to using all the 4 primary key fields?)

This is obviously a fairly 'theoretical' question, but I have considered doing this concatenation on a table that end up with 4 varchar fields, and it was obvious that uniqueness would be described by using just a shortened version. Obviously there is an effort to create this field in the first place, but in others opions would this effort be worthwhile, and at what point would it become more interesting.

I have searched for this but I have never found this question asked directly, it allays comes out as a 'natural' or 'artificial' primary key discussion.

Of course if this feels like a 'natural' or 'artificial' key discussion feel free to say so. My feeling is that this 'contrived' key would proffer the advantages of a both. Has anyone used this idea in a real world solution?

Thanks in advance for your thoughts.

David

Edit.
I just found this thread

https://stackoverflow.com/questions/3735390/best-primary-key-for-storing-urls

It seems to cover similar ground, I must admit I hadn't thought of 'hashing' my columns together (mainly because they are short by nature), but I do like the idea. I guess you could do that and hash the whole row !

Edit2.

I've returned to this question just to see if there has been any change to the answers, or extra comments. I've decided to accept a response, but would like to note that I have found all the responses helpfull in the terms of the discussion.

I'll answer obliquely...

The natural key is always the natural key and should be enforced with a unique constraint or index. This is the "primary key" that flows from your modelling phase.

The choice of an auto-number/identity surrogate key matters at implementation phase because there are good and bad choices for your clustered index (example: SQL Server, Sybase, MySQL InnoDB, Oracle IOT).

That is, primary key is orthogonal to your clustered index: don't confuse the two issues

I'd suggest using a contrived key adds no value over using an auto-number/identity column in this respect. You lose data from the natural key, probably won't be unique, is just as opaque.

FWIW, I use surrogate keys and composite keys when I need too:

Some natural keys are useful in their own right: ISO currency and country codes
A table with no secondary (non-clustered) indexes and no child table doesn't benefit from a surrogate key
If you have parent-child-grandchild, then I usually need to join parent-grandchild: with composite keys I can do so directly. Simpler JOINs, simpler indexes

Note: this assumes that every table requires a clustered index

Related on dba.se: SQL Server Primary key / clustered index design decision

It would be truly awful database architecture to use Compound Keys or to use a concatenated compound key as you propose.  For compound keys, any tables with a foreign key reference to your demographic data would also require columns to point to FirstName FamilyName DateOfBirth PlaceOfBirth.

Concatenating the data into one column is a terrible idea - you're going to be using VARCHAR(~256) for your Primary Key and Foreign Key references.  This will make your indexes huge and performance will suffer.  You'll also need to parse and join to get the actual data - this is error prone as Kevin Andersen New York is not the same as New York Kevin Andersen.

You should be using a surrogate key - a key that has no context in your business model (long/bigint or a GUID).

Look at Facebook's data model:

https://graph.facebook.com/cocacola

notice the ID is a surrogate key represented by a number which has no context in the data - 40796308305


SSIS OLE DB Error on package execution Good day,
I have an SSIS package that runs on scheduled job for a while now. all was okay, until very recently, i started receiving some failed job executions. When I investigated it, I found the following errors:

Error: 0xC0202009 at PACKAGE_NAME, Source - Query [127]: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Communication link failure".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "TCP Provider: A connection attempt failed because
the connected party did not properly respond after a period of time, or established
connection failed because connected host has failed to respond.".

Error: 0xC0047038 at PACKAGE_NAME, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on Source - Query returned error code 0xC0202009.
The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and
the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.

Can anyone help?

The semaphore timeout period has expired" is a Network error

Network issues between the source server and the server running SSIS?

A look in the Windows System logs of both machines might provide more information about any network issues. You will probably also find the specific windows error number, which will help in your research.

Locking/blocking ?

Using the dmvs dm_exec_connections, dm_exec_sessions and dm_exec_requests you can see if another query is blocking access to a resource your SSIS package is requesting from the source server.

select es.session_id, er.blocking_session_id, *
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id

Is the query too slow because of full table scans or lack of indexes?
Remote Query Timeout: Is the remote query timeout set on the source server set?

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

At 0 the default value, there is no timeout.

I've recently discovered the same problem.  It seems to be a problem when using Native Client 10.0 (even in SQL2008 R2 SP3).  Install a later Native client, eg. 11.  Or use the older "Microsoft OLE DB Provider for SQL Server".


How do I set a geography point to be the North Pole? I tried to execute this query on my SQL Server 2008 R2 instance:

UPDATE ITEMS
SET GEOG=geography::STPointFromText('POINT(0 90)', 4326);

SQL Server returned the following error:

System.FormatException: 24201: Latitude values must be between -90 and 90 degrees

I also tried POINT(0 89.99) and got the same error.

How do I set a point to be the North Pole?

Try this instead, with (or without) decimal points

DECLARE @a geography
SET @a=geography::STPointFromText('POINT(0.0 90.0)', 4326)

if this works - then something wrong with the ITEMS table

Solved. the problem was raised by a trigger enabled on the table.
the problem is that the trigger contain exactly the same code to update the GEOG field when GEO_LAT and LON field are updated... really strange but for now the problem is solved also without 0.0 and 90.0


After modifying the throughput of a DynamoDB table, where can I see the new throughput value before it gets active? After modifying the throughput of a table in DynamoDB, the "Modify Throughput" is greyed out:

Where can I see the new throughput value before it gets active? Sometimes it takes a while for a throughput to get applied, especially for large values, and I would like to check whether the person (myself or colleague) who changed the throughput didn't make a mistake.

I came across similar problem. So thinking to make some sort of series of describe table

DescribeTableResult describeTableResult = client.describeTable('tableName')

And put it in while loop until settings are applied. That could give rough idea how much time does it take to apply settings.


How to migrate data between "all-surrogate" databases? With some periodicity this needs to be done:

Inserting data from a production database into a test/development database, so that programmers have fresh data to test on.
Migrating data from a database into another, for whatever reason.

Note: databases are relational.

The problem is:

Databases were modeled with a "all-PK-are-surrogate" policy.
Moving data from a database to another involves programming to avoid colision of PK values ( generated by sequences ).
A table pairing the values of the PKs of origin and destination database's tables is needed in order to do the "equivalency" between surrogate keys.
Said table has to be created in advance of migration, by matching business keys (which are not PK, so don't govern FKs)
Data migration even of a single table in not trivial, as opossed to tables with natural keys.

What's the easiest way of copying rows from one database to another when all tables have surrogates keys ?

We came across this problem (or at least a similar one) not long ago at work, and our solution is to divide the migration process into steps:

Insert the new data to a temporary staging table(s)
Update the surrogate keys in the staging table(s) so that there are no conflicts
Insert the data into the target table(s)
Delete the staging table(s)

The second part is the tricky part, but in our case we were able to follow this algorithm for each table:

If there are any foreign keys against this table, set up foreign keys in the staging table with ON UPDATE CASCADE
Update the primary keys of all rows to a new id range. We used negative ids. E.g. UPDATE STAGINGTABLE SET ID=-ID-1; Now there is no risk of id clashes in the next step.
Now, for each row in STAGINGTABLE, generate and assign a new id

Repeat for each staging table. When all staging tables are processed, INSERT the data into the target tables.

One assumption this relies on is that all data is new and independent from the data already in the database.

Alternative approach if you do not need to disguise the data for privacy reasons. All database changes and values for lookup tables should be in source control. We simply restore the last prod backup and then use source control scripts to add whatever dev changes haven't made it to Prod yet.  If you have multiple databases, all should be refreshed at the same time.  One advantage of this is that you have roughly the same number of records as prod which makes a huge differnce in testing your database code for performance. It also helps keep your devs using source control for changes and helps practice deployments before you move them to other environments.


Hash Match inner join in simple query with in statement I am running the execution plan for the following query:

select m_uid from EmpTaxAudit
where clientid = 91682
and  empuid = 42100176452603
and newvalue in('Deleted','DB-Deleted','Added')

Here is the execution plan:

I have a non clustered index on EmpTaxAudit Table on ClientId and NewValue columns which shows above as 14.9% of the execution:

CREATE NONCLUSTERED INDEX [idx_EmpTaxAudit_clientid_newvalue] ON [dbo].

[EmpTaxAudit]
(
    [ClientID] ASC,
    [NewValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I also have a non clustered unique index PK as follows:

ALTER TABLE [dbo].[EmpTaxAudit] ADD  CONSTRAINT [PK_EmpTaxAudit] PRIMARY KEY NONCLUSTERED
(
    [ClientID] ASC,
    [EmpUID] ASC,
    [m_uid] ASC,
    [m_eff_start_date] ASC,
    [ReplacedOn] ASC,
    [ColumnName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Trigger code in source table EmpTax:

CREATE                trigger [dbo].[trins_EmpTax]
on [dbo].[emptax]
for insert
as
begin
  declare
    @intRowCount  int,
      @user          varchar(30)

  select @intRowCount = @@RowCount
  IF @intRowCount > 0
  begin
     select @user = suser_sname()

     insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
     select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Added'
        from inserted i
         where m_uid not in (select m_uid from EmpTaxAudit
                    where clientid = i.clientid  and (newvalue = 'Deleted'
            or newvalue = 'DB-Deleted'
                        or newvalue = 'Added') and  empuid = i.empuid)
       and i.m_eff_end_date is null

     insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
     select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Deleted'
        from inserted i
         where m_uid not in (select m_uid from EmpTaxAudit
                    where clientid = i.clientid  and (newvalue = 'Deleted'
            or newvalue = 'DB-Deleted'
                        or newvalue = 'Added') and  empuid = i.empuid)
       and i.m_eff_end_date is not null

  end
end

What can I do to avoid the high cost of Hash Match (Inner Join)?

Thanks!

For the 1st query, an index that uses all three columns from the WHERE clause and includes the column from the SELECT list would be much more useful:

-- index suggestion A
(clientid, empuid, newvalue) INCLUDE (m_uid)

or an index targeted specifically for this query:

-- index suggestion B
(clientid, empuid, m_uid)
WHERE newvalue in ('Deleted', 'DB-Deleted', 'Added')

Regarding the trigger, some comments:

The first query you show does not appear in the trigger. What appears is a join from that table to the inserted rows to another table (which has the trigger).
My suggestion B above seems better suited to be used by the trigger.
The trigger has 2 almost identical insert statements. Why? I think they could be combined in one - and simpler - insert and using NOT EXISTS instead of NOT IN:

insert EmpTaxAudit
  ( Clientid, empuid, m_uid, m_eff_start_date, ColumnName,
    ReplacedOn, ReplacedBy, OldValue, dblogin,
    newvalue
  )
select
    Clientid, empuid, m_uid, m_eff_start_date, 'taxcode',
    getdate(), IsNull(userid,@user), '', Left(@user,15),
    case when m_eff_end_date is null
        then 'Added' else 'Deleted'
    end
from inserted i
where not exists
      ( select 1
        from EmpTaxAudit
        where m_uid = i.m_uid
          and clientid = i.clientid
          and empuid = i.empuid
          and newvalue in ('Deleted', 'DB-Deleted', 'Added')
      ) ;

First critique: I don't think a PRIMARY KEY should consist of so many fields.  A primary key is just meant to be a simple identifier of uniqueness.  Arguably you could have two fields in your PK for many-to-many intersection tables, though I personally always have a single-field primary key, even for these.  So I'd say your PK PK_EmpTaxAudit really should be redefined as a unique index.

Then I would suggest adding newvalue to the INCLUDE fields of that index:

create unique index IX_EmpTaxAudit(
  [ClientID] ASC,
  [EmpUID] ASC,
  [m_uid] ASC,
  [m_eff_start_date] ASC,
  [ReplacedOn] ASC,
  [ColumnName] ASC
)
include (newvalue)

I suspect that will give you a neater execution plan.


The specified network name is no longer available We have an application that access the database (Sql server 2014 enterprise edition). The application calls a stored procedures to access the database. Everything was working fine until recently start sending the following error and stop the applications. Restarting the app fix the issue temporarily but facing the same error latter.

  Error: A transport-level error has occurred when receiving results from the server. (Provider: TCP Provider, error: 0 - The specified network name is no longer available.)

I did a lot of research which most of them pointing out as a network issue but could not find anything to actually solve the problem.  Do anyone know what change I should have to make on the database side to resolve this issue.  I highly appreciate any suggestions.

Sounds like spotty DNS resolution issues or long running queries.
For DNS trouble-shooting
As a Band-Aid and trouble-shooting aid add and entry to the hosts file on your app servers (not the SQL Server) at c:\windows\system32\drivers\etc
SQLServerIPAddress SQLServerName

Example:
172.16.0.5 ProductionSQLBox

That way, the SQL Server name will be resolved by the hosts file until you can find the real problem for what's going on with name resolution.
Be sure and test the hosts file by pinging the SQL box from the app server via a command line. Or, alternately create a DSN with the ODBC Administrator within Control Panel and test the connection there.
For long running queries
Implement a longer command timeout value for the application connection strings connecting to the SQL Server.
Example
<connectionStrings>
    <add name="webconfigconnectionString" connectionString="server=SQLServerName;database=dbName;uid=userName;password=ABC123;Connect Timeout=120" />
</connectionStrings>

Moved to hotspot and it's fixed for me - I'm no expert, but I'd say it has something to do with the internet connection / connectivity settings / router etc.


Limiting query to certain amount of rows evenly distributed Given a table:

id
--
1
2
4
6
8
10

I would like to be able to specify a limit and have that amount of rows returned evenly distributed across the entire set when it is ordered by a specific criteria. For example, 3 rows when the set is ordered by id would give me:

id
--
1
4
10

In the above scenario, either 4 or 6 could have been returned as the middle row. I am not specifically bothered as to which way the selection is rounded.

Ideally I'd like to do this in the most efficient way i.e. the entire table isn't parsed initially.

I found an excellent post here Select every n-th row, don't pull the entire table, regarding evenly distributed data sets but struggled when trying to calculate n in order to limit the final data set to a specific amount.

I'm currently running MySQL 5.6 on Ubuntu.

Ids are non sequential.
This database is dynamic so I can't hardcode the amount of rows or have select statements that reflect errors in a particular set of results.

You have a dynamic number of rows in the table, and want a specific number of evenly distributed results each time?

I'm asking because the solution you linked is already nearly there:

   SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, [column name]
    FROM (
        SELECT @row :=0) r, [table name]
    ) ranked
WHERE rownum % [n] = 1

Just replace [n] with the following:

(CEIL(@row/[m]))

where [m] is the number of results you want to have returned.


Where all can the /*+ PARALLEL */ hint be used? It works with SELECT and UPDATE statements.

Are there some more statements with which it works?

I was able to use it with create, but am not sure if the hint was actually considered.

SQL> create /*+ PARALLEL */ table t1
( id number,
name varchar2(40));

Table created.

SQL>

Except INSERT and UPDATE, when all is the PARALLEL hint considered?

The Data Warehousing Guide has a chapter on parallel operations that goes over what operations can be parallelized.

You can't parallel-ize a simple CREATE TABLE statement (nor would you want to-- there wouldn't be enough work to be able to parcel it out to multiple worker threads).  You can, however, parallel-ize a CREATE TABLE AS SELECT statement either by specifying the PARALLEL hint in the SELECT or by using the PARALLEL keyword in the CREATE.

Oracle supports parallel processing for a wide range of operations, including queries, DDL, and DML:

Queries that involve table or index range scans
Bulk insert, update, or delete operations
Table and index creation
The collection of object statistics using DBMS_STATS
Backup and recovery operations using Recovery Manager (RMAN)


Best solution to fixing database design with GUID as primary key I am after some confirmation of this idea to fix a badly performing database or a better suggestion if any one has one. Always open to better suggestions.

I have a very large database (20+ million records growing by about 1/2 million per day) which are using GUID as PK.

An oversight on my part but the PK is clustered on SQL server and is causing performance issues.

The reason for a guid - this database is partially synchronised with 150 other databases so the PK needed to be unique. The synchronisation is not managed by SQL Server, rather there is a custom process built which keeps the data in sync for the requirements of the system - all based on that GUID.

Each of the 150 remote databases don't store the full data as stored in the central SQL Database. they only store a subset of the data they actually require, and the data the require is not unique to them (10 out of the 150 database may have some of the same records from other sites databases for example - they share). Also - the data is actually generated at the remote sites - not at the central point - hence the need for the GUIDs.

The central database is used not only for keeping everything in sync, but queries from 3000+ users will be executed against that very large fragmented database. Already this is a big problem in initial testing.

Fortunately we are not live yet - so I can make changes and take things offline if required which is at least something.

The performance of the remote databases is not a problem - the data subsets are pretty small and the database usually never gets above 1GB in size in total. The records are fed back to the main system quite regularly and removed from the smaller BD's when no longer required.

The performance of the central DB which is the keeper of all records is woeful - due to a clustered GUID as a primary key for that many records. The index fragmentation is off the charts.

So - my thoughts to fix the performance issue is to Create a new column - Unsigned BIGINT IDENTITY(1,1) and then change the Clustered PK of the table BIGINT column.

I would create a Unique Non Clustered index on the GUID field which was the primary key.

The smaller remote 150 databases don't need to know about the new PK on the Central SQL Server database - its purely going to be used for organising the data in the database and stop the bad performance and fragmentation.

Would this work and improve the performance of the central SQL database and prevent future index fragmentation hell (to a degree of course)? or have I missed something very important here which is going to jump up and bite me and cause even more grief?

You certainly need NOT to cluster on the GUID.  If you have something which would allow you to uniquely identify records other than that GUID, I'd suggest you look at building an unique index on that other field & making that index clustered.  If not, you are free to cluster on other fields, even using nonunique indexes.  The approach there would be to cluster however best facilitates splitting your data up and querying - so, if you've got a "region" field, or something, that might be a candidate for your clustering scheme.

The problem with changing to a BIGINT would be additions to the data from other databases & integrating their database into the central store.  If this is not a consideration - and will never be a consideration - then, yes, the BIGINT would solve the index rebalancing issue nicely.

Behind the scenes, if you don't specify a clustered index, SQL Server does much the same thing: it creates a row ID field & maps all other indexes into that.  So, by doing it yourself, you're solving it just as SQL would solve it.

If GUID is stored properly as uniqueidentifier should not have any performace issues... and if you can use Sequential GUID even better...

Also @mattytommo has Good point about 11.5 years with using INT...


Why are NoSQL databases not ACID compliant? Not having ACID properties means that the database works well on clusters. But ACID is something very fundamental. How can a database work well if there is no atomicity, consistency, isolation and durability (ACID)?

How can a database work well if there is no atomicity, consistency, isolation and durability?

They can't. This is why those features were created.

Sometimes you can work around the lack of these features because you need to scale horizontally or scaling becomes your first and primary concern. You may not need need consistency now, you may be able to handle it later.. You may not need all the data returned in order: you may want the queries executed in parallel over clusters. Or your data may itself be fairly un-valuable.

Look at major products that implement these types of systems though -- generally, they suck and they have lots of errors. Ever post something to your wall on Facebook just to see it disappear and reappear multiple times? Or to have a conversation you're commenting on vanish for an extended period of time and come back? That's hundreds of people working to manage data using "NoSQL" and compiling PHP to C++. It doesn't especially work well. It works, and for most companies that's further than they can get developing an alternative.

Before people see the word "cluster" and think that this must be the next "New Thing" and therefore it must be "A Good Thing (tm))", just take a read of Ted Dziuba's article here. The title might give you some clue as to his take on NoSQL databases.

NoSQL databases are not ACID, they are BASE (*)! Basically Avaiable, Soft state, Eventually consistent! What this means is that, if say Amazon used this, you would always be able to "purchase" a book from them with no guarantee as to whether there was stock available to fullfill your order! They are an attempt to "beat" Brewer's theorem! Well, guess what Jim? "Ye cannae beat the laws 'ah physics Jim!" (with apologies to Gene Roddenberry :-)).

Michael Stonebraker really is a man to listen to when it comes to database technology, being successful both on the academic and commerical fronts.

He agrees with the fundamental premise of NoSQL that what he terms OldSQL is broken! He just argues (forcibly) that throwing out ACID guarantees is literally throwing the baby out with the bathwater.

What he argues for is ACID compliant databases without all the locking and transactional overhead of systems like Oracle, MS SQL Server, PostgreSQL and MySQL inter alia. He also suggests that they should be sent to the "home for retired software" :-)!

How, you may ask? Well, two ways:

His point about NewSQL is that for OLTP apps, you need a shared-nothing sharded architecture (check out his VoltDB - based on HStore) and that for OLAP you need dedicated columnar stores, i.e. Vertica (based on CStore, which he sold to HP).

To answer the actual question directly: "Why are nosql databases not acid compliant" is because they weren't designed to be. ACID is a lot of work and requries lots of resources in the machine, but even Google are being forced to go back to ACID and SQL!

NoSQL is an attempt to wriggle out of fundamental constraints and, as a result, will fail. That is not to say that it won't have a niche in certain areas, but it will always be just that, niche!


Estimate query speed for +1.5 billion rows mysql table I'm working on audio fingerprinting problem where I need to
query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to 100K rows in total, using multiple queries (between 20 and 50 queries).
The table has 3 columns, a hash and two int values. No constraints whatsoever. The hash column has a lot of collisions/duplicates. Here is the output for show create table
CREATE TABLE `fingerprints` (
  `hash` binary(10) NOT NULL,
  `int1` mediumint(8) unsigned NOT NULL,
  `int2` mediumint(8) unsigned NOT NULL,
  KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The query is simple, here is an example:
select int1 ,int2 from fingerprints where hash in (UNHEX("1ff99335cce004f2765d"),UNHEX("14c4b93ed575982ed2e4"),UNHEX("41044b0cf21dc8ac8f9b"),UNHEX("a791403ca116b4da53dd"),UNHEX("d9f91514b900c25fa095"),UNHEX("3349f906deae6cd32883"),UNHEX("221c0e3e2bc243fb0fe5").... more here);

I've tried different hardware specs (using AWS with only one machine/instance). Different my.cnf configurations but no significant performance boost.
the target speed threshold for this operation (total queries time) is 5 sec. But the best I've got in average is 3 sec for only one single query (if I have 20 queries, the total operation time is 1 minute).
final note: when profiling the query, SHOW profile command shows that the slowest part was (SENDING DATA) state. The query becomes slower when the result set is larger (i.e retrieving 10k rows takes about 6 sec, while retrieving 1000 rows takes 2 sec)
Questions:

What is the speed estimation for such query scenario for an SSD machine with enough RAM to hold indices. I have no experience working
at this scale.
Do you have recommendation for specific db setup? should I try mysql Memory engine? partitioning here is a necessity with distributed
machines? should I switch to innodb?

my setup:

read only myisam table compressed with myisampack and indexed on the where (hash) column.
the index table (MYI file) is fully loaded to RAM
SSD hard disk with limited iops (amazon AWS). According to AWS graphs i'm hitting 700 Iops sometimes.

Edit:
SHOW INDEX output:
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| fingerprints |          1 | hash     |            1 | hash        | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

EXPLAIN QUERY output (for the example query)

+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | fingerprints | NULL       | range | hash          | hash | 10      | NULL | 4912 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

(UNHEXing is not a significant part of the problem.)

The real problem is the randomness of hashes.  The leads to jumping around lots of places on disk.  Let's dissect the query.

The IN list is a list of values scattered throughout the INDEX(hash).
Each value is looked up by drilling down a BTree (found in .MYI file) that is cached in MyISAM's key_buffer.  What is the value of key_buffer_size?  What is the result of `SHOW TABLE STATUS LIKE 'fingerprints' \G ?
If the index_size is greater than key_buffer_size, then many of the lookups will hit the disk.
At the leaf node of each BTree lookup will be a 5-byte (I think) "record number".
Now to lookup the row -- This will be a random disk access (a seek, no BTree) into the fingerprints.MYD at offset = 17 * record_number.  (The records appear to be FIXED length of 17 bytes.)
Again, we are looking at a probable disk hit -- now assuming that the remaining free space on disk is less than the Data_length (see the TABLE STATUS).

What to do?

Case 1:  Data_length + Index_length < RAM size:  Have key_buffer_size a little bigger than Index_length.  Gradually both caches will fill with index and data, and the I/O will go away.

Case 2:  That sum is slightly bigger than RAM:  Pick one of the caches to make big enough.

Case 3:  The sum is a lot bigger than RAM:  You are stuck with lots of I/O until you get more RAM.

I suspect the Data_length and Index_length are about equal.  I would split available RAM in half -- half for key_buffer_size, the rest for data caching.

Here are 2 more ideas:

Rather than fetching the ints in a second step, have KEY(hash, int1, int2)  This means that only the BTree lookup is needed; the data will sitting in the leaf node.  With this approach, you could set key_buffer_size to 'most' of available RAM.  That SELECT won't touch the data, only the index.
Switch to InnoDB.  It's blocks are 16KB, not 1KB.  This may make things faster.  But the disk footprint will be 2-3 times as much.  Again, use the 3-column index, but shrink key_buffer_size to 20M and raise innodb_buffer_pool_size to 70% of RAM.

Other Notes:

"Sending data" does not tell you anything.  Profiling, in general, is useless.
SSDs run a lot faster than HDDs.
You appear to be I/O bound.
Whether you are I/O-bound or not, the total query time is roughly proportional to the number of hashes being looked up.  (This can be deduced from my dissection.)
MEMORY is not likely to be significantly faster or slower than MyISAM.  And if your data needs to persist, then there is a hassle because MEMORY is volatile.
I predict that compression is useless since you have only 6 bytes to compress.  (And the hash, itself, is probably not compressible.)
If your provider is limiting the IOPs, that is a problem.  If your index is fully cached (and not so big that it is unnecessarily eating RAM), then the IOPs are fetches of data blocks.  The 3-byte key would be about 70% larger; will a large enough key_buffer fit in RAM?  If so, that approach might be optimal.

When your query includes,

where hash in (UNHEX("1ff99335cce004f2765d"),UNHEX("14...............

will be forever limited to being slow.  Every ROW has to be 'unhex'ed' to determine relevance for this query.  In your case, several UNHEX operations per row retrieved.


How to make a PostgreSQL trigger thread-safe I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't mess things up), is it possible to do it with advisory lock like this?

BEGIN
    SELECT pg_advisory_xact_lock(1);
    -- do something thread-safely here
    ...
END;

According to this post, all triggers execute as part of the same transaction as the original query, so you would not need a lock statement:
https://stackoverflow.com/questions/10727335/after-insert-trigger-in-separate-transaction


Is it possible to Clone a RedShift Database? For some test and development work we would like the ability to routinely clone existing RedShift databases (all schema and all data).  Something like a backup-restore.

The best I can find starts by creating a snapshot of the entire cluster, which is not practical at this point in time.

We do have scripts for creating the database and the schema, but we don't have a simple/tidy mechanism for populating the tables from another AWS RedShfit database.  (We've found that we can't use INSERT INTO db_new.schema.table SELECT * FROM db_old.schema.table as cross-database syntax is not supported)

Does anyone have any suggestions?

You can do the following:

UNLOAD your data to S3 (http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html)
Run your deployment scripts against the new database
COPY the data into the new database (http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html)

This (UNLOAD/COPY) will be fairly quick for a development size dataset.

I like the idea of capturing the db state as code, but i think there is a easier way in the redshift context of backup snapshots and restore from those.
This AWS doc for copying a db to another AWS account is a guide, you can do the same within the account or this what you would do in an emergency restore from snapshot.

Take a manual snapshot via the console
Restore a redshift cluster from the snapshot.

Now your schema and data are there, the next task is slimming this db down as a test environment and resizing the cluster down. Theres also dns endpoints and other configs to clean up, mentioned in the doc.
The slim-down step is the part interested in, maybe someone else will comment on that.


Can I have Oracle primary 11.2.0.1 and standby 11.2.0.3? Can I have data guard system like:

Primary: 11.2.0.1
Standby: 11.2.0.3

In logical standby ?

In physical standby versions(releases) must be the same?

The physical standby database must be physically the same as the primary database and this means the versions must be identical.

A logical standby database can be any version, depending on how it is implemented. With 3rd party tools you can go from v10 to v12 and back. The restrictions here are on the data types that are to be replicated.

If this is for an upgrade, you start with a physical standby database, convert that to a logical standby for the upgrade.

Sometimes it is better to read before making advice.
https://docs.oracle.com/cd/E18283_01/server.112/e17022/upgrades.htm#CIHJIICA

B.4 Modifying the COMPATIBLE Initialization Parameter After Upgrading

When you upgrade to a new release of Oracle Database, certain new features might make your database incompatible with your previous release. Oracle Database enables you to control the compatibility of your database with the COMPATIBLE initialization parameter.

After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for the new Oracle Database release. When you are certain that you no longer need the ability to downgrade your database back to its original version, set the COMPATIBLE initialization parameter based on the compatibility level you want for your new database.

In a Data Guard configuration, if you decide to increase the setting of the COMPATIBLE initialization parameter after upgrading, then it is important that you perform the following steps in the order shown (note that the standby database should have a COMPATIBLE setting equal to, or higher than, the primary):

Increase the value of the COMPATIBLE initialization parameter on all standby databases in the configuration first, as follows:

Ensure that apply is current on the standby database(s).

On one instance of each standby database, execute the following SQL statement:

ALTER SYSTEM SET COMPATIBLE= SCOPE=SPFILE;
If Redo Apply or SQL Apply is running, then stop them.

Restart all instances of the standby database(s).

If you previously stopped Redo Apply or SQL Apply, then restart them.

Increase the value of the COMPATIBLE initialization parameter on the primary database, as follows:

On one instance of the primary database, execute the following SQL statement:

ALTER SYSTEM SET COMPATIBLE= SCOPE=SPFILE;
Restart all instances of the primary database.


Is it possible to shrink an .MDF file on a drive with low free space? Is there a way to shrink mdf file of database in SQL Server 2008 without using additional space?

Total space in hard disk is 136 GB, the .MDF file size is 124 GB, and the log file size is 2 GB.  I have only 12 GB free space and while running the shrink command on the  .MDF file, logs are growing and consume the free space -- low disk spaces throws the shrinking into a no respond state.

Is there another method to shrink this file without using additional disk space?

To summarize the answers here and to inject my own advice:

1: Blindly shrinking a data file is not the best route. Your data is your data and it is characterized by this size. Unless you are planning on deleting large chunks of data, shrinking is not going to do much of anything. As @billinkc pointed out, simply shrinking the file will not give you any sort of appreciable gain.

2: Running this is going to make your log file grow. Apparently you have your data and log on the same drive. I'd recommend against this for many reasons including space and IO contention.

3: You can certainly create a new data file and put that on a different volume. You could "archive" old data to free space in the current .mdf, or you could leave the current data file as-is and it would become an archive, of sorts.

4: I'm guessing that there are other things on this volume as well. I would move those things as quickly as I could in order to free up space.

If this is enterprise data, I'd push for more disk. This would allow you to separate your data and log files. Additionally, outside of just deleting data, you're going to need more disk to implement Brandon's suggestion anyway.

The documentation for this is here:

http://msdn.microsoft.com/en-us/library/ms190488(v=sql.100).aspx

Give the link a thorough read.


Unix domain socket "/var/run/postgresql/.s.PGSQL.5432" missing I try to run an application where its database on postgresql. It won't work and I'm getting the following error whenever I try to interact with the Postgres service:

could not connect to server: Aucun fichier ou dossier de ce type
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432`

I checked if Postgresql service is running

sudo systemctl status postgresql

postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enab
   Active: active (exited) since Thu 2019-04-04 10:28:27 +01; 58min ago
 Main PID: 1212 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 3522)
   CGroup: /system.slice/postgresql.service

avril 04 10:28:27 ubuntu systemd[1]: Starting PostgreSQL RDBMS...
avril 04 10:28:27 ubuntu systemd[1]: Started PostgreSQL RDBMS.

checked the content of the socket unix connection

ls /var/run/postgresql/

got this empty folder

brahim@ubuntu:/var/log/postgresql$ ls /var/run/postgresql/
10-main.pg_stat_tmp

checked the content of the data directory

ls /var/lib/postgresql/10/main/

it contains the following

 base         pg_logical    pg_serial     pg_subtrans  pg_wal
 global       pg_multixact  pg_snapshots  pg_tblspc    pg_xact
 pg_commit_ts  pg_notify     pg_stat      pg_twophase
 postgresql.auto.conf pg_dynshmem   pg_replslot   pg_stat_tmp
 PG_VERSION   postmaster.opts

the file postgresql.conf has the following configuration

  # - Connection Settings -
  #listen_addresses = 'localhost'
  port = 5432
  max_connections = 100
  unix_socket_directories = '/var/run/postgresql'

I tried to uncomment the listen_addresses instruction and it didn't work, also I've tried with '*' for all, same thing

the postgresql file log has

2019-04-04 12:17:57.765 +01 [942] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-04-04 12:17:57.767 +01 [942] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-04-04 12:17:58.190 +01 [971] LOG:  database system was shut down at 2019-04-03 20:19:54 +01
2019-04-04 12:17:58.192 +01 [971] PANIC:  replication checkpoint has wrong magic 1886413116 instead of 307747550
2019-04-04 12:17:59.900 +01 [942] LOG:  startup process (PID 971) was terminated by signal 6: Aborted
2019-04-04 12:17:59.900 +01 [942] LOG:  aborting startup due to startup process failure
2019-04-04 12:17:59.902 +01 [942] LOG:  database system is shut down

I am running postgresql 10.6
any thoughts

Try passing the -a option to ls to also show files starting with the period . character (i.e. "hidden" files):

$ ls -a /var/run/postgresql/
.  ..  .s.PGSQL.5432  .s.PGSQL.5432.lock

Solución que me funcionó en Postgresql 11:

sudo pg_ctlcluster 11 main start

También revisar que tenga bien configurado  pg_hba.conf


How to know when a refresh of a materialized view has been completed After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results.

Now, my problem is that I need a way to know when the refresh is complete so that I can then query and be sure to obtain an up to date result.

The MV is specified in this way:

CREATE MATERIALIZED VIEW ASSAY
("TYPE","METHOD",COMPOUND_CORPORATE_ID,RESULTS_AVG,RESULTS_ST_DEV,RESULTS_N)
AS
select "TYPE","METHOD",AVG("RESULTS"), STDDEV("RESULTS") , COUNT("RESULTS")
from ASSAY_INHIB
where upper("VALIDATION")='YES'
group by "TYPE","METHOD",COMPOUND_CORPORATE_ID;
CREATE INDEX assays_inhib_parent ON ASSAY(compound_corporate_id);
ALTER MATERIALIZED VIEW ASSAY  REFRESH FORCE   ON DEMAND;

I call from the GUI a stored procedure with the command exec REFRESH_MV() and the stored procedure is like this :

CREATE  PROCEDURE REFRESH_MV () AS
BEGIN
    DBMS_MVIEW.REFRESH('ASSAY');
END REFRESH_MV;

The problem is that I don't know when I can query the MV and be sure that data are up to date. Can anybody help?

Thanks

You you have said-

  After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results...

You may use ON COMMIT refresh instead of ON DEMAND BUT I just said may be, not sure about your requirement and implementation.

  ON COMMIT Refresh

  A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view.

The following query can be used to know when the MV was last refreshed.

SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
SQL> select owner, mview_name, last_refresh_type, last_refresh_date
    from all_mviews;

Sample output:

USERNAME         M_VIEW_TEST        COMPLETE   02/02/17 01:01:19

You can also use v$mvrefresh dynamic performance view to know which MV is being refresh.

select currmvowner, currmvname from v$mvrefresh;

References:
ALL_MVIEWS
ON COMMIT Refresh

https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG8373 looks like what you need. Unfortunately I don't know enough to be more specific.

Update: I believe hot2use's answer may only apply for background updates, which I don't think your call will trigger. See https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG8369.
My guess is that the way you are calling the refresh will complete the request before exiting the procedure, but that depends on what "the foreground process" means in Oracle.


Is there a way to show the creation statement for an index in PostgreSQL I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new name and then drop the old one. Is there any way to see the SQL statement that was used to create an index so I can just copy that?

There is actually, just query the pg_indexes system catalog view as follows:

SELECT indexdef FROM pg_indexes WHERE indexname = '...'

and you should get back the SQL statement used to define it.

More simply if you want them all (all the indexes) ...

=# SELECT indexdef FROM pg_indexes;


Is dmp file suitable despite ORA-01555 'snapshop too old' warnings? Well, the title of the question is self-explanatory.

Export finished successfully with warnings.
Log shows several ORA-01555 snapshot too old errors.

- Is it OK to use that dmp file to perform an import full in another instance?

- Would running the export in restricted mode (no users connected) garantee no 'ORA-01555 snapshot too old' would occur?

Technically, you could, but it is incomplete, so better repeat the export after performing the necessary actions to avoid ORA-01555 errors (have an appropriately sized undo tablespace, set undo_retention and retention guarantee).

It would be time saving if you just backup the tables being errored in ORA-1555 after the warning full backups.
Yes, to answer main question,  it is good backup to be imported,  you can exclude the errored tables or use table_exists_action=replace using tables only backup after taking the tables backup in above para.

Scenario warning = if the errored tables  have any dependent primary key or foreign key referenced  constraints, then the other tables being referenced also needs to be backed up maintaining consistency to make sure constraints don't get invalid.


Get NULL statistics for a column in MS SQL Server In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column?

Same information is available in Oracle and PostgreSQL, and I heard that MS SQL also has statistics, but I don't know where to find it. Google didn't help much.

I hope I will hear something like

SELECT null_frac FROM pg_stats WHERE tablename='mytab' and attname='mycol';
/* PostgreSQL, stats gathered by ANALYZE */

or

SELECT nullcnt FROM custom_stats_table WHERE tabname='MYTAB' and colname='MYCOL';
/* Oracle, stats gathered by dbms_stats.get_column_stats */

I'm not asking how to compute these values myself - I know how to do it.
I need these stats for several thousands of columns, and computing this would take very long on the database that I'm working on. I need approximations, and they should be somewhere in the server - they are needed anyway for cost-based optimizer.

Nothing so easy. You would need to use DBCC SHOW_STATISTICS and look at the histogram, taking into account that the statistics might be filtered or multi column.
Probably easier to just let SQL Server do it and generate an estimated execution plan for the statement select * from foo where bar is null SQL Server will then use appropriate statistics if they exist or create them if they don't (assuming that the auto create statistics option is enabled).
You can then look at the estimated number of rows going into the SELECT iterator (This shows up as StatementEstRows in the XML for purposes of parsing programatically)
Example code
/*Create temporary stored procedure with query of interest*/
CREATE PROC dbo.#plan_test
AS
SELECT *
FROM master..spt_values
WHERE low IS NULL

GO

/*Generate an execution plan*/
SET FMTONLY ON
EXEC dbo.#plan_test
SET FMTONLY OFF;

/*Retrieve the plan and parse the estimated rows*/
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT  query_plan.value('(//sql:StmtSimple[1]/@StatementEstRows)[1]', 'float') AS EstimatedNumberOfRows
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=OBJECT_ID('tempdb.dbo.#plan_test')
     AND sys.dm_exec_sql_text.dbid=2

DROP PROC dbo.#plan_test

For exact results, something like

SELECT IsNull(col) AS is_null, count(col) AS count FROM tbl GROUP BY is_null;

might do the trick (cannot test right now, don't have MS SQL here). Obviously that will be more expensive than asking the statistics module.


Error During IMPDP in oracle database 12c I create Dump file with EXPDP command in oracle database 11gR2 with operating system Oracle Linux 6.7

EXPDP Command:

expdp hmis/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log COMPRESSION=ALL FULL=Y

Now when I run IMPDP command in oracle database 12c with operating system Oracle Linux 7.7 then I am getting errors. I have CDB and PDB database in 12c

IMPDP Command:

impdp c##app/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log FULL=Y

Error:

ORA-39083: Object type TABLE:"HMIS"."TEMP_WE_TEST" failed to create with error:
ORA-01918: user 'HMIS' does not exist

ORA-39083: Object type TABLE:"APEX_030200"."WWV_FLOW_WORKSHEET_STICK" failed to create with error:
ORA-01918: user 'APEX_030200' does not exist

ORA-39083: Object type TABLE:"SYSMAN"."MGMT_GENSVC_AVAIL_BEACONS" failed to create with error:
ORA-01918: user 'SYSMAN' does not exist

How to solve this problem
Thanks

The error message is self-explanatory:

ORA-01918: user 'XXXXXX' does not exist

Look at the log output for your expdp execution. If it does not contain the following:

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

... then the users & grants were not exported in the first place, and so were not created when importing.

FULL=Y does not solve the problem when the users were not exported in the first  place.

Check that the exporting user, hmis has the CREATE USER grant, then re-export the data.

You need to create the user that you imported, and if you want to change the name of your schema; set in the configuration:

remapremap_schema
remap_tablespace

I hope those optiones are useful for you, regards!


MAX (id) function with MySQli How can I use the following statement to return only the record with the maximum PersonID from the table?

/* Create the prepared statement */
if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS PersonID,familyname,firstname,othernames,gender,CountryID,dateofbirth,nrcno,passportno,address,occupation,village,chief,district,StatusID,imgurl

FROM t_persons")) {

/* Execute the prepared Statement */
$stmt->execute();

/* Bind results to variables */
$stmt->bind_result($PersonID,$familyname,$firstname,$othernames,$gender,$CountryID,$dateofbirth,$nrcno,$passportno,$address,$occupation,$village,$chief,$district,$StatusID,$img);

/* fetch values */
while ($rows = $stmt->fetch()) {
 // display records in a table

in PostgreSQL you could use use something called "windowing and analytics". this does not exist in MySQL however.
an alternative way would be to use GROUP BY and do ..
SELECT max(id), person, address, ... FROM tab GROUP BY person, address, ...
this should work somewhat efficiently if you need it for all. if you need it for jsut one folk you should go for ypercube's answer.


How to JOIN two table to get missing rows in the second table In a simple voting system as

CREATE TABLE elections (
election_id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255),

CREATE TABLE votes (
election_id int(11),
user_id int(11),
FOREIGN KEYs

for getting the list of elections a user has voted, the following JOIN is used

SELECT * FROM elections
JOIN votes USING(election_id)
WHERE votes.user_id='x'

but how to get the list of elections a user has NOT voted?

Use your existing query to get the opposite of the list you want. That list can then be checked against via NOT IN to get the desired list.

SELECT * FROM elections WHERE election_id NOT IN (
    SELECT elections.election_id from elections
    JOIN votes USING(election_id)
    WHERE votes.user_id='x'
)

SELECT *
FROM elections
WHERE election_id NOT IN (
    SELECT DISTINCT(election_id) from votes
);


SQL Server: How to call stored procedure without schema name? SQL Server 2005std/2008web edition
db=user=schema=owner='John'

I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.

    Dim sqlCmdVehicle As SqlCommand = New System.Data.SqlClient.SqlCommand("mySP_Name", sqlConn)
    Dim dtVehicle As New DataTable

    With sqlCmdVehicle
        .Parameters.AddWithValue("FullStockNo", "N102010")
        .CommandType = CommandType.StoredProcedure
    End With

    sqlConn.Open()
    sqlAdapter.SelectCommand = sqlCmdVehicle
    sqlAdapter.Fill(dtVehicle)

DB is backed up and successfully restored on new server. When I try to run with new SQL Server 2008 I got an error:

  Could not find stored procedure 'mySP_Name'.

pointing at last line.
I can see procedures with 'MS SQL management studio' not as  dbo.mySP_Name but as John.mySP_Name

When I change

System.Data.SqlClient.SqlCommand("mySP_Name", sqlConn)

into

System.Data.SqlClient.SqlCommand("John.mySP_Name", sqlConn)

all works well, but it's the same with all other procedures and a lot of such kind places in code :(

I got http://msdn.microsoft.com/en-us/library/ms189915.aspx, but caller's default schema is correct.
Again, all works well on old box.

What should I fix in SQL to allow run SP without explicitly mentioned user/schema name?

Thank you.

=======================================

Unfortunately, I didn't find fine solution. The way I took was search-and-replace SP_Name to John.SP_Name for the whole project.
Thanks to all participants.

Best practice is always qualify schema for all object references. See this by Tibor Karaszi or this by Midnight DBA or just trust me or the MS SQL Server Best Practice Analyzer

But you can run this if you choose to ignore best practice:

ALTER SCHEMA dbo TRANSFER John.mySP_Name;

Given the comments you left, you might consider creating synonyms for the objects.  I haven't used them much in SQL Server, so I'm not sure if they'll help, but I used them a lot in Oracle. This article sounds like it might do what you want.


Connecting SQL Server Developer Edition to Visual Studio Windows Forms Application I downloaded the free SQL Server Developer Edition in hopes of being able to use SQL Server for my Visual studio project, but I can't seem to understand how to connect it. :(

In my SQL Management Studio I never received login credentials to log in with, for the server named LAPTOPXXXX/OwnerName, so I can't connect through SQL Authentication in Visual Studio. Nor is there a create new credentials option anywhere to be found. Also when trying to connect to the server using my computer name or IP address it also fails to connect to the server I downloaded.

How do I make log in credentials for my free server and how do I connect it using like a web.config or app.config file's connectionstring (but winforms version)?

1) Create an application user for your app on the SQL Server instance.  Never use an administrative user or SA.

2) Create a DSN (Data source Name) on the machine that the app will connect to the SQL Server database.  This applies even if the two are on the same machine (which would be poor architecture, but possible in a solitary dev environment).

2a) The login credentials for this DSN should be the app's user in SQL Server

3) Go to ConnectionStrings.com and generate a SQL connection string based on the DSN you just made. (https://www.connectionstrings.com/dsn/)

4) Use the connection string in your app/web config and reference it in the SQLConnection call.


Remove Local RAID Drive from MS SQL Windows Cluster We recently stood up a production SQL Server 2014 Enterprise on a two-node Windows Failover cluster (Active/Passive).  We had some SSD's laying around and decided to use them for the tempdb for one of the two instances hosted on this cluster.  The thought was local storage plus SSD = win.

We did run Dsksp, SQLIO, etc to test this setup before going into production and everything checked out ok.  However, once we got went live, we started to see significant I/O wait issues during (and only during) our nightly DB integrity check.

After trying a few things in an attempt to resolve the issue, we decided to move the tempdb off to our SAN storage--which cleared up the I/O wait issues.

We now want to remove the SSD from each node.  Before we do so, I want to make sure I understand the process correctly.

These were local disks so they were not assigned as a cluster resource.

From what it appears, now that the tempDB has been moved, we simply need to remove the logical drive from the OS (i.e. take offline/delete), then delete the virtual disk from the RAID controller.

Is it this simple or do is there something more that needs to be in SQL or in the cluster configuration?

I have seen articles about removing shared storage, but nothing touching on local and since this is our first cluster, I want to make sure there isn't anything hidden that will cause down time or otherwise ruin our day.

Thanks

SSDs are great in general, but they are much better suited to read heavily applications, while tempdb is a mix of reads / writes and the nature of tempdb generates a consistent churn which is not good for an SSD. But it sounds like you've already gotten your tempdb off the SSD, so that step is already done.

I've not tried swapping out a local SSD for a SAN drive, but I've been involved in a migration which did that process in reverse (swapped a SAN drive for a local SSD) We failed over to a virtualized server during the process (the main server was a physical server) but it was very straightforward. We might have been able to swap in the SSD and done the entire process in a 30 minute window and skipped the fail over altogether had we known. But the policy at my current work is that any downtime longer than 30 minutes needs to be a failover.

So TLDR, it was very easy, but I wasn't the person responsible for doing the physical drive swap, only for failing over SQL Server, then doing the work to utilize the new SSD, which was reassigned the drive letter that we used for data originally.


Connect to SQL Server behind NAT from remote machine The scenario is that this is a SQL Server behind a corporate firewall with your standard setup to allow only minimal outbound traffic and zero inbound.  However, port 80/443 are open, per the usual allowance.

Has anyone tried, or is it possible to

Connect to a remote machine port 80, say using Telnet.
On the local machine, note the bound address and port [1], e.g. netstat
Disconnect from telnet.  Start port forwarder and listen on [1], mapped to SQL Server TCP/IP listener
On remote machine, connect to SQL Server on [1]

What do you think?  I just need to get some testing done and I don't want to move my huge database outside for this one-off.  I am familiar with and have used UDP hole punching for NAT traversal, mainly for push to mobile phones, but am interested to know if this works with TCP as well.

References:

TCP Hole Punching
UDP Hole Punching
TCP Hole Punching #2

I am open to other suggestions.

I doubt that it's easy to do hole punching with TCP or switch the process connected to a port.

Try this for a reverse proxy through the firewall:
http://www.dest-unreach.org/socat/doc/socat-gender.txt

This solutions relies on socat, a swiss-army-knife like network tool which can connect almost any two endpoints and is bidirectional, unlike netcat or the |-pipes in command processors. It should be available for Windows, too.

You can either use socat easily to forward a TCP connection, or in this case: Reverse the "gender" of it: You'll have two socats running, one on each side of the firewall. One continuously connects to the outside. On the outside, socat will wait for a connection from someone else. Only if he connects, the outside socat will accept a connection from the inside socat. Once the inside socat connects to the outside socat, it will connect to the SQL-server. Voila, you can connect from outside to the SQL-server inside, and from the point of the firewall and from the point of TCP the connection looked like it was actually from inside to outside.

To copy from the link:

  1) Start the double client on the inside server
  // every 10 seconds, it tries to establish a connection to the outside host.
  // whenever it succeeds, it forks a sub process that connect to the internal
  // service and starts to transfer data

$ socat -d -d -d -t5 tcp:outside-host:80,forever,intervall=10,fork tcp:localhost:80

  2) Start double server on the outside client
  // wait for a connection from a local client. whenever it accepted it, forks
  // a subprocess that tries to bind to the socket where the inside double
  // client tries to connect (might need to wait for a previous process to
  // release the port)

$ socat -d -d -d tcp-l:80,reuseaddr,bind=127.0.0.1,fork tcp-l:80,bind=outside-host,reuseaddr,retry=10

Can you not just RDP in on port 80 and do what you need to do as per:
http://www.iteezy.com/change-rdp-3389-port-on-windows-2008-server/qc/10098


Deferrable Foreign Key Constraints What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE?

This answer on StackOverflow mentions the performance impact that comes with no longer using a unique index for a deferrable unique constraint and I can relate to that. However, what are disadvantages or side-effects of making a foreign key deferrable?

I can see only one disadvantage: inconsistencies introduced by earlier writes may go undetected until a COMMIT occurs. However, our application's storage layer delays all DML statements until the end of the transaction, so an inconsistent DB state is never read (not even by the code causing it) and only detected upon COMMIT anyway.

Are there others?

If it matters, we are thinking about making all foreign keys in a certain table family DEFERRABLE INITIALLY IMMEDIATE, but only make them deferred in one specific job (out of 20 or so that access these tables) that performs a large amount of mutually dependent inserts/updates/deletes.

TL;DR: What do we have to be aware of when making foreign keys DEFERRABLE INITIALLY DEFERRED?

Deferrable constraints are usually wrong. It's a proof that your ORM does something wrong. Usual problems are

commit might fail. This is usally not expexted by various tools and also by developers
commit might last very long. This might be problem with distributed(XA) transactions.
also troubleshooting might be problematic, especially when using Java(JDBC). When commit fails you can hardly say which particular constraint failed the transaction


SQL Server Index Maintenance-Conditional Rebuilds I have a question about index maintenance. I have a weekly process that does all of my database checks, etc. and a weekly rebuilding of indexes. Those rebuilds have been causing some locking pressure on one of our apps and I am looking at the possibility of scaling the rebuilds back, making them conditional under the right circumstances.

Now, I understand that every case and every application is different, imposing different kinds of tuning if I wanted it 100% optimized. But what I am looking for is a general "good enough" algorithm.

As we are running SQL 2016 Enterprise, it looks like I can do index rebuilds online, which will probably help with the locking pressure, but it would also be nice to be able to look at something in the database and say, hey, "this one can be skipped entirely this week.", either because it's one of my reference tables (no table content changes since last week), or some other slowly changing table...

For various reasons, I gave up on Microsoft's SQL Maintenance Plans a while back and I now use an in house c# program to do all of my SQL maintenance (transaction logs, weekly full backups, weekly checkdbs, weekly index maintenance, etc.) all while sending me an email when it's done letting me know if things went smoothly, or went south somewhere along the way. So, I don't mind adding some queries to my code to see about making index maintenance conditional or not.

It's totally an opinion and observation based in my current project where we handle databases over 20 TBs . We do not do any rebuild or re-organize of indexes as that is very expensive operation. So please check if that is actually helping you out in performance. Rather we focus more on update stats daily with change or modified stats.

If needed, you can use ola hallegren which has excellent parameters to set that up else use the one available from Microsoft.  The SQL tiger team has been doing an excellent job for such activities in their git hub page for adaptive index defrag proc  here

Usually, a good rule of thumb starting point is that indexes should be rebuilt when they are fragmented by more than 30% and should be reorganized when they are between 10-30% fragmented.

You could write a script which queries sys.dm_db_index_physical_stats (avg_fragmentation_in_precent column) to work out the percentage fragmentation and then take the appropriate action (rebuild or reorganize)


MySQL Workbench model/source synchronize issue I've been looking all over for any description of an issue like this and I haven't found anything so far.

I am trying to update my MySQL database using MySQL Workbench (5.2.40 CE). I am using the Synchronize Model option just as I have always done before to update the database with the changes I've made to the model. Main thing to note here is that the issue I'm going to describe happens even without any changes being made to the model.

The problem is when it gets to the point where it shows "Model and Database Differences", the fields which all normally line up between Model and Source are totally off.
I get everything doubled with the first set showing the Model names of my Schema and Tables with the Sources next to all of them showing N/A. Then below that it has all of the Source values filled in while the Model values all say N/A.

If I run the update as it is my database does get updated, but it also completely wipes out all the information on the database because it is dropping all the tables and re-entering them. Here is the picture of what the page looks like.

I have no idea if this is some kind of setting or bug. But on my test database after totally getting wiped out and trying it again, the Differences page shows up normally with everything 1:1 like it should, and it updated just fine.

Any clues on this would be appreciated.

MySql Workbench is powerful, but not perfect.

Some time in similar case, I've done the following to resolve the problem:

create a blank project
require a model update FROM db source
then I'll reapply the mod.

Often this happen if you just change a 'comma' [a small thing] in the db without sync it with the model, and then you change another 'comma' in the model. MySql Workbench goes crazy often as db increases in complexity and table number, but I think it's a very good product.

If you are sure your db is identical to your model, you can update your model instead your db.

UPDATE

I had faced the same problem with MySQLWorkbench. In my case I'm using Workbench v8.014 and test and production db server at v8.0.12 and v5.6 respectively.

Below is the algorithm to fix the synchronization issues. After each step, if the automated coded still drop any table or column it shouldn't or miss-detect any difference I try with the next step.

At the "Model and Database Differences" wizard screen:

Check and update the table mappings using the button "Table Mapping". This mainly fix the mayor problems related to drop and create tables.
Check the mapped fields using the button "Column Mapping". This sometimes works, and the generated sql code change from a "DROP" to a "CHANGE" when a table's field is updated in the model.

At the "Preview Database Changes to be Applied" screen:

Sometimes, the previous steps does not fix the problems. So I manually edit the generated sql to update the bd. You can figure out if you will write by hand the update sql code then is pointless use the Synchronization tool and read this answer, although if you edit the generated code to the right one inside this wizard screen, the model "magically" get correctly synchronized.

At the "Model and Database Differences" wizard screen:

At this point, all change and drop commands over a table or a table's field should be fixed. However there is still some indexes and foreign keys synchronization issues. For this scenario, first check the db exactly have same indexes and foreign keys as model has (I normally do it for 2 or 3 tables) then  run the tool and update model instead db. Do that selecting all changes in the screen and then pressing the button "Update Model". If everything was fine as you already checked, your model  diagrams should be intact: see mainly for any relationship change.

Notes:

When there are many real changes between the model and the bd, I recommend fix 2 or 3 tables on the same time, and set as "Ignore" all other changes detected by Workbench and and re-run above algorithm until your fix all tables.
I don't use views or routines, so the "algorithm" is not aware about It. It is all up to you


Is a 5+ column primary key bad for large (100 million+) table? I was reading about some real life DB issues, and one project had a 100 million row plus table that had 5 columns as its primary. I'm thinking this is bad, but can anyone tell me exactly why?

The table was kind of a micro rollup/aggregation table, so the 5 columns were like (day, market_id, product_id...). At first I thought that a 5 column primary key wasn't ideal, but the more I thought, I couldn't really come up with a good reason why it was bad.

This was in a late night discussion with half the company engineers. Someone just mentioned this was a bad design, one senior engineer agreed, but no one really jumped in as to why. Thus trying to research the matter for myself!

There are performance issues with very complex primary keys.  And It may not be defending against duplication as well as a simpler primary key might.

However, there is one design pattern that frequently yields tables with a primary key made up of six or so components.  It's star schema fact tables.  If the fact table of a star schema has six dimensions, then the primary key will have six components.  I've never seen a fact table with no declared primary key, and I think it's well worth the overhead, even though the ETL process still has to be quite carefully written.

Some reporting databases imitate the pattern of star schema even if it's not explicitly designed that way.

100 million + rows is not overly big for a fact table, especially with today's big data.

For some 15+ years I do not need such key, saw it sometimes, and it was only causing troubles. A lot of troubles.
First of all primary key are for holding data integrity, and they should be syntetic. They should not have any binding to real world.
Why ? Once real world change, and it will, for sure, your primary key is gone, and you have to update it, and all related information.

Imagime you need to remember this ker in some other table/database/service instead of one field you need to copy several, and you can forget to copy some of them. Instead sysntetic primary key, is just one piece of data, you have to provide.
I am not mentioning uniqness of index, which may by another huge topic for discussion.

So short summary, syntetic primary key(auto incrementing, guid, ..) is simple to maintain, copy, ...

So I consider, syntetic primary key, and another key for 5 columns you mentioned.

At last, if the table is only aggregate, and never ever will someone need to reference row by keys(but world changes, trust me it will, at least for me it changes permanently), I will probably leave it like it is(primary key with five rows), but in case we used to have, it alway cause lots of trouble. So I told you.


SQL Server Backup Job failed multiple times I am getting the same error as mentioned by Mr.Ali

Sql Server Backup Job failed

Can anyone please explain the possible ways to fix this error as the backup is failing since 1 month.

Here is the Job history log

Executed as user: SAP\sqladmin.
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started:  21:00:00
Progress: 2014-10-03 21:00:05.83
Source: {E7436186-D15A-4485-92B4-7623DA7DDF4D}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete
End
Progress
Error: 2014-10-03 23:32:25.53
Code: 0xC002F210
Source: Back Up Database Task Execute SQL Task
Description:
Executing the query "BACKUP DATABASE [P01] TO  DISK =N'\\cmgsap31.sap...."
failed with the following error:
"Write on "\\cmgsap31.sap.logica.com\p$\Central-MS-SQL-Backup\SAP0233_P01_Full.bkp"
failed: 64(failed to retrieve text for this error. Reason: 15105)
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 21:00:00
Finished: 23:32:25
Elapsed:  9145.29 seconds.
The package execution failed.  The step failed.

Well based on Reason:15105 I think that SQL Server doesn't have permission to access to the path you check the permissions of the account that is assigned to the SQL service account? or other reason would be that there is not enough free space to create backup.

This issue usually occur if you have a third-party backups and it might be breaking Log chain. I have came across the same problem at my work. Everyday, there are working fine but we had a third party backup at 9 pm and the backups fail for next 4 hours, then 1 am diff backup is done fine. I resolved this issue by moving third party backups from Database and disk level.


Can't find source code for building mysql I'm trying to follow the instructions for building MySQL from source. They guide me to a link where I can download the latest GA. I end up at the downloads page, where I can download mysql-5.6.19-osx10.7-x86_64.tar.gz for my mac, and then untar and unzip.

All good so far. But when I try to cmake . per the instructions, I get an error saying that it can't find CMakeLists.txt. Poking around looking for that, I've discovered that the download doesn't come with any actual source code except .h files. In fact, the download looks like an already-built setup with binaries such as mysqld already there.

I am probably missing something obvious, but I can't figure out where I'm supposed to get the full source code and the makefile, if not from the links provided under the title, "Installing MySQL Using a Standard Source Distribution". Anyone have an idea?

Funny - I'm just doing this myself now (assuming Linux (as per Alex Kuzminsky - I use Xubuntu - no heavy GUI desktop).

Get the source (all source downloads here (select from dropdown), Linux source here).

Run (depends on your Linux distro)

sudo apt-get install cmake
sudo apt-get install bison flex
sudo apt-get install libaio1
sudo apt-get install g++ (Ooops!)
sudo apt-get install libncurses5-dev

This was all I needed to do after a fresh (Oracle Virtual Box) install of Xubuntu 14.04 LTS.

Then, what I do is follow the instructions here.

Create a directory "sandbox" beside mysql-5.6.16.

cmake ../mysql-5.6.19
make
make package

then install the mysql-5.6.19-linux_64.tar.gz package (or whatever it's called)

Works nicely for those of use who want to control things and even run several MySQL servers on the same machine using different ports for example.


select columns with different conditions This question focus to the COUNT result

1st query will show the COUNT with date condition.

2nd query will show the COUNT without date condition.

Now I need a query which make the COUNT result in 1st query without date condition which is :
COUNT(c.CustomerID) > ONLY with condition b.BookingCustomerID=c.CustomerID
(without date condition)

c.CustomerTelephone, b.* > with condition WHERE b.BookingCustomerID=c.CustomerID
AND BookingDate LIKE '%2019-05-04%'

Thanks.

SELECT COUNT(c.CustomerID), c.CustomerTelephone, b.*
FROM Booking b, Customer c
WHERE b.BookingCustomerID=c.CustomerID
AND BookingDate LIKE '%2019-05-04%'
GROUP BY c.CustomerID

SELECT COUNT(c.CustomerID), c.CustomerTelephone, b.*
FROM Booking b, Customer c
WHERE b.BookingCustomerID = c.CustomerID
GROUP BY c.CustomerID

When doing a GROUP BY, it is improper to list anything other than the group-by columns and aggregates in the SELECT part.  In your case, the values of b.* could be random.

Are you trying to compute both counts at the same time?  If BookingDate is inc, then it is easy.  If it is in the other table, then it gets messier.  _WhenJOINing`, please make it clear which columns come from which tables._

SELECT COUNT(c.CustomerID) AS count_all,
       SUM(( SELECT BookingDate FROM Booking
                     WHERE BookingCustomerID = c.CustomerID
                       AND BookingDate LIKE '2019-05-04%' )) AS count_day
    FROM Customer AS c
    GROUP BY c.CustomerId

To get the rest them,

SELECT x.count_all, x.count_day
       c.CustomerTelephone,
       b.*
    FROM ( ... the query above ... ) AS x
    JOIN Customer AS c USING(CustomerID)
    JOIN Booking AS b ON b.BookingCustomerID = x.CustomerID
    FROM Customer AS c
    GROUP BY c.CustomerId

How its possible to "group by" a column with same with "count" . In your case you group by with customerId and also COUNT with customerId.

Can you please change group by Condition. on which basis(column) you want to COUNT by customerId.


Add a new Column and define its position in a table I have Table A with 5 columns:

TableA
--
Name
Tel
Email
Address

I want to add a new column (mobile) in between Tel & Email:

TableA
--
Name
Tel
Mobile
Email
Address

If I use

ALTER TABLE TableA
ADD COLUMN Mobile INT NOT NULL

the mobile column is added to the end of the table.

Is there a way to achieve this without dropping the table and moving data to a new table?

No. Rebuilding the table is the only way. See this Connect Item for confirmation.

You could use SSMS to script this for you if you trust the somewhat buggy table designer.

Apart from that you could declare a view with the desired column order as a way of grouping logically related columns together.

If you see "Saving changes is not permitted." messages after adding a new column in Designer with SQL Server 2005 or greater, you'll need to select Tools -> Options, select Designers, and deselect the box under Table Options named: "Prevent saving changes that require table re-creation."  After you do this step, you should be able to save your changes where you want.

It is strange that MySQL allows you to add columns anywhere using SQL, but with SQL Server, you can only do this using Designer.  Hmmm.  Seems like there must be some hidden magic commands somewhere! ;-)


Process to reclaim free space from mongodb ,replication is there We are facing space pressure on production server so we are planning to  reclaim space from prod instance.replication is there ,can anyone help me  to  know complete process in sequence to reclaim free space.

I am thinking to use repair db commend at slave node to reclaim space but don't know how to make secondary node after repairing to primary server and start replication again.

Once and for all: The database repair command is potentially harmful and is advised against

  You should not use repairDatabase for data recovery unless you have no other option.

And

  This command obtains a global write lock and will block other operations until it has completed.

Hardly what you want in a production system. I already answered a similar, though not identical question. However, the same rules apply. Read the part on capacity planning closely.

And honestly: if you have to ask for the procedure, you really, really, really should not even think about executing it. Even when you get a detailed How-To, you will lack any knowledge of potential side effects. If it is a production system, get yourself a DBA. It is worth it.

you can use repair db command for availing space. As you have mentioned that you are using replica set so you can repeat below steps (I am assuming that you are using 3 member replica set):

Stop one of your secondary and start it on some other port and do db repair.
Now restart your secondary with old port with replset option.

Repeat it for remaining secondary.

For primary ,you can step it down to secondary using "rs.stepDown" and then perform above mentioned steps. But before you start doing this make sure you have available space in your Db it should be equal to same as db size + 2GB.


Should binary files be stored in the database? What is the best place for storing binary files that are related to data in your database? Should you:

Store in the database with a blob
Store on the filesystem with a link in the database
Store in the filesystem but rename to a hash of the contents and store the hash on the database
Something I've not thought of

The advantages of (1) are (among others) that atomicity of transactions is preserved. The cost is that you might dramatically increase storage (and associated streaming/backup) requirements

The goal of (3) is to preserve atomicity to some degree - if you can enforce that the filesystem you are writing to does not allow files to be changed or deleted, and always has the correct hash as filename. The idea would be to write the file to the filesystem before permitting the insert/update referencing the hash - if this transaction fails after the filesystem write but before the database DML, that is fine because the filesystem is 'faking' being the repository of all possible files and hashes - it doesn't matter if there are some files in there that are not being pointed to (and you could clean them up periodically if you are careful)

EDIT:

It looks like some RDBMSs have this covered in their individual ways - I'd be interested to know how others do it - and particularly in a solution for postgres

Store in the database with a blob

A disadvantage is that it makes your database files quite large and possibly too large to back up with your existing set up. An advantage is integrity and atomicity.
Store on the filesystem with a link in the database

I've come across such horrible disasters doing this, and it scares me that people keep suggesting it. Some of the disasters included:

One privileged user who would rearrange the files and frequently break the links between the paths in the DB and where they now are (but somehow this became my fault).
When moving from one server to another, the ownership of some of the files was lost as the SID for the old machine's administator account (what the old website was running on) was not part of the domain and so the copied files had ACLs that could not be resolved thus presenting users with the username/password/domain login prompt.
Some of the paths ended up being longer than 256 characters from the C:\ all the way to the .doc and not all versions of NT were able to deal with long paths.

Store in the filesystem but rename to a hash of the contents and store the hash on the database

The last place I worked at did this based on my explanation of the above scenarios did this. They thought it was a compromise between the organization's inability to obtain experience with large databases (anything larger than about 40G was ordained to be "too big"), the corporate inability to purchase large hard drives, and the inability to purchase a more modern back up solution, and the need to get away from risks #1 & #3 that I identified above.

My opinion is that storing in the DB as a blob is a better solution and more scalable in a multi-server scenario, especially with failover and availability concerns.

Share my experience of Ms SQL server and a huge number of files. We save the files on a file server. Database has two tables, one for the file folders and access credentials, one for the filename. It is easy to maintain the database and files. You can easily move the files even cross the servers, just need to modify the folders table.


Does a DBA need to know how to program in a system language besides SQL? To what extent does a Database Administrator need to know system or application level programming languages (for example .NET or PHP) besides "just SQL"?

For the purposes of this question, no specific version of the SQL standard is considered for this answer (SQL ANSI 86, SQL ISO 87, SQL:2008) as the question is in regards to desktop or server languages outside the realm of SQL.

It depends.

In a big shop, maybe not because you have 1000s of servers to look after and your tools are provided. In a small shop, you'll probably need more knowledge because you have a wider remit.

Scripting languages (PowerShell, cmd.exe for example) are always useful though for monitoring, deploying etc. I've often had some Perl scripts I had to maintain (just about). Then there are the assorted ETL packages that you'd be expected to know about.

Saying that, most DBAs (that I know) would be able to write basic CLR stuff or know PL/SQL well. For me, the dividing line is knowing about the broader patterns or architecture of .net or Java. I don't need it as a DB developer or DBA. In the same way that .net or PHP or Java Monkeys don't understand database design or architecture or code as well as I do.

Personally, I decided to stop chasing the latest or best client language years ago and focus on database work. That doesn't make me a "non-programmer": I'd need to learn it again if I had to.

I'm pretty convinced that only big shops with big applications have demand for a non-programming DBA, as small shops with simple databases can get away with the jack-of-all-trades developer / DBA. For a DBA to become a master of a product (Oracle, SQL Server, whatever) there has to be demand for this knowleadge! The DBA must be subjected to complex environments and problemas to help him become more knowleadgeable. This is basically a rant of mine, because I'm a DBA at a small shop and I don't do anything that requires more than 2 neurons, I think I'm wasting my time at this job. I want to be a developer.


SQL Server URL for DBEdit2 There is a cool program out there called DBEdit2, which can be used for SQL Server. Here is where you can get it free:

http://dbedit2.sourceforge.net/

The problem is that I can't make it work! It gives me the following error (conf info crossed out):

This is what I put in the login details:

And this is what I get if I click details:

Any ideas at all? Can anyone else make this program work?

I've made sure that I have my SQL Server set to allow both SQL Server and Windows authentication mode.

I've ensured that the server is running and that I have browser running.

I don't know.

As @a_horse_with_no_name points out, your connection string is incorrect.  The java jdbc driver needs to know the instance name ( it doesn't play well with the backslash notation ), which will be overridden if you're specifying the port ( 1433 is the default instance port - you might not have to specify the instance name at all if that's what you're trying to connect to ).

Try something along the lines of this instead for your connection string:

  jdbc:jtds:sqlserver://JT__428/j___test_db;instance=J__MS14

The solution in the end was two fold:

1) firstly ensure that your TCP/IP is switched on. To do this go to Configuration Manager and check under SQL Server Network Configuration > Protocol  and TCP/IP - rClick and select Enable.

2) secondly, if you have a named instance, like I did, DBEdit2 didn't seem to like it when I tried the backslash and name of instance. In other words, it didn't like this:

jdbc:jtds:sqlserver://MyHostName\MyInstanceName:1433/test_db

But you can set the port of the TCP/IP to another port, other than the standard 1433, and simply change the connection string in DBEdit2 to connect. To set the port, go back to the TCP/IP settings mentioned above, and set all references to the Port to something like 1434, then restart the instance. Then alter the connection string accordingly, only listing the host name:

jdbc:jtds:sqlserver://MyHostName:1434/test_db

Incidentally, to find the host name, you can rClick the service in SSCM and you'll find it on the Service tab.


Are ER diagrams independent of a DBMS? When modelling a database, does the ER diagram depend on the DBMS used to implement the said ER?

When the ER (Entity-Relationship) model was devised in the 1970s, it was specifically intended to be implementation neutral (sometimes called "implementation agnostic").  The relational model, by contrast, was tilted towards a relational design and implementation.

In classical three stage methodology, an ER model is devised to capture the data analysis of the subject matter.  You could implement the database as relational, or OODB, or unstructured, or whatever. This is called the conceptual model.

The next model is a relational model, and captures the logical design.  This is tilted towards a class of Relational DBMS products that includes Oracle, SQL Server, and many others.  This is called the logical model.

The third model is a detailed model of the proposed database, and captures features that are DBMS specific, like indexes, and table spaces.  This is called the physical model.

Very often the logical and physical models are merged together, because the DBMS has already been chosen, and they are both design models.

Each ER diagram will be translated to SQL DDL. So we can translate you question to  SQL DDL dependence from DBMS. In practice if you follow some SQL standard like SQL92 you are relatively undependable. If you use concrete RDBMS extensions for SQL standard you are dependable. It's the point.