The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,157 other followers

Archive for the ‘Database Development’ Category

Kristian Köhntopp had an interesting JOINs go when moving from monolithic to microservicecs (i.e. when customers and orders are in separate services)

Posted by jpluimers on 2020/11/19

is a summary of:

[WayBack] Thread by @isotopp: Are you a Developer and understand (Micro-) Services? I am a database person and a bit simple, and I have a genuine Question: When moving to…

Are you a Developer and understand (Micro-) Services? I am a database person and a bit simple, and I have a genuine Question:

When moving to a services architecture, where do the JOINs go?

So you sell stuff, that is you have an orders table o, with an oid, which stores a customer id cid from a customers c table, and an article id aid, from an articles table and a count cnt.
customer 17 ordered 3 45’s:
? SELECT cnt
> FROM o JOIN c ON o.cid = c.cid
> AND o.aid = a.aid
> WHERE c.cid = 17;= 3
When moving to services, because you are multibillion dollar enterprise, your customers, orders and articles can no longer fit into a single database, and there are other reasons to have an OrderService, CustomerService and ArticleService.

You still want to ask something (OrderService?) about the number of 45’s that 17 ordered.

Who do you ask? What does this do to connect the dots? How do you do reporting (“Show me all top 10 articles by country, zipcode digit 1 by week over the last 52w”)?

Do you reimplement join algorithms by hand in application code? Are there supporting tools? Do you reimplement data warehousing aggregations, too?
If so, what tooling for reporting does exist, and how does that compare to eg existing tooling for data warehousing?

Some of the reactions on Twitter are below

–jeroen

Read the rest of this entry »

Posted in Database Development, Development, Software Development, Systems Architecture | Leave a Comment »

Find a Table on a SQL Server across all Databases – TechNet Articles – United States (English) – TechNet Wiki

Posted by jpluimers on 2020/10/13

Neither solution on [WayBackFind a Table on a SQL Server across all Databases – TechNet Articles – United States (English) – TechNet Wiki is nice, but the most readable (though undocumented) works:

sp_MSforeachdb'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''

Similar solutions at:

–jeroen

Posted in Database Development, Development, SQL Server | Leave a Comment »

Configure IntelliSense (SQL Server Management Studio) | Microsoft Docs

Posted by jpluimers on 2020/10/08

Not sure why, but all of a sudden, SSMS did not code-complete any table or column names any more.

This shows where that setting is [WayBack] Configure IntelliSense (SQL Server Management Studio) | Microsoft Docs.

The odd thing: updating to a more fresh 17.x version solved the problem all by itself.

Anyway, you can change the settings under the section “All Languages”, “Transact-SQL” or “XML”, each in the “General” sub-section:

–jeroen

Posted in Database Development, Development, SQL, SQL Server, SSMS SQL Server Management Studio | Leave a Comment »

sql server – Index Seek vs Index Scan – Database Administrators Stack Exchange

Posted by jpluimers on 2020/10/01

Below some links I used to get a feel for the different query execution plan entries I observed.

The first one was the most important for me, so hopefully this post helps bump it up in the search engine results.

–jeroen

Posted in Database Development, Development, SQL, SQL Server | Leave a Comment »

When your ORM does not support string concatenation by || or + operator…

Posted by jpluimers on 2020/09/30

If your ORM does not support string concatenation by operator (standard double pipe || or non-standard plus +), you can usually revert to the CONCAT function.

Very often, the CONCAT function supports more than 2 parameters.

References:

–jeroen

Posted in Database Development, Development, Software Development | Leave a Comment »

 
%d bloggers like this: