הבלוג של גרי רשף

15/09/2011

יצירת אובייקטים משותפים

Filed under: Uncategorized — גרי רשף @ 19:02

ב-SQL Server ניתן ליצור פרוצדורות מערכת ו-Views מערכת (וכפי שנראה בהמשך- גם טבלאות), כשהכוונה לאובייקטים שיוצרים אותם וניתן להשתמש בהם בכל הדטבייסים כאילו היו מקומיים (כלומר- לא יוצרים אותן בכל דטבייס בנפרד אלא פעם אחת עבור כולם), והתנאים כדלקמן:
1. יש ליצור את האובייקט (פרוצדורה או View) בדטבייס master.
2. שם האובייקט צריך להתחיל בשלושת התווים SP_.
3. יש להכריז על האובייקט כאובייקט מערכת באמצעות הפרוצדורה (הלא מתועדת) SP_MS_MarkSystemObject.

נבדוק איך זה נראה: ניצור ב-master (תנאי 1) ארבעה אובייקטים – שתי פרוצדורות ושני Views בהתאם לתנאי מספר 2, ונכריז על אחד מכל סוג כעל אובייקט מערכת (תנאי 3):

Use master;
Go

If Object_ID('SP_Try1') Is Not Null Drop Proc SP_Try1;
Go

Create Proc SP_Try1 As
Select 'master' DBName1, DB_Name() DBName2,* From sys.sysfiles;
Go

If Object_ID('SP_Try2') Is Not Null Drop Proc SP_Try2;
Go

Create Proc SP_Try2 As
Select 'master' DBName1, DB_Name() DBName2,* From sys.sysfiles;
Go

Exec SP_MS_MarkSystemObject SP_Try2;
Go

If Object_ID('SP_Try3') Is Not Null Drop View SP_Try3;
Go

Create View SP_Try3 As
Select 'master' DBName1, DB_Name() DBName2,* From sys.sysfiles;
Go

If Object_ID('SP_Try4') Is Not Null Drop View SP_Try4;
Go

Create View SP_Try4 As
Select 'master' DBName1, DB_Name() DBName2,* From sys.sysfiles;
Go

Exec SP_MS_MarkSystemObject SP_Try4;
Go

כל האובייקטים מבצעים אותה שליפה, שבניתי אותה כדי לבחון את שתי הנקודות הבאות:

השליפה מ-sys.sysfiles מחזירה את שמות קבצי הדטבייס של זה בו אנחנו נמצאים, פונקציית המערכת DB_Name מחזירה את שם הדטבייס בו נמצאים, ובנוסף יש ציון "קשיח" (Hard coded) של שם הדטבייס כדי שיהיה ברור היכן האובייקט באמת נמצא.

כעת נעבור לדטבייס tempdb וניצור גם שם את ארבעת האובייקטים הנ"ל, אם כי ללא הכרזה עליהם כעל אובייקט מערכת, ועם השינוי המתבקש בציון ה"קשיח" של שם הדטבייס:

Use tempdb;
Go

If Object_ID('SP_Try1') Is Not Null Drop Proc SP_Try1;
Go

Create Proc SP_Try1 As
Select 'tempdb' DBName1, DB_Name() DBName2,* From sys.sysfiles;
Go

If Object_ID('SP_Try2') Is Not Null Drop Proc SP_Try2;
Go

Create Proc SP_Try2 As
Select 'tempdb' DBName1, DB_Name() DBName2,* From sys.sysfiles;
Go

If Object_ID('SP_Try3') Is Not Null Drop View SP_Try3;
Go

Create View SP_Try3 As
Select 'tempdb' DBName1, DB_Name() DBName2,* From sys.sysfiles;
Go

If Object_ID('SP_Try4') Is Not Null Drop View SP_Try4;
Go

Create View SP_Try4 As
Select 'tempdb' DBName1, DB_Name() DBName2,* From sys.sysfiles;
Go

נריץ כעת את ארבעתם כשאנחנו עדיין ב-tempdb:

Use tempdb;
Go

Exec SP_Try1;
Go

Exec SP_Try2;
Go

Select * From SP_Try3;
Go

Select * From SP_Try4;
Go

clip_image002

ארבעת האובייקטים שהופעלו הם אלו שב-tempdb, כלומר- המערכת מחפשת כל אובייקט קודם כל בדטבייס המקומי, ורק לאחר מכן ב-master (כפי שנראה להלן).

זה עומד בניגוד לעצה שמופיעה לעיתים ברשימות של Best Practices בהן מומלץ לא לתת לפרוצדורות שמות שמתחילים ב-SP_ כדי שהמערכת לא תחפש אותם לחינם ב-master, אך מתברר שאין זה כך והיא מחפשת שם רק לאחר שלא מצאה כאן.

כעת נבטל את ארבעת האובייקטים שב-tempdb ונפעיל אותם שוב מתוך tempdb (כך שיופעלו אלו שב-master):

Use tempdb;
Go

If Object_ID('SP_Try1') Is Not Null Drop Proc SP_Try1;
If Object_ID('SP_Try2') Is Not Null Drop Proc SP_Try2;
If Object_ID('SP_Try3') Is Not Null Drop View SP_Try3;
If Object_ID('SP_Try4') Is Not Null Drop View SP_Try4;
Go

Exec SP_Try1;
Go

Exec SP_Try2;
Go

Select * From SP_Try3;
Go

Select * From SP_Try4;
Go

clip_image004

ודם כל- כל האובייקטים שהופעלו הם אלו מה-master, ואת זה רואים לפי העמודה הראשונה.

SP_Try1: לא הוכרזה כפרוצדורת מערכת, ה-DB_Name הפעיל מבחינתה הוא tempdb אבל היא פונה ל-master ומציגה את הקבצים שלו. כדאי לשים לב שבגלל שהשם מתחיל ב-SP_ המערכת יודעת לגשת ולחפש אותו ב-master למרות שלא נעשה שימוש ב-SP_MS_MarkSystemObject.

SP_Try2: הוכרזה כפרוצדורת מערכת, ה-DB_Name הפעיל מבחינתה הוא tempdb והיא גם פונה לפונקציות המערכת שלו. כלומר- לא רק שניתן להפעיל אותה מ-tempdb, אלא היא גם פועלת בתוך tempdb, ואם למשל תופיע בה פקודת Create Table – הטבלה תיווצר בדטבייס בו היא הופעלה ולא ב-master (כפי שהיה קורה בפרוצדורה הקודמת).

SP_Try3 & SP_Try4: שני ה-Views פועלים ב-master ופונים ל-master ללא קשר לשאלה אם נעשה שימוש ב- SP_MS_MarkSystemObject.

מה לגבי טבלאות? ננסה:

Use master;
Go

If Object_ID('SP_Try5') Is Not Null Drop Table SP_Try5;
Go

Create Table SP_Try5(I Int Identity,
DBName Sysname Default DB_Name());
Go

Insert
Into SP_Try5
Default Values;
Go 5

Select * From SP_Try5;

clip_image006

למי שלא הבין את פקודת ה-Insert: היא מכניסה את ערך ברירות המחדל (Default Values) לעמודות, ומתבצעת 5 פעמים (Go 5).

כעת נעבור ל-tempdb ולמרבה הפלא (או שלא) ניתן לפנות לטבלה גם משם (עדכון ושליפה):

Use tempdb;
Go

Insert
Into SP_Try5
Default Values;
Go 5

Select * From SP_Try5;

clip_image008

ניתן איפוא ליצור טבלאות משותפות לכל הדטבייסים בתנאי ששמם מתחיל ב-SP_, וזה מזכיר לי בדיחה:

הפיל הנמר והנחש נפגשים.

אומר הפיל: איזה קטע- השמות של שלושתנו מתחילים באות נ'.

מתפלא הנמר: אבל פיל מתחיל ב-פ'.

עונה הפיל: נכון, אבל השם שלי ניסים..

אז אם לפיל קוראים ניסים, לטבלה אפשר לתת שם שמתחיל ב-SP_..

סיכום:

1. פרוצדורות ו-Views שנמצאים ב-master וששמם מתחיל ב-SP_ – ניתן להפעיל מכל הדטבייסים כאילו הם מקומיים.

2. פרוצדורה כזו שהוכרזה כפרוצדורת מערכת על ידי SP_MS_MarkSystemObject תפעל על הדטבייס מתוכו היא הופעלה.

3. פרוצדורה כזו שלא הוכרזה כפרוצדורת מערכת על ידי SP_MS_MarkSystemObject תפעל בדטבייס מתוכו היא הופעלה על master.

4. Views יפעלו ב-master ולכן הם שימושיים רק אם רוצים להחזיר בעזרתם נתונים שאינם תלויי דטבייס (למשל- פרמטרים של השרת, פנייה קשיחה לדטבייס מסויים, פניה לשרת אחר דרך Linked Server וכו').

5. ב-Views אין משמעות לשימוש ב-SP_MS_MarkSystemObject.

6. ניתן ליצור גם טבלאות מערכת שיהיו משותפות לכל הדטבייסים.

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: